MySQL连接使用及分类

sql连接

sql 连接（join）子句用于将数据库中两个或者两个以上表中的记录组合起来。连接通过共有值将不同表中的字段组合在一起。

考虑下面两个表，（a）customers 表：
+----+----------+-----+-----------+----------+
| id | name | age | address | salary |
+----+----------+-----+-----------+----------+
| 1 | ramesh | 32 | ahmedabad | 2000.00 |
| 2 | khilan | 25 | delhi | 1500.00 |
| 3 | kaushik | 23 | kota | 2000.00 |
| 4 | chaitali | 25 | mumbai | 6500.00 |
| 5 | hardik | 27 | bhopal | 8500.00 |
| 6 | komal | 22 | mp | 4500.00 |
| 7 | muffy | 24 | indore | 10000.00 |
+----+----------+-----+-----------+----------+
（b）另一个表是 orders 表：
+-----+---------------------+-------------+--------+
|oid | date | customer_id | amount |
+-----+---------------------+-------------+--------+
| 102 | 2009-10-08 00:00:00 | 3 | 3000 |
| 100 | 2009-10-08 00:00:00 | 3 | 1500 |
| 101 | 2009-11-20 00:00:00 | 2 | 1560 |
| 103 | 2008-05-20 00:00:00 | 4 | 2060 |
+-----+---------------------+-------------+--------+
现在，让我们用 select 语句将这个两张表连接（join）在一起：
sql> select id, name, age, amount
from customers, orders
where customers.id = orders.customer_id;
上述语句的运行结果如下所示：
+----+----------+-----+--------+
| id | name | age | amount |
+----+----------+-----+--------+
| 3 | kaushik | 23 | 3000 |
| 3 | kaushik | 23 | 1500 |
| 2 | khilan | 25 | 1560 |
| 4 | chaitali | 25 | 2060 |
+----+----------+-----+--------+
sql 连接类型
sql 中有多种不同的连接：
? 内连接（inner join）：当两个表中都存在匹配时，才返回行。
? 左连接（left join）：返回左表中的所有行，即使右表中没有匹配的行。
? 右连接（right join）：返回右表中的所有行，即使左表中没有匹配的行。
? 全连接（full join）：只要某一个表存在匹配，就返回行。
? 笛卡尔连接（cartesian join）：返回两个或者更多的表中记录集的笛卡尔积。
内连接
最常用也最重要的连接形式是 内连接，有时候也被称作“equijoin”（等值连接）。
内连接根据连接谓词来组合两个表中的字段，以创建一个新的结果表。sql 查询会比较逐个比较表 1 和表 2 中的
每一条记录，来寻找满足连接谓词的所有记录对。当连接谓词得以满足时，所有满足条件的记录对的字段将会结
合在一起构成结果表。
语法：
内连接的基本语法如下所示：
select table1.column1, table2.column2...
from table1
inner join table2
on table1.common_field = table2.common_field;
示例：
考虑如下两个表格，（a）customers 表：
+----+----------+-----+-----------+----------+
| id | name | age | address | salary |
+----+----------+-----+-----------+----------+
| 1 | ramesh | 32 | ahmedabad | 2000.00 |
| 2 | khilan | 25 | delhi | 1500.00 |
| 3 | kaushik | 23 | kota | 2000.00 |
| 4 | chaitali | 25 | mumbai | 6500.00 |
| 5 | hardik | 27 | bhopal | 8500.00 |
| 6 | komal | 22 | mp | 4500.00 |
| 7 | muffy | 24 | indore | 10000.00 |
+----+----------+-----+-----------+----------+
（b）orders 表：
+-----+---------------------+-------------+--------+
| oid | date | id | amount |
+-----+---------------------+-------------+--------+
| 102 | 2009-10-08 00:00:00 | 3 | 3000 |
| 100 | 2009-10-08 00:00:00 | 3 | 1500 |
| 101 | 2009-11-20 00:00:00 | 2 | 1560 |
| 103 | 2008-05-20 00:00:00 | 4 | 2060 |
+-----+---------------------+-------------+--------+
现在，让我们用内连接将这两个表连接在一起：
sql> select id, name, amount, date
from customers
inner join orders
on customers.id = orders.customer_id;
上述语句将会产生如下结果：
+----+----------+--------+---------------------+
| id | name | amount | date |
+----+----------+--------+---------------------+
| 3 | kaushik | 3000 | 2009-10-08 00:00:00 |
| 3 | kaushik | 1500 | 2009-10-08 00:00:00 |
| 2 | khilan | 1560 | 2009-11-20 00:00:00 |
| 4 | chaitali | 2060 | 2008-05-20 00:00:00 |
+----+----------+--------+---------------------+
左连接
左链接返回左表中的所有记录，即是右表中没有任何满足匹配条件的记录。这意味着，如果 on 子句在右表中匹
配到了 0 条记录，该连接仍然会返回至少一条记录，不过返回的记录中所有来自右表的字段都为 null。
这就意味着，左连接会返回左表中的所有记录，加上右表中匹配到的记录，或者是 null （如果连接谓词无法匹
配到任何记录的话）。
语法：
左连接的基本语法如下所示：
select table1.column1, table2.column2...
from table1
left join table2
on table1.common_field = table2.common_field;
这里，给出的条件可以是任何根据你的需要写出的条件。
示例：
考虑如下两个表格，（a）customers 表：
+----+----------+-----+-----------+----------+
| id | name | age | address | salary |
+----+----------+-----+-----------+----------+
| 1 | ramesh | 32 | ahmedabad | 2000.00 |
| 2 | khilan | 25 | delhi | 1500.00 |
| 3 | kaushik | 23 | kota | 2000.00 |
| 4 | chaitali | 25 | mumbai | 6500.00 |
| 5 | hardik | 27 | bhopal | 8500.00 |
| 6 | komal | 22 | mp | 4500.00 |
| 7 | muffy | 24 | indore | 10000.00 |
+----+----------+-----+-----------+----------+
（b）orders 表：
+-----+---------------------+-------------+--------+
| oid | date | id | amount |
+-----+---------------------+-------------+--------+
| 102 | 2009-10-08 00:00:00 | 3 | 3000 |
| 100 | 2009-10-08 00:00:00 | 3 | 1500 |
| 101 | 2009-11-20 00:00:00 | 2 | 1560 |
| 103 | 2008-05-20 00:00:00 | 4 | 2060 |
+-----+---------------------+-------------+--------+
现在，让我们用左连接将这两个表连接在一起：
sql> select id, name, amount, date
from customers
left join orders
on customers.id = orders.customer_id;
上述语句将会产生如下结果：
+----+----------+--------+---------------------+
| id | name | amount | date |
+----+----------+--------+---------------------+
| 1 | ramesh | null | null |
| 2 | khilan | 1560 | 2009-11-20 00:00:00 |
| 3 | kaushik | 3000 | 2009-10-08 00:00:00 |
| 3 | kaushik | 1500 | 2009-10-08 00:00:00 |
| 4 | chaitali | 2060 | 2008-05-20 00:00:00 |
| 5 | hardik | null | null |
| 6 | komal | null | null |
| 7 | muffy | null | null |
+----+----------+--------+---------------------+
左连接
右链接返回左表中的所有记录，即是左表中没有任何满足匹配条件的记录。这意味着，如果 on 子句在左表中匹
配到了 0 条记录，该连接仍然会返回至少一条记录，不过返回的记录中所有来自左表的字段都为 null。
这就意味着，右连接会返回右表中的所有记录，加上左表中匹配到的记录，或者是 null （如果连接谓词无法匹
配到任何记录的话）。
语法：
右连接的基本语法如下所示：
select table1.column1, table2.column2...
from table1
right join table2
on table1.common_field = table2.common_field;
这里，给出的条件可以是任何根据你的需要写出的条件。
示例：
考虑如下两个表格，（a）customers 表：
+----+----------+-----+-----------+----------+
| id | name | age | address | salary |
+----+----------+-----+-----------+----------+
| 1 | ramesh | 32 | ahmedabad | 2000.00 |
| 2 | khilan | 25 | delhi | 1500.00 |
| 3 | kaushik | 23 | kota | 2000.00 |
| 4 | chaitali | 25 | mumbai | 6500.00 |
| 5 | hardik | 27 | bhopal | 8500.00 |
| 6 | komal | 22 | mp | 4500.00 |
| 7 | muffy | 24 | indore | 10000.00 |
+----+----------+-----+-----------+----------+
（b）orders 表：
+-----+---------------------+-------------+--------+
| oid | date | id | amount |
+-----+---------------------+-------------+--------+
| 102 | 2009-10-08 00:00:00 | 3 | 3000 |
| 100 | 2009-10-08 00:00:00 | 3 | 1500 |
| 101 | 2009-11-20 00:00:00 | 2 | 1560 |
| 103 | 2008-05-20 00:00:00 | 4 | 2060 |
+-----+---------------------+-------------+--------+
现在，让我们用右连接将这两个表连接在一起：
sql> select id, name, amount, date
from customers
right join orders
on customers.id = orders.customer_id;
上述语句将会产生如下结果：
+------+----------+--------+---------------------+
| id | name | amount | date |
+------+----------+--------+---------------------+
| 3 | kaushik | 3000 | 2009-10-08 00:00:00 |
| 3 | kaushik | 1500 | 2009-10-08 00:00:00 |
| 2 | khilan | 1560 | 2009-11-20 00:00:00 |
| 4 | chaitali | 2060 | 2008-05-20 00:00:00 |
+------+----------+--------+---------------------+
全连接
全连接将左连接和右连接的结果组合在一起。
语法：
全连接的基本语法如下所受：
select table1.column1, table2.column2...
from table1
full join table2
on table1.common_field = table2.common_field;
这里，给出的条件可以是任何根据你的需要写出的条件。
示例：
考虑如下两个表格，（a）customers 表：
+----+----------+-----+-----------+----------+
| id | name | age | address | salary |
+----+----------+-----+-----------+----------+
| 1 | ramesh | 32 | ahmedabad | 2000.00 |
| 2 | khilan | 25 | delhi | 1500.00 |
| 3 | kaushik | 23 | kota | 2000.00 |
| 4 | chaitali | 25 | mumbai | 6500.00 |
| 5 | hardik | 27 | bhopal | 8500.00 |
| 6 | komal | 22 | mp | 4500.00 |
| 7 | muffy | 24 | indore | 10000.00 |
+----+----------+-----+-----------+----------+
（b）orders 表：
+-----+---------------------+-------------+--------+
| oid | date | id | amount |
+-----+---------------------+-------------+--------+
| 102 | 2009-10-08 00:00:00 | 3 | 3000 |
| 100 | 2009-10-08 00:00:00 | 3 | 1500 |
| 101 | 2009-11-20 00:00:00 | 2 | 1560 |
| 103 | 2008-05-20 00:00:00 | 4 | 2060 |
+-----+---------------------+-------------+--------+
现在让我们用全连接将两个表连接在一起：
sql> select id, name, amount, date
from customers
full join orders
on customers.id = orders.customer_id;
上述语句将会产生如下结果：
+------+----------+--------+---------------------+
| id | name | amount | date |
+------+----------+--------+---------------------+
| 1 | ramesh | null | null |
| 2 | khilan | 1560 | 2009-11-20 00:00:00 |
| 3 | kaushik | 3000 | 2009-10-08 00:00:00 |
| 3 | kaushik | 1500 | 2009-10-08 00:00:00 |
| 4 | chaitali | 2060 | 2008-05-20 00:00:00 |
| 5 | hardik | null | null |
| 6 | komal | null | null |
| 7 | muffy | null | null |
| 3 | kaushik | 3000 | 2009-10-08 00:00:00 |
| 3 | kaushik | 1500 | 2009-10-08 00:00:00 |
| 2 | khilan | 1560 | 2009-11-20 00:00:00 |
| 4 | chaitali | 2060 | 2008-05-20 00:00:00 |
+------+----------+--------+---------------------+
如果你所用的数据库不支持全连接，比如 mysql，那么你可以使用 union all子句来将左连接和右连接结果
组合在一起：
sql> select id, name, amount, date
from customers
left join orders
on customers.id = orders.customer_id
union all
select id, name, amount, date
from customers
right join orders
on customers.id = orders.customer_id
笛卡尔连接（交叉连接）
笛卡尔连接或者 交叉连接返回两个或者更多的连接表中记录的笛卡尔乘积。也就是说，它相当于连接谓词总是为
真或者缺少连接谓词的内连接。
语法：
笛卡尔连接或者说 交叉连接的基本语法如下所示：
select table1.column1, table2.column2...
from table1, table2 [, table3 ]
示例：
考虑如下两个表格，（a）customers 表：
+----+----------+-----+-----------+----------+
| id | name | age | address | salary |
+----+----------+-----+-----------+----------+
| 1 | ramesh | 32 | ahmedabad | 2000.00 |
| 2 | khilan | 25 | delhi | 1500.00 |
| 3 | kaushik | 23 | kota | 2000.00 |
| 4 | chaitali | 25 | mumbai | 6500.00 |
| 5 | hardik | 27 | bhopal | 8500.00 |
| 6 | komal | 22 | mp | 4500.00 |
| 7 | muffy | 24 | indore | 10000.00 |
+----+----------+-----+-----------+----------+
（b）orders 表：
+-----+---------------------+-------------+--------+
| oid | date | id | amount |
+-----+---------------------+-------------+--------+
| 102 | 2009-10-08 00:00:00 | 3 | 3000 |
| 100 | 2009-10-08 00:00:00 | 3 | 1500 |
| 101 | 2009-11-20 00:00:00 | 2 | 1560 |
| 103 | 2008-05-20 00:00:00 | 4 | 2060 |
+-----+---------------------+-------------+--------+
现在，让我用内连接将这两个表连接在一起：
sql> select id, name, amount, date
from customers, orders;
上述语句将会产生如下结果：
+----+----------+--------+---------------------+
| id | name | amount | date |
+----+----------+--------+---------------------+
| 1 | ramesh | 3000 | 2009-10-08 00:00:00 |
| 1 | ramesh | 1500 | 2009-10-08 00:00:00 |
| 1 | ramesh | 1560 | 2009-11-20 00:00:00 |
| 1 | ramesh | 2060 | 2008-05-20 00:00:00 |
| 2 | khilan | 3000 | 2009-10-08 00:00:00 |
| 2 | khilan | 1500 | 2009-10-08 00:00:00 |
| 2 | khilan | 1560 | 2009-11-20 00:00:00 |
| 2 | khilan | 2060 | 2008-05-20 00:00:00 |
| 3 | kaushik | 3000 | 2009-10-08 00:00:00 |
| 3 | kaushik | 1500 | 2009-10-08 00:00:00 |
| 3 | kaushik | 1560 | 2009-11-20 00:00:00 |
| 3 | kaushik | 2060 | 2008-05-20 00:00:00 |
| 4 | chaitali | 3000 | 2009-10-08 00:00:00 |
| 4 | chaitali | 1500 | 2009-10-08 00:00:00 |
| 4 | chaitali | 1560 | 2009-11-20 00:00:00 |
| 4 | chaitali | 2060 | 2008-05-20 00:00:00 |
| 5 | hardik | 3000 | 2009-10-08 00:00:00 |
| 5 | hardik | 1500 | 2009-10-08 00:00:00 |
| 5 | hardik | 1560 | 2009-11-20 00:00:00 |
| 5 | hardik | 2060 | 2008-05-20 00:00:00 |
| 6 | komal | 3000 | 2009-10-08 00:00:00 |
| 6 | komal | 1500 | 2009-10-08 00:00:00 |
| 6 | komal | 1560 | 2009-11-20 00:00:00 |
| 6 | komal | 2060 | 2008-05-20 00:00:00 |
| 7 | muffy | 3000 | 2009-10-08 00:00:00 |
| 7 | muffy | 1500 | 2009-10-08 00:00:00 |
| 7 | muffy | 1560 | 2009-11-20 00:00:00 |
| 7 | muffy | 2060 | 2008-05-20 00:00:00 |
+----+----------+--------+---------------------+