join two tables and select records only if NOT in table 2

Question

I would like to join two tables and only print records from table 1 where the rec_number is NOT in table 2.

table 1
name        rec_number
john smith   123
Bob jonson   345
etc

Table 2 
Bob jonson   345
etc

What is the query in php that would do this so the query only gives me John smith, not bob jonson. is it:

    $query = "select * from table1
    left join rec_number on table1.rec_number = table2.rec_number";
    $result=mysql_query($query);

Thank you.

possible duplicate of [Mysql select where not in table](http://stackoverflow.com/questions/354002/mysql-select-where-not-in-table) — Marcus Adams, Apr 28 '14 at 20:37

score 2 · Answer 1 · answered Apr 28 '14 at 20:25

2

You can use this query

select 
t1.*
from table1 t1
left join table2 t2 
on t2.rec_number = t1.rec_number
where t2.rec_number IS NULL

answered Apr 28 '14 at 20:25

Abhik Chakraborty

2

Also known as an [antijoin](http://en.wikipedia.org/wiki/Relational_algebra#Antijoin_.28.E2.96.B7.29). – Marcus Adams Apr 28 '14 at 20:33
here is a nice article with examples of different joins . http://www.mysqldiary.com/mysql-left-join/ – Abhik Chakraborty Apr 28 '14 at 20:37

score 0 · Answer 2 · answered Apr 28 '14 at 20:35

0

Beside the left join mentioned by Abhik, you could also use a subselect:

SELECT * FROM table1 WHERE table1.name NOT IN (SELECT name FROM table2);

answered Apr 28 '14 at 20:35

dognose

2 Answers2