I have two table:
+--+----+ |id|name| +--+----+ |0 |foo | |1 |bar | |2 |baz | +-------+
+--+----+ |A |cond| +--+----+ |0 |X | |1 |Y | +-------+
Where B.A column is A.id value.
I want to select all row from A where have no match in B table when B.cond = 'X'.
So, result should be:
How to write this SQL request with join (or similar performance method) ?mysqlsql
SELECT A.* FROM A LEFT JOIN B ON A.id = B.A AND B.cond = 'X' WHERE B.A IS NULL
This query joins the tables based on the conditions you specified, and then only selects the rows where there's no match in table
You can use
SELECT a.id, a.name FROM A WHERE NOT EXISTS ( SELECT 1 FROM B WHERE b.A = a.id AND b.cond = 'X' )
However, i always forget that MySql is the only(?) rdbms which has problems to optimize an
NOT EXISTS. So it's slightly more efficient to use a
LEFT JOIN approach.
In MS SQL-Server it's better to use the
In addition to the proposed solutions, using
not in would work too:
SELECT * FROM A WHERE ID NOT IN (SELECT A FROM B WHERE COND = 'X')
It should perform on par with a
left join and is a bit more compact.