SQL之连接查询
连接查询包括合并、内连接、外连接和交叉连接,如果涉及多表查询,了解这些连接的特点很重要。只有真正了解它们之间的区别,才能正确使用。
环境准备
表customers
CREATE TABLE customers
(
CUSTOMER_ID INT NOT NULL AUTO_INCREMENT,
CUSTOMER_NAME VARCHAR(255),
PRIMARY KEY (CUSTOMER_ID)
)
INSERT INTO customers (CUSTOMER_ID, CUSTOMER_NAME) VALUES (1, 'C1');
INSERT INTO customers (CUSTOMER_ID, CUSTOMER_NAME) VALUES (2, 'C1');
INSERT INTO customers (CUSTOMER_ID, CUSTOMER_NAME) VALUES (5, 'C1');
表orders
CREATE TABLE orders
(
ORDER_ID INT NOT NULL AUTO_INCREMENT,
ORDER_NAME VARCHAR(255),
CUSTOMER_ID INT,
PRIMARY KEY (ORDER_ID),
INDEX FK_57wwsm6wqqkcr1amp4dtsk5bs (CUSTOMER_ID)
)
INSERT INTO orders (ORDER_ID, ORDER_NAME, CUSTOMER_ID) VALUES (1, 'O1', 1);
INSERT INTO orders (ORDER_ID, ORDER_NAME, CUSTOMER_ID) VALUES (2, 'O2', 1);
INSERT INTO orders (ORDER_ID, ORDER_NAME, CUSTOMER_ID) VALUES (3, 'O1', 2);
INSERT INTO orders (ORDER_ID, ORDER_NAME, CUSTOMER_ID) VALUES (4, 'O2', 2);
INSERT INTO orders (ORDER_ID, ORDER_NAME, CUSTOMER_ID) VALUES (5, 'O1', 10);
INSERT INTO orders (ORDER_ID, ORDER_NAME, CUSTOMER_ID) VALUES (6, 'O2', 10);
1、Union
UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
UNION 运算符通过组合其他两个结果表(例如 TABLE1 和 TABLE2)并消去表中任何重复行而派生出一个结果表。
当 ALL 随 UNION 一起使用时(即 UNION ALL),不消除重复行。两种情况下,派生表的每一行不是来自 TABLE1 就是来自 TABLE2。
注意:使用UNION时,两张表查询的结果有相同数量的列、列类型相似。
2、INNER JOIN(内连接)
INNER JOIN(内连接),也成为自然连接
作用:根据两个或多个表中的列之间的关系,从这些表中查询数据。
注意: 内连接是从结果中删除其他被连接表中没有匹配行的所有行,所以内连接可能会丢失信息。
重点:内连接,只查匹配行。
select c.*,o.* from customers c inner join orders o on c.customer_id=o.customer_id;
作用类似于:
select c.*,o.* from customers c,orders o where c.customer_id=o.customer_id;
3、外连接
与内连接相比,即使没有匹配行,也会返回一个表的全集。
外连接分为三种:左外连接,右外连接,全外连接。对应SQL:LEFT/RIGHT/FULL OUTER JOIN。通常我们省略outer 这个关键字。写成:LEFT/RIGHT/FULL JOIN。
重点:至少有一方保留全集,没有匹配行用NULL代替。
左外连接
select c.*,o.* from customers c left join orders o on c.customer_id=o.customer_id;
又外连接
select c.*,o.* from customers c right join orders o on c.customer_id=o.customer_id;
全外连接
select c.*,o.* from customers c full join orders o on c.customer_id=o.customer_id;
Mysql不支持,用以下语句代替
select c.*,o.* from customers c left join orders o on c.customer_id=o.customer_id
union
select c.*,o.* from customers c right join orders o on c.customer_id=o.customer_id;
4、CROSS JOIN(交叉连接)
交叉连接。交叉连接返回左表中的所有行,左表中的每一行与右表中的所有行组合。交叉连接也称作笛卡尔积。
简单查询两张表组合,这是求笛卡儿积,效率最低。
select c.*,o.* from customers c cross join orders o ;