Set difference in MySQL without primary key

Question

I want to do an EXCEPT/MINUS in MySQL. If primary keys were available, the obvious solution would be:

SELECT
    *
FROM
    table_a AS a
WHERE
    a.ID not in(
        SELECT
            b.ID
        FROM
            table_b AS b
    )

However, what if I don't have a key column and I want an actual set difference, i.e. taking into account all (possibly many) columns?

I want something like

SELECT * FROM table_a WHERE * NOT IN …

This isn't possible of course. I can't assign an entire row to a variable, can I? Any ideas?

Add some sample table data and the expected result - as well formatted text. — jarlh, Jan 16 '17 at 14:01

score 1 · Accepted Answer · answered Jan 16 '17 at 14:06

You can still use not in:

select a.*
from table_a a
where (a.col1, a.col2, . . . ) not in (select b.col1, b.col2, . . . from table_b b);

I would tend to use exists, however:

select a.*
from table_a a
where not exists (select 1
                  from table_b b
                  where a.col1 = b.col1 and a.col2 = b.col2 and . . .
                 );

Not exists usually works more intuitively if any columns have null. Plus, you can use <=>, the NULL-safe equality operator.

Set difference in MySQL without primary key

1 Answers1