CODEWITHSUNDEEP
sqlsql-serverjoin
Karthick NS
Karthick NS

Reputation: 88

Need to fetch records from one table which is not present in another one table

I have two tables, one table has three columns another one has two columns. I need to fetch records from Table1 which is not present in Table2. For example

DECLARE @Table1 TABLE (C1 INT, C2 INT, C3 INT)
INSERT INTO @Table1 VALUES(1,1,1)
INSERT INTO @Table1 VALUES(1,2,2)
INSERT INTO @Table1 VALUES(1,3,3)
INSERT INTO @Table1 VALUES(2,1,4)

DECLARE @Table2 TABLE (C1 INT, C2 INT)
INSERT INTO @Table2 VALUES(1,1)
INSERT INTO @Table2 VALUES(1,2)

I need the result as shown below

C1 C2 C3
--------
1  3   3
2  1   4

Upvotes: 1

Views: 665

Answers (2)

Rus925
Rus925

Reputation: 408

This should work: 

SELECT Table1.* 
FROM Table1 
LEFT OUTER JOIN Table2 
    ON Table1.C1 = Table2.C1 
    AND Table1.C2 = Table2.C2 
WHERE Table2.C1 IS NULL
    AND Table2.C2 IS NULL

Upvotes: 2

Aditya Kakirde
Aditya Kakirde

Reputation: 5215

Please try this - 

SELECT t1.*
from table1 t1
where not exists (select null from table2 t2
                   where t1.c1=t2.c1
                       and t1.c2=t2.c2);

Upvotes: 1

Related Questions