Exclude records if right table matches

bux Source

I have two table:

Table A:

+--+----+
|id|name|
+--+----+
|0 |foo |
|1 |bar |
|2 |baz |
+-------+

Table B:

+--+----+
|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:

  • bar
  • baz

How to write this SQL request with join (or similar performance method) ?

mysqlsql

Answers

answered 3 years ago Dmytro Shevchenko #1

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 B.

answered 3 years ago Tim Schmelter #2

You can use NOT EXISTS

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 EXISTS/NOT EXISTS. So it's slightly more efficient to use a LEFT JOIN approach.

http://explainextended.com/2009/09/18/not-in-vs-not-exists-vs-left-join-is-null-mysql/

In MS SQL-Server it's better to use the NOT EXISTS.

http://sqlperformance.com/2012/12/t-sql-queries/left-anti-semi-join

answered 3 years ago jpw #3

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.

comments powered by Disqus