CODEWITHSUNDEEP
sql
Rocshy
Rocshy

Reputation: 3509

Difference between 2 SQL tables

I have two tables: tableA and tableB. How can I get all the records that are in tableA but not in tableB? I am interested in the proper way to do this, not workarounds. I've tried using a left join and then adding where ID is not null. It works, but I am not sure whether that is the right way to do it.

Upvotes: 1

Views: 1991

Answers (5)

Sahil
Sahil

Reputation: 9480

Since you have not given the table schema lets assume that there are primary key in each table.id1,id2

YOU Can do with the help of left join.

Select * from table1 left join table2 where id2 is null

NOte this is not an actual query just a hint for you to go ahead.

Upvotes: 0

Akash KC
Akash KC

Reputation: 16310

You can use NOT EXISTS. I prefer to use NOT EXISTS because when the columns allow nulls but has none, the NOT IN performs significantly worse than NOT EXISTS.

Select * from table1 a
    Where not exists (Select ID from table2 b where a.ID = b.ID)

On non-nullable columns, you can use either of them which fits to your situation as the behaviour and performance of NOT IN and NOT EXISTS are the same.

Upvotes: 0

Satinder singh
Satinder singh

Reputation: 10198

Sql fiddle [][1]

Demo http://www.sqlfiddle.com/#!3/10300/2

select * from table1 where id not in(select id from table2)

Upvotes: 0

user240141
user240141

Reputation:

You have to use join for this if you need to show the data on the page (programming), in case you need to move your data from one table to another table for backup or other similar purpose, you need a sql compare tool. For first on you need sql left join. Make your table A as left one and put and join with B

select * from A
left join B  on A.id = B.id
where b.id is null

Upvotes: 0

juergen d
juergen d

Reputation: 204756

select * from tableA
where id not in (select id from tableB)

or 

select * from tableA a
left join tableB b on a.id = b.id
where b.id is null

Both are totally acceptable ways to retrieve what you asked.

Upvotes: 3

Related Questions