CODEWITHSUNDEEP
mysqlsqljoin
otporan
otporan

Reputation: 1355

What SQL JOIN to use to get unique values across tables?

Lets say I have 2 tables T1 and T2. I need all values from table T1 that does not exist in table T2. How would that SQL look like?

T1:         T2:
T1.ID       T2.ID   
T1.Value    T2.T1ID 
T1.Date     T2.Value

Upvotes: 0

Views: 161

Answers (4)

Peter Å
Peter Å

Reputation: 1319

If I understand you correct you need something like this:

SELECT *
  FROM t1
 WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE t1.id = t2.t1id)

This will return all records in t1 that do not have any matching records in t2 (t1.id = t2.t1id).

Upvotes: 0

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52137

I need all values from table T1 that does not exist in table T2. How would that SQL look like?

You mean you need every T1.Value that does not match any T2.Value?

If so, you can use something like this:

SELECT * FROM T1
WHERE Value NOT IN (SELECT Value FROM T2)

MySQL, however, may not be very good at finding optimal plan for executing this query, so if your measurements show a performance problem, try something like this:

SELECT DISTINCT T1.*
FROM T1 LEFT JOIN T2 ON T1.Value = T2.Value
WHERE T2.ID IS NULL

Note the DISTINCT clause, which ensures T1 rows are not "multiplied" by the JOIN.

Or even like this, if you only care about T1.Value:

SELECT DISTINCT T1.Value
...

Upvotes: 2

dbd
dbd

Reputation: 531

select t1.* from t1
left join t2 on t1.id = t2.id
where t2.id is null

Upvotes: 0

ppeterka
ppeterka

Reputation: 20726

This is exactly what a LEFT JOIN is for. In your case:

SELECT T1.* 
FROM T1
LEFT JOIN T2 ON T1.ID=T2.T1ID
WHERE T2.ID IS NULL

Recommended reaading: MySQL JOIN Syntax

Upvotes: 1

Related Questions