Why MySQL's LEFT JOIN is returning "NULL" records when with WHERE clause?
Solution 1
A left join condition and where condition filter are not both same. Data is filtered by the where clause after the physical join is done. if you look a left join it will normally return every row from your left table, but once you have a where clause, it will filter the output of the join so the result is like an inner join. You will want to focus on the two diagrams on the left side of the image below.
Solution 2
The solution to this question becomes quite intuitive once one is aware of the execution order or Logical Query Processing Phases of the SQL statement. The order is: -
1. FROM
2. ON
3. OUTER
4. WHERE
5. GROUP BY
6. CUBE | ROLLUP
7. HAVING
8. SELECT
9. DISTINCT
10. ORDER BY
11. TOP
As ON is performed before the OUTER(LEFT/RIGHT) part(adding NULL valued rows) of the JOIN, the 1st case has rows with NULL values in rank column. In the 2nd case the rows get filtered out based on the values of the rank column after the OUTER JOIN(LEFT JOIN here) is performed. Hence, the rows with NULL values in the rank column are filtered out.
One very important thing that can be noticed in your SQL query is the comparison with NULL This area requires special attention as the NULL values when with NULL/NON-NULL values using the normal arithmetic operators result NULL(it is neither TRUE nor FALSE) because NULL means no value is available for comparison. This behavior is defined in the ANSI SQL-92 standard.(This can be overridden by turning ansi null(actual name may vary) parameter off in some SQL processors) So, your SQL query with where clause filters out rows with NULL value in rank column which might seem counter-intuitive due to "17 != NULL" seems TRUE ex-
NULL = NULL results NULL/UNKNOWN
17 = NULL results NULL/UNKNOWN
17 != NULL results NULL?UNKNOWN
Some interesting posts/blogs for reference are:
http://blog.sqlauthority.com/2009/04/06/sql-server-logical-query-processing-phases-order-of-statement-execution/ http://blog.sqlauthority.com/2009/03/15/sql-server-interesting-observation-of-on-clause-on-left-join-how-on-clause-effects-resultset-in-left-join/ http://www.xaprb.com/blog/2006/05/18/why-null-never-compares-false-to-anything-in-sql/
Solution 3
First Case :
After joining, the records are getting filtered by the (WHERE clause). So only 1 result.
Second Case (when you replace WHERE with AND)
Will return all join entries + entries satisfied in second condition (t2.rank != 17). That is why here you get 2 records ( one from join + another from AND clause)
nass
Updated on May 24, 2020Comments
-
nass about 4 years
Today I've tried some more complex MySQL queries and I've noticed that MySQL's LEFT JOIN is not working with WHERE clause. I mean, it does return some records but it does not return the ones which are empty on the right side.
For example let's say we've got to tables:
albums ; albums_rap id artist title tracks ; id artist title rank ---- -------- ---------- --------------- ; ---- --------- ----- -------------- 1 John Doe Mix CD 20 ; 3 Mark CD #7 15 2 Mark CD #7 35 ;
And when I run this query:
SELECT t1.artist as artist, t1.title as title, t1.tracks as tracks, t2.rank as rank, FROM albums as t1 LEFT JOIN albums_rap as t2 ON t1.artist LIKE t2.artist AND t1.title LIKE t2.title WHERE t2.rank != 17
I get this:
artist title tracks rank ------ ----- ------ ----- Mark CD #7 35 15
but when I replace "WHERE" with "AND" in this query I get:
artist title tracks rank ------ --------- ------ ----- Mark CD #7 35 15 John Doe Mix CD 20 NULL
Why the first one is not returning records with "NULL" (null is not equal to 17...)
I hope You understood what I meant and you'll explain somehow me the difference. Sorry for my bad english, it's not my mother tongue.
-
David Faber over 9 yearsI don't believe there is any such thing as a LEFT INNER JOIN. There are only INNER JOINs. One can read more about this here: stackoverflow.com/questions/2389204/…
-
Baxny over 8 yearsFor each where condition that matches a left join table move the where condition up to the left join statement and use AND
-
Billal Begueradj about 5 yearsThis is the most useful contribution