Saturday, May 2, 2009

Outer join

List all accounts and their contacts, show contact with NULL value if an account has no contacts.
SELECT t1.ACCOUNT_NAME, t2.CONTACT_NAME
FROM ACCOUNT t1, CONTACT t2
WHERE t1.ACCOUNT_ID = t2.ACCOUNT_ID(+)

In SQL Server:
SELECT t1.ACCOUNT_NAME, t2.CONTACT_NAME
FROM ACCOUNT t1 LEFT JOIN
CONTACT t2 ON t1.ACCOUNT_ID = t2.ACCOUNT_ID

No comments:

Post a Comment