MySQL – on & where from join

1. JOIN

In SQL, the key word “join” means combine multiple tables into a logical table by some conditions. The conditions can follow after “on” or “where”.

2. The difference between “ON” and “WHERE”

2.1. Prepare testing data

There are three tables for clarify the different of “on” and “where”, take a look:

2.2. Condition on

SELECT * FROM `order` o 
LEFT JOIN `order_goods` og 
ON o.order_id = og.order_id;

Let us check the result:

The result means join has combined two tables, order table and order_goods table.

2.3. Condition on and

SELECT * FROM `order` o 
LEFT JOIN `order_goods` og 
ON o.order_id = og.order_id AND og.goods_id = '3000';

Take a look the result :

From the result of executed SQL, we can deduce the condition on works before left join. Further, in the process of executing SQL, first getting the intermediate result by condition on “og.goods_id = ‘3000’”, then combine(left join) the intermediate result with order table by condition “o.order_id = og.order_id”.

2.4. Condition where

SELECT * FROM `order` o 
LEFT JOIN `order_goods` og 
ON o.order_id = og.order_id 
WHERE og.goods_id = '3000';

Take a look the result:

Again, we can also deduce the process of executed SQL, first combining(left join) two tables by condition “ON o.order_id = og.order_id” to the intermediate result, then filter the intermediate result by where condition “og.goods_id = ‘3000’”.

The intermediate result is Temporary tables in MySQL.

3. Other example

SELECT o.order_id, g.goods_name, g.goods_id
FROM (`order` o LEFT JOIN `order_goods` og 
      ON o.order_id = og.order_id 
      AND og.goods_id != '3000')
LEFT JOIN goods g ON g.goods_id = og.goods_id
WHERE o.order_source = 20;