CODEWITHSUNDEEP
mysqljoinwhere-clause
Woods Chen
Woods Chen

Reputation: 620

MySQL left join: null elements be filtered by where

I have 2 MySQL tables: join_test_l and join_test_r.

join_test_l:

+------+------+
| ca   | cb   |
+------+------+
| a    | s    |
| b    | s    |
| c    | d    |
| d    | NULL |
+------+------+

join_test_r:

+------+------+
| cc   | cb   |
+------+------+
| a    | NULL |
| b    | s    |
| c    | d    |
| d    | NULL |
+------+------+

and when I tried to query with left join and a <> filter on the left table:

select * from join_test_l as l left join join_test_r as r on l.cb=r.cb where l.ca<>'c';
+------+------+------+------+
| ca   | cb   | cc   | cb   |
+------+------+------+------+
| a    | s    | b    | s    |
| b    | s    | b    | s    |
| d    | NULL | NULL | NULL |
+------+------+------+------+

the rows where l.ca is null remained. and when I tried to query with left join and a <> filter on the right table:

select * from join_test_l as l left join join_test_r as r on l.cb=r.cb where r.cc<>'c';
+------+------+------+------+
| ca   | cb   | cc   | cb   |
+------+------+------+------+
| a    | s    | b    | s    |
| b    | s    | b    | s    |
+------+------+------+------+

all the rows where r.cc is null is also removed.

can any explain this? I mean why the null values in the result set is filtered by <> clause?

Upvotes: 0

Views: 1048

Answers (2)

philipxy
philipxy

Reputation: 15156

Re <> & null

x<>y is true when x is not equal (in the normal sense) to y and x is not null and y is not null. It is false when x is equal (in the normal sense) to y and x is not null and y is not null. Otherwise it is technically unknown, but that happens to be treated as if it were null by operators/syntax like is.

Most SQL operators are like that--they return their normal results when each argument is not null, and unknown (treated like null) otherwise. SQL operators are not the normal relational or mathematical operators with the same names; they treat (the values) unknown & null specially. (Saying that null is not a value is fuzzy unhelpful SQL culture rhetoric.)

Re "the "normal sense of equal", from the MySQL 8.0 Reference Manual 12.3.2 Comparison Functions and Operators:

Comparison operations result in a value of 1 (TRUE), 0 (FALSE), or NULL.

  • <=>

    NULL-safe equal. This operator performs an equality comparison like the = operator, but returns 1 rather than NULL if both operands are NULL, and 0 rather than NULL if one operand is NULL. The <=> operator is equivalent to the standard SQL IS NOT DISTINCT FROM operator.

Note on & where return rows for which the condition evaluates to true. Constraints are satisfied when they don't evaluate to false.

Re left join on & where

Learn what LEFT/RIGHT JOIN returns: INNER JOIN rows plus unmatched left/right table rows extended by NULLs. FULL JOIN returns INNER JOIN rows UNION ALL unmatched left & right table rows extended by NULLs. Always know what INNER JOIN you want as part of an OUTER JOIN. A WHERE or ON that requires a possibly NULL-extended column to be not NULL after an OUTER JOIN ON removes any rows extended by NULLs, ie leaves only INNER JOIN rows, ie "turns an OUTER JOIN into an INNER JOIN". You have that.

Read your SQL DBMS documentation.

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522636

You need to move the logic in the WHERE clause to the ON clause:

SELECT *
FROM join_test_l as l
LEFT JOIN join_test_r as r
    ON l.cb = r.cb AND r.cc <> 'c';

The issue here is that the WHERE is filtering off records from the result set. On the other hand, by moving the logic to the join, we retain every record in the left table join_test_l. Then, records which did not join to anything would have null for every column in join_test_r.

Upvotes: 1

Related Questions