SQL Full Outer JOIN old-style vs ANSI
SQL
Download (.zip)
USE pubs GO -- New style OUTER JOIN correctly finds 11 rows SELECT COUNT(*) FROM ( -- FIRST join authors and titleauthor (authors AS A LEFT OUTER JOIN titleauthor AS TA ON A.au_id=TA.au_id )
-- The result of the previous join is then joined to titles FULL OUTER JOIN titles AS T ON TA.title_id=T.title_id ) WHERE state <> 'CA' OR state IS NULL GO -- Attempt with old-style join. Really no way to do FULL OUTER -- JOIN. This query finds 144 rows–-WRONG!! SELECT COUNT(*) FROM authors A, titleauthor TA, titles T WHERE A.au_id *= TA.au_id AND TA.title_id =* T.title_id AND (state <> 'CA' OR state IS NULL) GO
|