database - How to retrieve non-matching results in mysql

Question

I'm sure this is straight-forward, but how do I write a query in mysql that joins two tables and then returns only those records from the first table that don't match. I want it to be something like:

Select tid from table1 inner join table2 on table2.tid = table1.tid where table1.tid != table2.tid;

but this doesn't seem to make alot of sense!

Answer 1

You can use a left outer join to accomplish this:

select
    t1.tid
from
    table1 t1
    left outer join table2 t2 on
        t1.tid = t2.tid
where
    t2.tid is null

What this does is it takes your first table (table1), joins it with your second table (table2), and fills in null for the table2 columns in any row in table1 that doesn't match a row in table2. Then, it filters that out by selecting only the table1 rows where no match could be found.

Alternatively, you can also use not exists:

select
    t1.tid
from
    table1 t1
where
    not exists (select 1 from table2 t2 where t2.tid = t1.tid)

This performs a left semi join, and will essentially do the same thing that the left outer join does. Depending on your indexes, one may be faster than the other, but both are viable options. MySQL has some good documentation on optimizing the joins, so you should check that out..