How to combine 2 tables in 1 SQL request?

user_10787347 0
Viewed 148

I have 2 tables: 

A table: 
---+----------
id +  key

B table: 
-----+--------+---------
id   +  value +   key

"key" field in B is getting from A table.

I need all id from A table with a 'key' which are absent in B table.

mysql sql
4 Answers

Try this:

SELECT id FROM tablea a
WHERE NOT EXISTS (SELECT 1 FROM tableb b WHERE b.key = a.key)
user_104988280

Did you try to use JOIN? If I understood well, you want a full outer join (with exclusion), which is the same as a double "not in".

See picture below for more details.

enter image description here

user_46563660

NOT IN is one way:

SELECT id FROM A
WHERE key NOT IN (
    SELECT key FROM B)
user_10818970

What about a LEFT JOIN like

select a.id,a.key from A a
left join B b on a.key = b.key
where b.id is null
user_5674350
Related