Snippets v2

Languages
My Snippets

Select Data from Left Join that doesn't exist in b Login or Register to Bookmark this snippet

Description SQL

Selects data from two tables, that are left joined so matching data doesn't have to exist in the 2nd table for the data of the 1st to be returned.

Normally when selecting data from two tables with a "table1.field=table2.field" means that both tables must have a valid record for the row to be returned.

This particular query selects records from table1 that don't have a matching record in table2.

The Code Download
  1. SELECT
  2.     COUNT(b.id) AS cnt,
  3.     a.*
  4. FROM
  5.     table1 a
  6. LEFT JOIN
  7.     table2 b
  8. ON
  9.     b.id=a.id
  10. GROUP BY
  11.     a.id
  12. HAVING
  13.     cnt=0
Credits Contact JC

Added by JC on 14th November, 2007

Comments
25th January, 2008 7:45 am Joel Pearson
Using a GROUP BY in this instance isn't the best idea, because you are relying on the fact that MySQL allows you to select other columns that aren't using Group By (Aggregate) Functions.
I know Postgres doesn't allow this either does Microsoft SQL Server.
Also using a GROUP BY would be a more expensive operation anyway.
You'd be better off just to check if b.id IS NULL in the WHERE clause. Eg

SELECT
a.*
FROM
table1 a
LEFT JOIN
table2 b
ON
b.id=a.id
WHERE b.id IS NULL
Post Comment HTML is allowed

You must be registered and logged in to post a comment.

Login here to post a comment