学习SQL系列之查询数据(四)

Join语句

创建Join语句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT vend_name, prod_name, prod_price FROM Vendors, Products WHERE Vendors.vend_id = Products.vend_id;

+-----------------+---------------------+------------+
| vend_name | prod_name | prod_price |
+-----------------+---------------------+------------+
| Doll House Inc. | Fish bean bag toy | 3.49 |
| Doll House Inc. | Bird bean bag toy | 3.49 |
| Doll House Inc. | Rabbit bean bag toy | 3.49 |
| Bears R Us | 8 inch teddy bear | 5.99 |
| Bears R Us | 12 inch teddy bear | 8.99 |
| Bears R Us | 18 inch teddy bear | 11.99 |
| Doll House Inc. | Raggedy Ann | 4.99 |
| Fun and Games | King doll | 9.49 |
| Fun and Games | Queen doll | 9.49 |
+-----------------+---------------------+------------+

Join语句种类

Inner Joins

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT vend_name, prod_name, prod_price FROM Vendors INNER JOIN Products ON Vendors.vend_id = Products.vend_id;

+-----------------+---------------------+------------+
| vend_name | prod_name | prod_price |
+-----------------+---------------------+------------+
| Doll House Inc. | Fish bean bag toy | 3.49 |
| Doll House Inc. | Bird bean bag toy | 3.49 |
| Doll House Inc. | Rabbit bean bag toy | 3.49 |
| Bears R Us | 8 inch teddy bear | 5.99 |
| Bears R Us | 12 inch teddy bear | 8.99 |
| Bears R Us | 18 inch teddy bear | 11.99 |
| Doll House Inc. | Raggedy Ann | 4.99 |
| Fun and Games | King doll | 9.49 |
| Fun and Games | Queen doll | 9.49 |
+-----------------+---------------------+------------+

Join多张表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SELECT prod_name, vend_name, prod_price, quantity FROM OrderItems, Products, Vendors WHERE Products.vend_id = Vendors.vend_id AND OrderItems.prod_id = Products.prod_id AND order_num = 20007;

+---------------------+-----------------+------------+----------+
| prod_name | vend_name | prod_price | quantity |
+---------------------+-----------------+------------+----------+
| 18 inch teddy bear | Bears R Us | 11.99 | 50 |
| Fish bean bag toy | Doll House Inc. | 3.49 | 100 |
| Bird bean bag toy | Doll House Inc. | 3.49 | 100 |
| Rabbit bean bag toy | Doll House Inc. | 3.49 | 100 |
| Raggedy Ann | Doll House Inc. | 4.99 | 50 |
+---------------------+-----------------+------------+----------+

SELECT cust_name, cust_contact FROM Customers, Orders, OrderItems WHERE Customers.cust_id = Orders.cust_id AND OrderItems.order_num = Orders.order_num AND prod_id = 'RGAN01';

+---------------+--------------------+
| cust_name | cust_contact |
+---------------+--------------------+
| Fun4All | Denise L. Stephens |
| The Toy Store | Kim Howard |
+---------------+--------------------+

注意:

  • Join太多表的话,性能会急剧下降,请避免Join不必要的表
  • 不同的数据库支持的最多Join表的数量不一样,请查阅各自文档

Self Joins

1
2
3
4
5
6
7
8
SELECT cust_name, cust_contact FROM Customers AS C, Orders AS O, OrderItems AS OI WHERE C.cust_id = O.cust_id AND OI.order_num = O.order_num AND prod_id = 'RGAN01';

+---------------+--------------------+
| cust_name | cust_contact |
+---------------+--------------------+
| Fun4All | Denise L. Stephens |
| The Toy Store | Kim Howard |
+---------------+--------------------+
1
2
3
4
5
6
7
8
SELECT c1.cust_id, c1.cust_name, c1.cust_contact FROM Customers AS c1, Customers AS c2 WHERE c1.cust_name = c2.cust_name AND c2.cust_contact = 'Jim Jones';

