定義Definition:
LEFT JOIN: Return all rows from the left table, and the matched rows from the right table
RIGHT JOIN: Return all rows from the right table, and the matched rows from the left table
INNER JOIN: Returns all rows when there is at least one match in BOTH tables
以下例子examples足以說明用法:
table customer
id name
001 Jack
002 John
003 Tom
table shopping
buy name
apple jack
orange jack
grape John
banana John
pear Mary
strawberry Mary
SELECT * FROM customer LEFT JOIN shopping ON customer.name = shopping.name
id name name buy
001 Jack Jack apple
001 Jack Jack orange
002 John John grape
002 John John banana
003 Tom null null
在這裡 LEFT JOIN 的用法比較像，顧客全部有誰，他們買什麼。沒買的人也要知道。
SELECT * FROM customer RIGHT JOIN shopping ON customer.name = shopping.name
id name name buy
001 Jack Jack apple
001 Jack Jack orange
002 John John grape
002 John John banana
null null Mary pear
null null Mary strawberry
在這裡 RIGHT JOIN 的用法比較像，全部賣了什麼，誰買。沒買的人不重要。
SELECT * FROM customer INNER JOIN shopping ON customer.name = shopping.name
id name name buy
001 Jack Jack apple
001 Jack Jack orange
002 John John grape
002 John John banana
在這裡 INNER JOIN 的用法比較像，什麼人，買了什麼。全部有誰，全部賣什麼不重要。
