Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

What's the best way to use LEFT OUTER JOIN to check for non-existence of related rows

Using MySQL 5.x I want to efficiently select all rows from table X where there is no related row in table Y satisfying some condition, e.g.

Give me all records in X where a related Y with foo = bar does NOT exist

SELECT count(id) FROM X
LEFT OUTER JOIN Y ON y.X_id = X.id AND y.foo = 'bar'
WHERE y....?

As I understand it, a left outer join is guaranteed to produce a row for each row in the left (first) table -- X in this case -- whether or not a satisfying row in the joined table was found. What I want to do is then select only those rows where no row was found.

It seems to me that y.X_id should be NULL if there is no matching record, but this test doesn't seem to work. Nor does y.X_id = 0 or !y.X_id.

Edits: corrected transcription error (ON not AS) which was pointed out by several responses. Fixed grammatical error.

like image 279
podperson Avatar asked May 04 '11 15:05

podperson


People also ask

Does outer join result include non-matching rows?

In SQL Full Outer Join, all rows from both the tables are included. If there are any unmatched rows, it shows NULL values for them.

When would you use a left outer join?

A left outer join is a method of combining tables. The result includes unmatched rows from only the table that is specified before the LEFT OUTER JOIN clause. If you are joining two tables and want the result set to include unmatched rows from only one table, use a LEFT OUTER JOIN clause or a RIGHT OUTER JOIN clause.

How can I check left outer join?

Left Outer Join: Left Outer Join returns all the rows from the table on the left and columns of the table on the right is null padded. Left Outer Join retrieves all the rows from both the tables that satisfy the join condition along with the unmatched rows of the left table. SELECT [column1, column2, ....]

How do you use join for not exists in SQL?

The LEFT OUTER JOIN will return all rows from the left table, both where rows exist in the related table and where they does not. The WHERE NOT EXISTS() subquery will only return rows where the relationship is not met.


2 Answers

SELECT count(id) FROM X 
LEFT OUTER JOIN Y ON (y.X_id = X.id AND y.foo = 'bar')
WHERE y.X_id is null

You were close.

First do the join as normal, then select all rows for which a not null row in Y is in fact null, so you are sure there's a "no match" and not just a null value in Y.

Also note the typo (since corrected) you made in the query:

LEFT OUTER JOIN Y AS
-- should be
LEFT OUTER JOIN Y ON
-- This however is allowed
LEFT OUTER JOIN table2 as Y ON ....
like image 186
Johan Avatar answered Oct 15 '22 23:10

Johan


Checking if the primary key of table Y is NULL would do the trick, which tells the join did not matched :

SELECT count(id) FROM X 
LEFT OUTER JOIN Y ON (y.X_id = X.id AND y.foo = 'bar')
WHERE y.Y_id is null
like image 28
MaxiWheat Avatar answered Oct 16 '22 00:10

MaxiWheat