Loading

select records...

select records...

Ankit Shukla

Ankit Shukla

10y1.1kSQL

Hi,

I have two tables with same column names. I need to select those records of table 1 which are not in table 2.

8 Replies

Know the answer? Post it — somebody with the same question will find it here.

snowal raj9y ago
it have 2 types

1.Using Not in

SELECT *
FROM Table1
WHERE ID NOT IN
(SELECT ID
FROM Table2)

but its not get all records in all time

2.Using Join

SELECT A.ID
FROM TABLE1 A
LEFT JOIN TABLE2 B ON A.ID=B.ID
WHERE A.ID IS NULL
Its Give Perfect result in any time
0
Manas Mohapatra10y ago
You can use NOTIN or EXCEPT or LeftJoin to achieve the result.
--Using NotIn
select * from table1 where productid NOT IN(select * from table2)

--USE EXCEPT
select * from table1
except
select * from table2
Difference between Except and NotIn is: Except return distinct records but NotIn doesn't.

http://dba.stackexchange.com/questions/83684/except-operator-vs-not-in
0
Bikesh Srivastava10y ago
Hi Amit,
I have already done this logic you can use query like this.
SELECT*
FROMTable2A
WHERENOTEXISTS
(
SELECT*
FROMTable1B
WHEREA.[Company]=B.[Company/Account]
);
Mark as answer if find exact answer.
0
Ananth G10y ago
1. SELECT a.id
2. FROM Table1 a
3. LEFT JOIN Table2 b ON a.id=b.id
4. WHERE a.id IS NULL
it help to you
0

Tapan Patel

Tapan Patel10y ago

If you are saying that you need to select the records for the column which are only present in table 1, then take a look at below link..

https://msdn.microsoft.com/en-us/library/ms188055.aspx

Please mark it as an answer if it resolves the issue.

0

Vinay Singh

Vinay Singh10y ago

Have a look of the query

SELECT *
FROM Table1
WHERE ID NOT IN
(SELECT ID
FROM Table2)

0

Jisny Av10y ago

You can use EXCEPT clause. Please go through below link

http://www.tutorialspoint.com/sql/sql-except-clause.htm

0

Praveen Sreeram

Praveen Sreeram10y ago

SELECT t1.name

FROM table1 t1

LEFT JOIN table2 t2 ON t2.name = t1.name

WHERE t2.name IS NULL

Hope it helps,

Thanks,

Prawin

0