SQL之连接查询

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 ;