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