联结比较
1、数据表
-
customers 表
包含:cust_id(顾客ID)、cust_name(顾客名字)、cust_address(顾客的地址)、cust_email(顾客的邮箱)
一共有 4 个字段、 5 条记录。
-
orders 表
包含:order_num(订单号)、order_date(订单日期)、cust_id(顾客号)
一共有 3 个字段、 5 条记录。
2、联结测试
2.1、联结(默认省略形式)
mysql> select *
-> from customers,orders;
+---------+-----------+----------------+-----------------+-----------+---------------------+---------+
| cust_id | cust_name | cust_address | cust_email | order_num | order_date | cust_id |
+---------+-----------+----------------+-----------------+-----------+---------------------+---------+
| 1 | Coyote | 111 Maple Lane | ylee@coyote.com | 205 | 2005-09-01 00:00:00 | 1 |
| 2 | Mouse | 222 Fromage | | 205 | 2005-09-01 00:00:00 | 1 |
| 3 | Wascals | 333 Sunny | wascals@qq.com | 205 | 2005-09-01 00:00:00 | 1 |
| 4 | Yosemite | 444 Riverside | NULL | 205 | 2005-09-01 00:00:00 | 1 |
| 5 | Fudd | 555 Street | NULL | 205 | 2005-09-01 00:00:00 | 1 |
| 1 | Coyote | 111 Maple Lane | ylee@coyote.com | 206 | 2005-09-12 00:00:00 | 3 |
| 2 | Mouse | 222 Fromage | | 206 | 2005-09-12 00:00:00 | 3 |
| 3 | Wascals | 333 Sunny | wascals@qq.com | 206 | 2005-09-12 00:00:00 | 3 |
| 4 | Yosemite | 444 Riverside | NULL | 206 | 2005-09-12 00:00:00 | 3 |
| 5 | Fudd | 555 Street | NULL | 206 | 2005-09-12 00:00:00 | 3 |
| 1 | Coyote | 111 Maple Lane | ylee@coyote.com | 207 | 2005-10-01 00:00:00 | 4 |
| 2 | Mouse | 222 Fromage | | 207 | 2005-10-01 00:00:00 | 4 |
| 3 | Wascals | 333 Sunny | wascals@qq.com | 207 | 2005-10-01 00:00:00 | 4 |
| 4 | Yosemite | 444 Riverside | NULL | 207 | 2005-10-01 00:00:00 | 4 |
| 5 | Fudd | 555 Street | NULL | 207 | 2005-10-01 00:00:00 | 4 |
| 1 | Coyote | 111 Maple Lane | ylee@coyote.com | 208 | 2005-11-15 00:00:00 | 5 |
| 2 | Mouse | 222 Fromage | | 208 | 2005-11-15 00:00:00 | 5 |
| 3 | Wascals | 333 Sunny | wascals@qq.com | 208 | 2005-11-15 00:00:00 | 5 |
| 4 | Yosemite | 444 Riverside | NULL | 208 | 2005-11-15 00:00:00 | 5 |
| 5 | Fudd | 555 Street | NULL | 208 | 2005-11-15 00:00:00 | 5 |
| 1 | Coyote | 111 Maple Lane | ylee@coyote.com | 209 | 2006-01-01 00:00:00 | 1 |
| 2 | Mouse | 222 Fromage | | 209 | 2006-01-01 00:00:00 | 1 |
| 3 | Wascals | 333 Sunny | wascals@qq.com | 209 | 2006-01-01 00:00:00 | 1 |
| 4 | Yosemite | 444 Riverside | NULL | 209 | 2006-01-01 00:00:00 | 1 |
| 5 | Fudd | 555 Street | NULL | 209 | 2006-01-01 00:00:00 | 1 |
+---------+-----------+----------------+-----------------+-----------+---------------------+---------+
25 rows in set (0.00 sec)
是笛卡尔积的结果,一共 25 条记录。
mysql> select *
-> from customers,orders
-> where customers.cust_id = orders.cust_id;
+---------+-----------+----------------+-----------------+-----------+---------------------+---------+
| cust_id | cust_name | cust_address | cust_email | order_num | order_date | cust_id |
+---------+-----------+----------------+-----------------+-----------+---------------------+---------+
| 1 | Coyote | 111 Maple Lane | ylee@coyote.com | 205 | 2005-09-01 00:00:00 | 1 |
| 3 | Wascals | 333 Sunny | wascals@qq.com | 206 | 2005-09-12 00:00:00 | 3 |
| 4 | Yosemite | 444 Riverside | NULL | 207 | 2005-10-01 00:00:00 | 4 |
| 5 | Fudd | 555 Street | NULL | 208 | 2005-11-15 00:00:00 | 5 |
| 1 | Coyote | 111 Maple Lane | ylee@coyote.com | 209 | 2006-01-01 00:00:00 | 1 |
+---------+-----------+----------------+-----------------+-----------+---------------------+---------+
5 rows in set (0.00 sec)
where 子句的重要性:等值联结。(因为在 orders 表中可知顾客 1、3、4、5 号有订单信息)
2.2、inner join 形式
mysql> select *
-> from customers inner join orders
-> where customers.cust_id = orders.cust_id;
+---------+-----------+----------------+-----------------+-----------+---------------------+---------+
| cust_id | cust_name | cust_address | cust_email | order_num | order_date | cust_id |
+---------+-----------+----------------+-----------------+-----------+---------------------+---------+
| 1 | Coyote | 111 Maple Lane | ylee@coyote.com | 205 | 2005-09-01 00:00:00 | 1 |
| 3 | Wascals | 333 Sunny | wascals@qq.com | 206 | 2005-09-12 00:00:00 | 3 |
| 4 | Yosemite | 444 Riverside | NULL | 207 | 2005-10-01 00:00:00 | 4 |
| 5 | Fudd | 555 Street | NULL | 208 | 2005-11-15 00:00:00 | 5 |
| 1 | Coyote | 111 Maple Lane | ylee@coyote.com | 209 | 2006-01-01 00:00:00 | 1 |
+---------+-----------+----------------+-----------------+-----------+---------------------+---------+
5 rows in set (0.00 sec)
inner join 的结果和默认省略的形式相同。
mysql> select *
-> from customers inner join orders
-> on customers.cust_id = orders.cust_id;
+---------+-----------+----------------+-----------------+-----------+---------------------+---------+
| cust_id | cust_name | cust_address | cust_email | order_num | order_date | cust_id |
+---------+-----------+----------------+-----------------+-----------+---------------------+---------+
| 1 | Coyote | 111 Maple Lane | ylee@coyote.com | 205 | 2005-09-01 00:00:00 | 1 |
| 3 | Wascals | 333 Sunny | wascals@qq.com | 206 | 2005-09-12 00:00:00 | 3 |
| 4 | Yosemite | 444 Riverside | NULL | 207 | 2005-10-01 00:00:00 | 4 |
| 5 | Fudd | 555 Street | NULL | 208 | 2005-11-15 00:00:00 | 5 |
| 1 | Coyote | 111 Maple Lane | ylee@coyote.com | 209 | 2006-01-01 00:00:00 | 1 |
+---------+-----------+----------------+-----------------+-----------+---------------------+---------+
5 rows in set (0.00 sec)
不过推荐使用 on 关键字。
2.3、自然联结
自然联结只考虑那些在两个关系模式中都出现的属性上取值相同的元组对(但要注意,自然联结是特殊的等值联结,但是等值联结不一定是自然联结。因为联结的表中可能存在两个或更多相同的列(属性)):
mysql> select *
-> from customers natural join orders;
+---------+-----------+----------------+-----------------+-----------+---------------------+
| cust_id | cust_name | cust_address | cust_email | order_num | order_date |
+---------+-----------+----------------+-----------------+-----------+---------------------+
| 1 | Coyote | 111 Maple Lane | ylee@coyote.com | 205 | 2005-09-01 00:00:00 |
| 3 | Wascals | 333 Sunny | wascals@qq.com | 206 | 2005-09-12 00:00:00 |
| 4 | Yosemite | 444 Riverside | NULL | 207 | 2005-10-01 00:00:00 |
| 5 | Fudd | 555 Street | NULL | 208 | 2005-11-15 00:00:00 |
| 1 | Coyote | 111 Maple Lane | ylee@coyote.com | 209 | 2006-01-01 00:00:00 |
+---------+-----------+----------------+-----------------+-----------+---------------------+
5 rows in set (0.01 sec)
2.4、外部联结
许多联结将一个表中的行与另一个表中的行相联结。但有时候会需要包含没有关联行的那些行。这种类型的联结称为外联结。
#检索所有用户,包括那些没有订单的客户
mysql> select *
-> from customers left join orders
-> on customers.cust_id = orders.cust_id;
+---------+-----------+----------------+-----------------+-----------+---------------------+---------+
| cust_id | cust_name | cust_address | cust_email | order_num | order_date | cust_id |
+---------+-----------+----------------+-----------------+-----------+---------------------+---------+
| 1 | Coyote | 111 Maple Lane | ylee@coyote.com | 205 | 2005-09-01 00:00:00 | 1 |
| 3 | Wascals | 333 Sunny | wascals@qq.com | 206 | 2005-09-12 00:00:00 | 3 |
| 4 | Yosemite | 444 Riverside | NULL | 207 | 2005-10-01 00:00:00 | 4 |
| 5 | Fudd | 555 Street | NULL | 208 | 2005-11-15 00:00:00 | 5 |
| 1 | Coyote | 111 Maple Lane | ylee@coyote.com | 209 | 2006-01-01 00:00:00 | 1 |
| 2 | Mouse | 222 Fromage | | NULL | NULL | NULL |
+---------+-----------+----------------+-----------------+-----------+---------------------+---------+
6 rows in set (0.00 sec)
outer 可以省略。
mysql> select *
-> from customers left outer join orders
-> on customers.cust_id = orders.cust_id;
+---------+-----------+----------------+-----------------+-----------+---------------------+---------+
| cust_id | cust_name | cust_address | cust_email | order_num | order_date | cust_id |
+---------+-----------+----------------+-----------------+-----------+---------------------+---------+
| 1 | Coyote | 111 Maple Lane | ylee@coyote.com | 205 | 2005-09-01 00:00:00 | 1 |
| 3 | Wascals | 333 Sunny | wascals@qq.com | 206 | 2005-09-12 00:00:00 | 3 |
| 4 | Yosemite | 444 Riverside | NULL | 207 | 2005-10-01 00:00:00 | 4 |
| 5 | Fudd | 555 Street | NULL | 208 | 2005-11-15 00:00:00 | 5 |
| 1 | Coyote | 111 Maple Lane | ylee@coyote.com | 209 | 2006-01-01 00:00:00 | 1 |
| 2 | Mouse | 222 Fromage | | NULL | NULL | NULL |
+---------+-----------+----------------+-----------------+-----------+---------------------+---------+
6 rows in set (0.00 sec)
对比如下:
mysql> select *
-> from customers inner join orders
-> on customers.cust_id = orders.cust_id;
+---------+-----------+----------------+-----------------+-----------+---------------------+---------+
| cust_id | cust_name | cust_address | cust_email | order_num | order_date | cust_id |
+---------+-----------+----------------+-----------------+-----------+---------------------+---------+
| 1 | Coyote | 111 Maple Lane | ylee@coyote.com | 205 | 2005-09-01 00:00:00 | 1 |
| 3 | Wascals | 333 Sunny | wascals@qq.com | 206 | 2005-09-12 00:00:00 | 3 |
| 4 | Yosemite | 444 Riverside | NULL | 207 | 2005-10-01 00:00:00 | 4 |
| 5 | Fudd | 555 Street | NULL | 208 | 2005-11-15 00:00:00 | 5 |
| 1 | Coyote | 111 Maple Lane | ylee@coyote.com | 209 | 2006-01-01 00:00:00 | 1 |
+---------+-----------+----------------+-----------------+-----------+---------------------+---------+
5 rows in set (0.00 sec)