MySQL - why LEFT JOIN and NULL condition on NON-NULL column are returning all the rows

Question

The MySQL query is :

     SELECT 
     p1.id,p1.name, p1.category_id, p1.price AS p1_price, p2.price as p2_price 
     FROM
     products p1 
     LEFT JOIN products p2 
     ON p1.category_id=p2.category_id AND  
     p1.price<p2.price AND p2.price IS NULL

The products table is shown in the following screenshot :

And the result the query produces is in the following screenshot :

The result actually shows the table as it is with just a column (i.e. p2_price) with all NULL values.

But the condition AND p2.price IS NULL in the ON condition seems to be never true as no row in the table p2 has NULL value in the price column. So how is the result produced ?

EDIT: this question never uses the term INNER JOIN and is therefore not any duplicate of any question regarding INNER JOIN.

EDIT2: This question is about MySQL, so it cannot be termed as duplicate of any question of other language i,e. SQL .

Predicate `p2.price IS NULL` is applied to the `LEFT JOIN` operation, i.e. to the rows returned by the left-joined table, `products p2` in this case. The predicate has no effect on the records returned from `products p1` table. — Giorgos Betsos, Nov 09 '17 at 13:49
A LEFT JOIN cannot be used to limit the returned rows. If you want to return only some of the rows based on a JOIN condition, use an INNER JOIN. — E. Villiger, Nov 09 '17 at 14:01
Please [use text, not images/links, for text](https://meta.stackoverflow.com/a/285557/3404097). — philipxy, Nov 09 '17 at 14:19
Read a definition of left join: it returns inner join rows plus unmatched left table rows extended by nulls. What reference are you using? What do you get when you apply its definition? — philipxy, Nov 09 '17 at 14:22
Possible duplicate of [What is the difference between "INNER JOIN" and "OUTER JOIN"?](https://stackoverflow.com/questions/38549/what-is-the-difference-between-inner-join-and-outer-join) Beware of all the poor answers there. *The most upvoted answer is very poor.* See my many comments & also [my answer](https://stackoverflow.com/a/46091641/3404097). — philipxy, Nov 09 '17 at 14:39
@philipxy, The OP is about MySQL whereas the question you pointed to is about SQL. — Istiaque Ahmed, Nov 09 '17 at 14:51
MySQL is a version of SQL, and for this question an "SQL" answer is a MySQL answer, and anyway there are zillions of other answers, which you will discover if you search, which should be done before asking. You should also have read a definition, tried to apply it, referenced it here, and shown your efforts here, as I also already commented. Also, the *answers* (that are not poor) to the duplicate question answer your question. Please read [ask], the downvote arrow mouseover text, and hits googling 'stackexchange homework'. — philipxy, Nov 09 '17 at 15:37
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/158621/discussion-between-istiaque-ahmed-and-philipxy). — Istiaque Ahmed, Nov 09 '17 at 16:39
@philipxy, the question arose out of a vague concept about `LEFT JOIN` which the asker could not identify himself. Question can be marked as duplicate only when same question has been asked again which obviously is not the case here. Lots of questions are in SO that stem from same problem and can be solved with a clear concept about the issue but those cannot be marked as duplicate as far as I understand. — Istiaque Ahmed, Nov 09 '17 at 16:43

score 3 · Answer 1 · answered Nov 09 '17 at 13:49

3

You are using a LEFT JOIN, which means p1 will always keep its values, whereas p2 is only non-NULL when the JOIN conditions apply

Maybe you want to move some of the conditions to the WHERE clause:

SELECT 
p1.id,p1.name, p1.category_id, p1.price AS p1_price, p2.price as p2_price 
FROM
products p1 
LEFT JOIN products p2 ON p1.category_id=p2.category_id AND p1.price<p2.price
WHERE p2.price IS NULL

In cases like these it's important to distinguish between the JOIN ON conditions and the WHERE conditions. WHERE conditions apply to the entire result set.

answered Nov 09 '17 at 13:49

E. Villiger

876
10
27

Actually the `p2.price IS NULL` was in `WHERE` clause but I just want to inspect the result if it is inside the `ON` condition. " whereas p2 is only non-NULL when the JOIN conditions apply " -what do you mean ? – Istiaque Ahmed Nov 09 '17 at 13:56
1

It's the very basics of what a [LEFT JOIN](https://www.w3schools.com/sql/sql_join_left.asp) does. A left join always returns all columns from the original table (p1). From the dependent table (p2) it only returns the actual data if the JOIN conditions are True, otherwise the fields are set to NULL. – E. Villiger Nov 09 '17 at 14:01
'whereas p2 is only non-NULL when the JOIN conditions apply' - I think you meant : when all the JOIN conditions are fulfilled, right ? – Istiaque Ahmed Nov 09 '17 at 14:10
JOIN conditions apply === all the JOIN conditions are fulfilled - correct. – E. Villiger Nov 09 '17 at 14:16

Joe Taras · Answer 2 · 2017-11-09T14:04:32.130

2

Two things you must know for LEFT JOIN.

The first:

ON clause builds only the link between two tables. If left table doesn't match this link, informations about this table will return NULL.

If you want to cut these rows, you must move your conditions in WHERE clause

The second:

NULL comparing: Only if you use IS NULL / IS NOT NULL you can correctly process a condition with NULL. If you compare two fields (for example p1.price and p2.price) if one of them is null you haven't TRUE but UNKNOWN

edited Nov 09 '17 at 14:04

answered Nov 09 '17 at 13:49

Joe Taras

15,166
7
42
55

When the `ON` condition is applied to the `LEFT JOIN` , I get many rows. Does this mean that for all the rows in result, `p2.price` is `NULL` ? – Istiaque Ahmed Nov 09 '17 at 13:58
Yes, because you have told: give me the rows from table left where p1.category_id=p2.category_id AND p1.price – Joe Taras Nov 09 '17 at 14:04

score 1 · Accepted Answer · answered Nov 09 '17 at 14:13

The condition on p1.category_id = p2.category_id and p1.price < p2.price and p2.price is null is always false. Therefore the resulting left join is each row from products (p1) filled up with NULL values for the products (p2) columns, e.g.

1, 'LG P880 4X HD', 336, 3, NULL, NULL, NULL, NULL
2, 'Google Nexus 4', 299, 2, NULL, NULL, NULL, NULL
...
12, 'Abercrombie Lake Arnold Shirt', 60, 1, NULL, NULL, NULL, NULL

MySQL - why LEFT JOIN and NULL condition on NON-NULL column are returning all the rows

3 Answers3