Rows of one table not present in other table

[pankajv]

I have 2 tables which have 2 columns in common. I want to get the rows from table 1 for which the combination of 2 columns does not exist in the other table.

For e.g:

TAB 1
Col1 Col2
1 2
1 3
2 1
2 2

TAB 2

Col1 Col2
1 2
1 4
2 1
2 0

Output should be:

Col1 Col2
1 3
2 2

 

[swampBoogie]

select tab1.col1, tab1.col2
from tabl left outer join tab2
on tab1.col1 = tab2.col1
and tab1.col2 = tab2.col2
where tab2.col1 is null

 

[Vancouverite]

Try:

Select t1.*
FROM Table1 t1
LEFT Join Table2 t2 ON
t1.Col1 = t2.Col1 AND t1.Col2 = t2.Col2
WHERE t2.Col1 Is Null

A LEFT JOIN returns all of the data in Table1, and any matching records in Table2. If Table2 does not have a matching record, then the Table2 Data is Null, so filtering on t2.Col1 Is Null returns only rows from Table1 with no records matching in Table2