Retrieve the rows from T2 that are not in T1?
-
Hi,
Plz issume me a query in sql server to retrieve the rows from table-2 that are not present in table-1.
table-1 table-2
-------- ---------
101 101
102 102
103 105
107
From the table-2, I required rows 105 and 107.
regards
riyaz
-
A couple of ways but the way I use for simple situations like this is
SELECT t2.col
FROM t2
LEFT JOIN t1
ON t2.col = t1.col
WHERE t1.col is null
Left will match all t2 items against matching items in t1 and items with no match will cause t1 to be null. Thus the where eliminates all but the missing items.
-
Can do a 'not exists' but prefer Antares686 solution.
-
if you want only theose rows present in table2 but not in table1 then user the following query:
select * from table2 where col2 not in(select col1 from table1)
But if you want all the rows which are not common then use this:
select isnull(col_one,0),isnull(col_two,0) from table_one full outer join table_two
on col_one= col_two
where col_one is null or col_two is null
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply