SQLTeam.com | Weblogs | Forums

Get Columns from both tables in NOT EXISTS

sql2008

#1

Hello,
I have 2 tables where only 2 columns are common in these tables. a.col1 = b.col1 and a.col2 = b.col2. Rest of the columns are different in both tables.
I have the following query and need to get columns from both tables:

SELECT *
FROM tableA a
WHERE NOT EXISTS( SELECT * FROM tableB b
WHERE a.col1 = b.col1
AND a.col2 = b.col2)

Thanks.


#2
SELECT a.*, b.*
FROM tableA a
LEFT JOIN tableB b ON a.col1 = b.col1 AND a.col2 = b.col2
WHERE b.col1 IS NULL and b.col2 IS NULL

Using LEFT JOIN/IS NULL is similar to NOT EXISTS but also allows you to get the column names from the right table.


#3

I guess I don't fully understand. If a row doesn't exist in the other table (B), there wouldn't be any columns to get from that table, would there? All results from that table will always be NULL.