Filtering Tables in Joins in MySQL

I had a situation recently where I was outer joining one table onto another. I needed all rows from the first table, plus any matching rows from the second table. So:

SELECT * 
FROM table1 t1
LEFT JOIN table2 t2 ON (t1.col_a = t2.col_a)

So far, so good, right? In this particular instance though I wanted to filter my results on a particular criteria so I added something like:

SELECT * 
FROM table1 t1
LEFT JOIN table2 t2 ON (t1.col_a = t2.col_a)
WHERE t2.col_b <> 42

This is where it all started to go a bit weird. I wanted all the rows from the first table, plus anything from the second table that matched but didn't have col_b equal to 42 (this is a rather contrived example I know but my real-world example was so complicated I'd still be explaining it if I started). The syntax above was causing a problem in the case that there was a single matching row in the second table which matched the criteria in the where clause. In this instance, the entry from the first table would appear once, and then immediately get filtered out by the constraint in the where clause.

What I actually needed to do was filter the second table before the join happens. So here's the syntax:

SELECT *
FROM table1 t1
LEFT JOIN table2 t2 ON (t1.col_a = t2.col_a AND t2.col_b <> 42)

It turns out that you can use the ON clause when you join to include anything that you would usually put in a where clause to restrict the rows on the table that is being joined. Hope this makes sense and is useful to someone, add your comments if you have any!

2 thoughts on “Filtering Tables in Joins in MySQL

  1. This caught me out once, big time. Now I know to filter out with the join clause.

    Not that I intend on using that knowledge again for a long time!

  2. Yes this can be a very useful feature of SQL, something I always thought was woefully lacking in Oracle.

    I generally prefer to keep joins and filters separate, for sake of readability. So would recommend using a view, or joining on to a subquery, e.g.

    [geshi lang=sql]
    SELECT *
    FROM table1 t1
    LEFT JOIN
    (
    SELECT *
    FROM table2
    WHERE NOT col_b = 42
    ) t2
    ON (t1.col_a = t2.col_a)
    [/geshi]

Leave a Reply

Please use [code] and [/code] around any source code you wish to share.

This site uses Akismet to reduce spam. Learn how your comment data is processed.