Why does MySQL LEFT JOIN return "NULL" records when with a WHERE clause?

Question

Today I tried several more complex MySQL queries, and I noticed that MySQL LEFT JOIN does not work with the WHERE clause. I mean, it returns some records, but does not return those that are empty on the right side.

For example, let's say we have 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 does not return records with "NULL" (zero is not equal to 17 ...)

I hope you understand what I had in mind, and you will somehow explain the difference to me. Sorry for my poor English, this is not my native language.

Answer 1

The left join condition and the condition filter do not match. Data is filtered by the where clause after the completion of the physical connection. if you look at the left join, it will usually return every row from your left table, but as soon as you have a where clause, it will filter the join output so that the result is like an inner join. You want to focus on the two diagrams on the left side of the image below.

Answer 2

The first case:

After joining, records are filtered by clause (WHERE). Thus, only 1 result.

The second case (when replacing WHERE with AND)

Will return all connection records + records that satisfy the second condition (t2.rank! = 17). That's why here you get 2 entries (one from join + another from the AND clause)

Answer 3

"The LEFT JOIN keyword returns all the rows from the left table (table1) with the corresponding rows in the right table (table 2). The result is NULL on the right side when there is no match ."

Above quote from w3schools.com

However, when you put a WHERE clause in a LEFT JOIN, SQL now treats this as an INNER JOIN and:

"The INNER JOIN keyword selects all rows from both tables if there is a match between the columns in both tables."

Also quoted from w3schools.com

TL; DR;

Introducing a WHERE clause in a LEFT OUTER JOIN gives INNER JOIN behavior combining

Answer 4

The solution to this issue becomes quite intuitive if you are aware of the execution order or stages of processing logical SQL queries. Order: -

 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 

Since ON is executed before the OUTER (LEFT / RIGHT) part (by adding NULL-digit strings) JOIN, in the first case the rows are NULL in the ranks column. In the second case, the rows are filtered based on the values ​​of the rank column after executing the OUTER JOIN (LEFT JOIN here). Therefore, rows with NULL values ​​in the rank column are filtered out.

One very important thing that can be seen in your SQL query is a comparison with NULL This area requires special attention, since NULL values ​​when NULL / NON-NULL values ​​using ordinary arithmetic operators result in NULL (this is not TRUE and FALSE) because NULL means there is no value to compare. This behavior is defined in the ANSI SQL-92 standard. (This can be overridden by disabling the ansi null parameter (actual names may vary) on some SQL processors) Thus, your SQL statement with a where clause filters out rows with a NULL value in the rank column, which may seem to contradict intuition because of "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:

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 /