Where to Put Filter Conditions in MySQL Joins

Table of contents

Article body

A simple demo illustrates the problem.

I joined Table A and Table B using project_id=4 as the filter. The query should return ID and name from Table A and project_id from Table B. If no record in Table B matches Table A, a.project_id should be null. My first SQL query looked like this:

SELECT
	t.id,t.name,a.project_id
FROM
	micrisevice AS t
	LEFT JOIN project_mirservie AS a ON a.micrservice_id = t.id
	WHERE t.STATUS = 0 AND a.project_id = 4 OR a.id is NULL

The problem appeared immediately. I expected the left join to return all records from the left-hand table (Table A here), but the results did not do that:

The record with ID 2 was missing. Why? At first I thought the problem was missing parentheses around the AND and OR conditions. Adding parentheses produced the same result, so I removed the conditions after t.STATUS=0 to see what the query returned:

These are the results after the left join. At this point I understood: the earlier conditions were being applied to this intermediate table. The condition where a.project_id= 4 therefore filtered out the rows whose project_id values were 3, 5, and 3. That was not what I originally wanted. The condition should instead go into the left join to produce the intermediate table I intended:

SELECT
	t.id,
	t.NAME,
	a.project_id
FROM
	micrisevice AS t
	LEFT JOIN project_mirservie AS a ON a.micrservice_id = t.id
	AND a.project_id = 4
	OR a.id IS NULL
WHERE
	t.STATUS = 0

Applying the WHERE condition to this intermediate table then gives the expected result.