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 |
产品表
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 |