referencing data from another table without using a join



  • 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.
    enter image description here

    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



  • Since you don't need to return any data from the shipping_details table, you can use a https://en.wikipedia.org/wiki/Relational_algebra#Semijoin_(%E2%8B%89)(%E2%8B%8A) , 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
    ) ;




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2