Search code examples
mysqlsqlleft-joinwhere-clause

Why is Mysql's left join condition not being honoured?

Orders table:

+----------------+
| Field          |
+----------------+
| orderNumber    |
| customerNumber |
+----------------+

OrderDetails Table:

+-----------------+
| Field           |
+-----------------+
| orderNumber     |
| productCode     |
+-----------------+

SELECT 
    o.orderNumber, 
    o.customerNumber, 
    d.orderNumber,
    d.productCode
FROM
    orders o
LEFT JOIN orderdetails d 
    ON ((o.orderNumber = d.orderNumber) AND (o.orderNumber = 10123)) ;

Result:

Results

I am expecting to see rows which have an orderNumber of only 10123.

Why are we seeing other order numbers included?

The condition of the join (i.e. o.orderNumber = 10123) should have excluded those other rows.

I could add o.orderNumber = 10123 into a WHERE clause, which would fix the problem, but it should work as expected when included in the ON clause. Because the condition would return false given that o.orderNumber IS NOT 10123, and thus should be excluded from the result set.

The MySQL database:
https://sp.mysqltutorial.org/wp-content/uploads/2018/03/mysqlsampledatabase.zip

Online version where you can type in commands: https://www.mysqltutorial.org/tryit/

Solution

  • If you want to filter the resultset on a given order number, youw ant that condition in the WHERE clause:

    SELECT ...
FROM orders o
LEFT JOIN orderdetails d ON d.orderNumber = o.orderNumber
WHERE o.orderNumber = 10123;

    This brings all orders for the given number, along with the corresponding order details. If there are no order details, you still get the order row(s) for the given number. If there is no order for that number, the resultset is empty.