Mysql – How does LEFT JOIN with WHERE clause works

join;MySQL

Given the query below

    SELECT * FROM tableA 
    LEFT JOIN tableB ON tableB.id = tableA.id
    WHERE tableA.name = 'e'

tableA                        tableB
-----------                   ----------- 
id    name                     id    school
-----------                   -----------  
1     a                         1     AA
2     b                         2     BB                     
3     c                         3     CC                     
4     d                         4     DD                     
5     e                         5     EE

What baffles me is the process that is happening behind.

Does the system JOINs first both tableA and tableB before they select only the row from the WHERE clause?

If yes, then what about this query

SELECT * FROM 
(
    SELECT * FROM tableA     
    WHERE name = 'e'

) A LEFT JOIN tableB ON tableB.id = A.id

Does this query select first using the WHERE clause before it JOINs the other table?

Sorry I can't find the answer on the web. I wanted to lessen the MySQL resource bandwidth. Does the second query is the best for this situation?

Best Answer

The query optimizer will decide and it is pretty smart.

SELECT * FROM tableA 
LEFT JOIN tableB 
  ON tableB.id = tableA.id
WHERE tableA.name = 'e'

There are many cases where doing the join first is more efficient. If name is indexed and 'e' is somewhat unique then do that first is more efficient.

This is different

SELECT * FROM tableA 
LEFT JOIN tableB 
  ON tableB.id = tableA.id
WHERE tableB.school = 'EE'

That changes the left join into an inner join but since there is a match in your data you would get the same results.

Related Solutions

Sql-server – Is it correct, order of where clause doesn’t matter when it is used with join

The order of items in the where clause should not make a difference, especially if you use the preferred join syntax as follows:

select a.col1, 
       b.col2 
 from table1 a 
 join table2 b on b.col1 = a.col1
where a.col3 = 10

This keeps the join conditions separated from the filters.

LEFT JOIN conversion to INNER JOIN, can’t change FROM clause

That one caused me quite a bit of headache before I found a halfway elegant solution:

WITH x AS (
    SELECT BASE.id AS b_id
          ,BASE.start_date AS b_start_date
          ,BASE.end_date AS b_end_date
          ,CHILD.id AS c_id
          ,CHILD.start_date AS c_start_date
          ,CHILD.end_date AS c_end_date
          ,CASE WHEN DATE '2011-1-1' BETWEEN CHILD.start_date AND CHILD.end_date
                                     OR CHILD.id IS NULL THEN 0 ELSE 1 END AS c_hit
    FROM   BASE LEFT OUTER JOIN CHILD ON (BASE.ID=CHILD.ID)
    WHERE  BASE.ID = 1
    AND    DATE '2011-1-1' BETWEEN BASE.start_date AND BASE.end_date
    )
SELECT b_id, b_start_date, b_end_date
      ,c_id, c_start_date, c_end_date
FROM   x
WHERE  x.c_hit = 0

UNION ALL
SELECT b_id, b_start_date, b_end_date
      ,NULL, NULL,         NULL
FROM   x
GROUP  BY b_id, b_start_date, b_end_date
HAVING min(c_hit) = 1;

Of course, the better solution would be to get rid of the restrictions and adjust the JOIN in the base query. As @a_horse expressed it so poetically in the comments above:

the perils of ORM frameworks

Anyway, to solve the question at hand:
After selecting rows where BASE.id and BASE.start_date / BASE.end_date match, we have four different cases:

No child at all, so CHILD.id is NULL.
Child matches.
Child does not match and no other matching child.
Child does not match but other matching child.

This is hard to solve on one query level. You could do it with window functions and CASE statements and DISTINCT, in fact I had a basic working query, but it gets monstrous.

Using a CTE dramatically simplifies the syntax.
In the CTE:

Filter BASE on id and date range. Still includes rows with non-matching children.
Assign aliases for identical column names
Tag rows where child matches the date

In the final SELECT:

Return all rows with matching children
Return one row where no matching children exist, with NULLs for child-columns.

Here is working demo on sqlfiddle.com for those who want to toy with it.

Best Answer

Related Solutions

Sql-server – Is it correct, order of where clause doesn’t matter when it is used with join

LEFT JOIN conversion to INNER JOIN, can’t change FROM clause

Related Question