SQL left join(left outer join, difference of putting condition under join and under where)

SQL Server LEFT JOIN: conditions in ON vs. WHERE clause

The following query finds the products that belong to the order id 100:

1 2 3 4 5 6 7 8 9 10

SELECT     product_name,     order_id FROM     production.products p LEFT JOIN sales.order_items o    ON o.product_id = p.product_id WHERE order_id = 100 ORDER BY     order_id;

Let’s move the condition order_id = 100 to the ON clause:

1 2 3 4 5 6 7 8 9 10 11

SELECT     p.product_id,     product_name,     order_id FROM     production.products p     LEFT JOIN sales.order_items o          ON o.product_id = p.product_id AND             o.order_id = 100 ORDER BY     order_id DESC;

The query returned all products but only the order with id 100 has the associated product’s information.

Consider these queries:

SELECT *
FROM Orders
LEFT JOIN OrderLines ON OrderLines.OrderID=Orders.ID
WHERE Orders.ID = 12345

and

SELECT *
FROM Orders
LEFT JOIN OrderLines ON OrderLines.OrderID=Orders.ID 
    AND Orders.ID = 12345

The first will return an order and its lines, if any, for order number 12345. The second will return all orders, but only order 12345 will have any lines associated with it.

With an INNER JOIN, the clauses are effectively equivalent. However, just because they are functionally the same, in that they produce the same results, does not mean the two kinds of clauses have the same semantic meaning.

Same for mysql

https://www.sqlservertutorial.net/sql-server-basics/sql-server-left-join/

https://stackoverflow.com/questions/354070/sql-join-where-clause-vs-on-clause

https://www.mysqltutorial.org/mysql-left-join.aspx