Data in condition is not equal

Question

How to select all records in the table t2 which t2.t1_id has no coincidence with t1.id.

SELECT * FROM t2 LEFT JOIN t1 ON t1.id <> t2.t1_id

Any tips, link or code example would be useful.

Can you post table information and an example of the data you want to get? — Marshall Tigerus, Apr 29 '14 at 15:26
possible duplicate of [Return row only if value doesnt exists \[MYSQL\]](http://stackoverflow.com/questions/21633115/return-row-only-if-value-doesnt-exists-mysql) — Barmar, Apr 29 '14 at 15:29

score 2 · Accepted Answer · answered Apr 29 '14 at 15:27

2

If what you want is all t2 records without a matching id in t1, but no columns from t1, you could do:

 Select * from t2
 WHERE t2.t1_id NOT IN(Select id from T1)

This selects all records in t2, but then filters out those that exist in t1 based on t1_id

answered Apr 29 '14 at 15:27

Marshall Tigerus

score 1 · Answer 2 · answered Apr 29 '14 at 15:26

1

You can use a not in:

SELECT *
FROM   t2
WHERE  t2.t1_id not in (select id from t1)

answered Apr 29 '14 at 15:26

Patrick Hofman

score 1 · Answer 3 · answered Apr 29 '14 at 15:28

1

SELECT t2.* 
FROM t2 
LEFT JOIN t1 
    ON t1.id = t2.t1_id 
where t1.id is null

answered Apr 29 '14 at 15:28

sion_corn

MinhD · Answer 4 · 2014-04-29T15:38:49.713

1

Just want to add, NOT EXIST is better in most cases:

SELECT *
FROM   t2
WHERE  NOT EXIST (SELECT 1 FROM t1
                            WHERE t2.t1_id = t1.id)

Otherwise, you can use NOT IN or LEFT JOIN with NULL

edited Apr 29 '14 at 15:38

answered Apr 29 '14 at 15:32

MinhD

4 Answers4