referencing data from another table without using a join

All we need is an easy explanation of the problem, so here it is.

Hi all so I have a homework assignment that I am having difficulty understanding. the problem I have been given is this:

ix. Referring to the customers and shipping_details tables on the ER, display all data from the customers table but only where the province on the shipping_details table is equal to NS. Do not use a join for this requirement.

So I can complete the question easily enough using a join but I am having difficulty understanding how one can reference another table without using a join. I am trying to use a vector aggregate function but I am very unclear on how to reference a table and a value within a column of that table without using a join

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Since you don’t need to return any data from the shipping_details table, you can use a semi-join, which can be written with either IN and a subquery or EXISTS and a correlated subquery.

Examples:

-- Using IN --
select c.*
from customers as c
where c.shipping_detail_id IN
  ( select sd.shipping_detail_id
    from shipping_details as sd
    where sd.province = 'NS'
  ) ;


-- Using EXISTS --
select c.*
from customers as c
where EXISTS 
  ( select 1 
    from shipping_details as sd
    where sd.province = 'NS'
      and sd.shipping_detail_id = c.shipping_detail_id    -- correlation
  ) ;

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0