SQL Select 多表查询

该语句用于从多个表中检索字段。为此,我们需要使用连接查询从多个表中获取数据。

让我们看一下从多个表中选择的示例:

SELECT orders.order_id, suppliers.name   
FROM suppliers  
INNER JOIN orders  
ON suppliers.supplier_id = orders.supplier_id  
ORDER BY order_id;  

让我们取三个表,两个名为 customer1 和 customer2 的客户表,第三个表是产品表。

customer1表:

Cus_id Name1
1 Jack
2 Jill

customer2表:

Cus_id Name2
1 Sandy
2 Venus

product表:

P_id Cus_id P_name
1 1 Laptop
2 2 Phone
3 P1 Pen
4 P2 Notebook

从多个表中选择的示例语法: 

SELECT p. p_id, p.cus_id, p.p_name, c1.name1, c2.name2  
FROM product AS p  
LEFT JOIN customer1 AS c1  
ON p.cus_id=c1.cus_id  
LEFT JOIN customer2 AS c2  
ON p.cus_id = c2.cus_id  
P_id Cus_id P_name P_name P_name
1 1 Laptop Jack NULL
2 2 Phone Jill NULL
3 P1 Pen NULL Sandy
4 P2 Notebook NULL Venus

热门文章

优秀文章