查詢多個表格的SQL SELECT語句

Question

如何取得所有來自customers1和customers2的產品（包括他們的客戶名稱）？

customer1 table
cid name1
1 john
2 joe

customer2 table
cid name2
p1 sandy
p2 linda

product table
pid cid pname
1 1 phone
2 2 pencil
3 p1 pen
4 p2 paper

結果應該是這樣的

pid cid pname name1 name2
1 1 phone john NULL
2 2 pencil joe NULL
3 p1 pen NULL sandy
4 p2 paper NULL linda

Answer 1

SELECT pid, cid, pname, name1, name2 
FROM customer1 c1, product p 
WHERE p.cid=c1.cid 
UNION SELECT pid, cid, pname, name1, name2 
FROM customer2 c2, product p 
WHERE p.cid=c2.cid;

Answer 2

SELECT p.pid, p.cid, p.pname, c1.name1, c2.name2
FROM product p
LEFT JOIN customer1 c1 ON p.cid = c1.cid
LEFT JOIN customer2 c2 ON p.cid = c2.cid