+------------+-----------+--------------------+
| cust_id | cust_name | cust_contact |
+------------+-----------+--------------------+
| 1000000003 | Fun4All | Jim Jones |
| 1000000004 | Fun4All | Denise L. Stephens |
+------------+-----------+--------------------+

注意:

  • Oracle数据库不支持AS, 使用Cutomers c代替Customers AS C
  • Self-Joins通常用来替代从同一张表查询的子查询

Natural Joins

1
2
3
4
5
6
7
8
SELECT C.*, O.order_num, O.order_date, OI.prod_id, OI.quantity, OI.item_price FROM Customers AS C, Orders AS O, OrderItems AS OI WHERE C.cust_id = O.cust_id AND OI.order_num = O.order_num AND prod_id = 'RGAN01';

+------------+---------------+---------------------+-----------+------------+----------+--------------+--------------------+-----------------------+-----------+---------------------+---------+----------+------------+
| cust_id | cust_name | cust_address | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email | order_num | order_date | prod_id | quantity | item_price |
+------------+---------------+---------------------+-----------+------------+----------+--------------+--------------------+-----------------------+-----------+---------------------+---------+----------+------------+
| 1000000004 | Fun4All | 829 Riverside Drive | Phoenix | AZ | 88888 | USA | Denise L. Stephens | dstephens@fun4all.com | 20007 | 2012-01-30 00:00:00 | RGAN01 | 50 | 4.49 |
| 1000000005 | The Toy Store | 4545 53rd Street | Chicago | IL | 54545 | USA | Kim Howard | NULL | 20008 | 2012-02-03 00:00:00 | RGAN01 | 5 | 4.99 |
+------------+---------------+---------------------+-----------+------------+----------+--------------+--------------------+-----------------------+-----------+---------------------+---------+----------+------------+

Outer Joins

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

// Left Outer Join
SELECT Customers.cust_id, Orders.order_num FROM Customers LEFT OUTER JOIN Orders ON Customers.cust_id = Orders.cust_id;

+------------+-----------+
| cust_id | order_num |
+------------+-----------+
| 1000000001 | 20005 |
| 1000000001 | 20009 |
| 1000000002 | NULL |
| 1000000003 | 20006 |
| 1000000004 | 20007 |
| 1000000005 | 20008 |
+------------+-----------+

// Right Outer Join
SELECT Customers.cust_id, Orders.order_num FROM Customers RIGHT OUTER JOIN Orders ON Orders.cust_id = Customers.cust_id;

+------------+-----------+
| cust_id | order_num |
+------------+-----------+
| 1000000001 | 20005 |
| 1000000001 | 20009 |
| 1000000003 | 20006 |
| 1000000004 | 20007 |
| 1000000005 | 20008 |
+------------+-----------+

Join语句和聚合函数结合使用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
SELECT Customers.cust_id, COUNT(Orders.order_num) AS num_ord FROM Customers INNER JOIN Orders ON Customers.cust_id = Orders.cust_id GROUP BY Customers.cust_id;

+------------+---------+
| cust_id | num_ord |
+------------+---------+
| 1000000001 | 2 |
| 1000000003 | 1 |
| 1000000004 | 1 |
| 1000000005 | 1 |
+------------+---------+

SELECT Customers.cust_id, COUNT(Orders.order_num) AS num_ord FROM Customers LEFT OUTER JOIN Orders ON Customers.cust_id = Orders.cust_id GROUP BY Customers.cust_id;

+------------+---------+
| cust_id | num_ord |
+------------+---------+
| 1000000001 | 2 |
| 1000000002 | 0 |
| 1000000003 | 1 |
| 1000000004 | 1 |
| 1000000005 | 1 |
+------------+---------+

说明

  • 本文所有例子都来自SQL IN 10 MINUTES
  • 本文的所有例子都是基于MySQL,其它数据库可能会有微小差别,具体请查阅特定数据库的文档