SQL 2005 Transact-SQL Full Outer Self Join

SQL 2005 Transact-SQL Full Outer Self Join

• Clark Anderson

  as Outer joins will return NULLs for columns that belong to the non-matching rows and the WHERE clause you have will eliminate those rows. You can do the following:

  SELECT T1.product, T1.vendor, T2.product, T2.vendor

  FROM (SELECT * FROM product_vendor WHERE product = 1) T1

  FULL OUTER JOIN

  (SELECT * FROM product_vendor WHERE product = 1) T2

  ON T1.vendor = T2.vendor

  Another way to write the query without FULL join is to do below. This will perform much faster than the FULL JOIN especially for larger data sets.

  select t.vendor

  , case t.cnt

  when 2 then 'Vendor sells p1 & p2'

  when 1 then (case

  when p2 is null then 'Vendor sells p1 but not p2'

  when p1 is null then 'Vendor sells p2 but not p1'

  end)

  end as product_status

  from (

  select p.vendor, min(p.product) as p1, max(p.product) as p2, count(distinct p.product)_as cnt

  from product_vendor as p

  where p.product in (1, 2)

  group by p.vendor

  ) as t

  
  
  

• aquaseal

  When you have a [LEFT] JOIN, and you add columns from the [RIGHT] table in the WHERE clause, you effectively turn it into an INNER JOIN.

  SO, move the [T2.product = 2] into the JOIN criteria.

  SELECT
  t1.product,
  t1.vendor,
  t2.product,
  t2.vendor
  FROM Product_Vendor t1
  FULL OUTER JOIN Product_Vendor t2
  ON ( t1.vendor = t2.vendor
  AND t2.product = 2
  )
  WHERE t1.product = 1

  
  
  

• bharathi_tunes

  Thanks for the solution. The first option works just fine. However, the second one requires a minor change as mentioned below, and it works fine after the fix.

  ...

  when p2 is null then 'Vendor sells p1 but not p2'

  when p1 is null then 'Vendor sells p2 but not p1'

  ...

  This needs to be changed as p1 and p2 values will never be NULL in the sub-query and we need to check for the specific value as below.

  ...

  when p1 = 1 then 'Vendor sells p1 but not p2' <-- both p1 and p2 will be 1

  when p1 = 2 then 'Vendor sells p2 but not p1' <-- both p1 and p2 will be 2

  ...

  Thanks for your help.

  
  

• HellsChicken

  Thanks for the response. However, this query returns only the matching rows and non-matching rows for only one product (as if it is LEFT or RIGHT OUTER JOIN). I belive it is because of the WHERE clause. In case you are interested, the solution posted by another user (from MS team) works fine.

  
  

• coolarian

  "This will perform much faster than the FULL JOIN especially for larger data sets."

  If there is an unique index on Product + Vendor (in that order, combination key), do you still think the second option will be more efficient than the first one This table is expected to have about 10,000 products and about 20 vendors for each product (about 200,000 at the maximum). Please clarify.

  Thanks

  
  
• SQL 2005 Transact-SQL Full Outer Self Join