CODEWITHSUNDEEP
mysqlsql
Niks
Niks

Reputation: 41

How to retrieve rows NOT common in two tables

I have two tables:

tata_data1:

Password | Region | Company | System
-------------------------------------
a02040   | Del    | xx      | abc
a01917   | Mum    | xxx     | pqr
a01916   | Mum    | x       | zzz
a01906   | Nag    | x       | pny

and tata_passwords:

Password | Region | Company
----------------------------
a02049   | Nag    | xxxx
a01917   | Mum    | xxx
a01000   | Del    | xx
a01906   | Nag    | x

I want to fetch only those rows from tata_passwords, which are not there in tata_data1. Consider Password as the primary key.

Upvotes: 0

Views: 74

Answers (3)

Nagaraj S
Nagaraj S

Reputation: 13484

select * 
from tata_passwords 
where tata_passwords.password NOT IN (select password from tata_data1)

Or you can use Mr.Kickstart said LEFT OUTER JOIN

SELECT tata_passwords.*
FROM tata_passwords LEFT OUTER JOIN tata_data1 ON tata_passwords.password=tata_data1.password 
where tata_data1.password IS NULL

Upvotes: 0

blue
blue

Reputation: 1949

Try this:

SELECT * FROM tata_passwords WHERE (Password, Region, Company) NOT IN ( SELECT Password, Region, Company FROM tata_data1 )

EDIT:

Now when Password is primary key, one could reduce the query to:

SELECT * FROM tata_passwords WHERE Password NOT IN ( SELECT Password FROM tata_data1 )

Upvotes: 0

Kickstart
Kickstart

Reputation: 21513

Using a LEFT OUTER JOIN:-

SELECT  tata_passwords.*
FROM  tata_passwords
LEFT OUTER JOIN tata_data1
ON tata_passwords.Password = tata_data1.Password
WHERE tata_data1.Password IS NULL

Upvotes: 1

Related Questions