mysql joins

2022-08-02 444 words 3 minutes

Contents

LEFT JOIN

LEFT JOIN 可将两张或两张以上的表以某种条件连接起来，最左边的表作为主表，故叫LEFT JOIN

基本语法

1
2
3
4
5
6


SELECT 
    select_list
FROM
    t1
LEFT JOIN t2 ON 
    join_condition;

在上面的语法中，t1是左表(主表)，t2是右表

LEFT JOIN 从左表开始(t1)筛选数据，并且t1的每一条与t2的每一条基于join_condition做判断，如此一来会有两种情况

如果join_condition的值为true,则LEFT JOIN两个表中行合并成一个新的数据行
如果join_condition的值为false，即t1的数据与t2的任何一条都不匹配，依然生成新的一条数据，但是t2的所有列都是NULL

也就是说LEFT JOIN返回的是t1所有的数据条数，而不管是否与t2的数据匹配，能匹配则带上t2的数据，不能匹配用NULL占位

例子

Download MySQL Sample Database

两张表

这是顾客表和订单表

1
2
3
4
5
6
7
8
9


SELECT 
    c.`customerNumber`, 
    c.`customerName`, 
    o.`orderNumber`, 
    o.`status`
FROM
    customers as c
LEFT JOIN orders as o ON 
    c.customerNumber = o.customerNumber;

可以看见有些数据的右表字段是NULL

利用这个特性，很容知道哪些顾客没有订单

 1
 2
 3
 4
 5
 6
 7
 8
 9
10


SELECT 
    c.`customerNumber`, 
    c.`customerName`, 
    o.`orderNumber`, 
    o.`status`
FROM
    customers as c
LEFT JOIN orders as o ON 
    c.customerNumber = o.customerNumber
    WHERE o.`orderNumber` IS NULL

三张表

如果是三张表，情况如下

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15


SELECT 
    e.lastName, 
    e.firstName, 
    c.customerName, 
    p.checkNumber, 
    p.amount
FROM
    employees as e
LEFT JOIN customers as c ON 
    e.employeeNumber = c.salesRepEmployeeNumber
LEFT JOIN payments as p ON 
    p.customerNumber = c.customerNumber
ORDER BY 
    c.customerName, 
    p.checkNumber;

第一次LEFT JOIN是employees和customers关联，当关联的条件为false时，customers部分字段用NULL表示
第二次LEFT JOIN是customers和payments关联，当关联的条件为false时，payments部分字段用NULL表示

可以看到第一次关联不到数据项，第二次也关联不上，即如果customerName为NULL则payments部分字段必为NULL

过滤条件应该放哪儿

将过滤条件放在WHERE中

 1
 2
 3
 4
 5
 6
 7
 8
 9
10


SELECT 
    o.orderNumber, 
    customerNumber, 
    productCode
FROM
    orders as o
LEFT JOIN orderDetails as od
   ON o.orderNumber=od.orderNumber
WHERE
    o.orderNumber = 10123;

将过滤条件放在ON中

1
2
3
4
5
6
7
8


SELECT
	o.orderNumber,
	customerNumber,
	productCode 
FROM
	orders AS o
	LEFT JOIN orderDetails AS od ON o.orderNumber = od.orderNumber 
	AND o.orderNumber = 10123;

看出差别来了吗

过滤条件放在连接语句的ON里面，是作为一种连接条件，只会关联o.orderNumber = od.orderNumber 并且o.orderNumber = 10123,但是左表依然是全部查询，只是关联条件比原来(o.orderNumber = od.orderNumber)苛刻了

而放在WHERE里面是将连接表作为整体来看，只展示o.orderNumber = 10123的数据

RIGHT JOIN

RIGHT JOIN 和 LEFT JOIN 非常类似，唯一的区别是哪个表作为主表

1
2
3
4
5


SELECT 
    select_list
FROM t1
RIGHT JOIN t2 ON 
    join_condition;

在上面的sql中，t2作为主表

1
2
3
4
5
6
7
8
9


SELECT 
    employeeNumber, 
    customerNumber
FROM
    customers
RIGHT JOIN employees 
    ON salesRepEmployeeNumber = employeeNumber
ORDER BY 
	employeeNumber;

INNER JOIN

INNER JOIN 取几个表的交集

1
2
3
4
5
6


SELECT
    select_list
FROM t1
INNER JOIN t2 ON join_condition1
INNER JOIN t3 ON join_condition2
...;

例子

 1
 2
 3
 4
 5
 6
 7
 8
 9
10


SELECT 
    c.`customerNumber`, 
    c.`customerName`, 
    o.`orderNumber`, 
    o.`status`
FROM
    customers as c
INNER JOIN orders as o ON 
    c.customerNumber = o.customerNumber
    WHERE `status` IS NULL;

可以发现，WHERE status IS NULL 的结果集为空，这和LEFT JOIN不一样，这是因为INNER JOIN 只有join_condition为true时才会生成新的数据行

可以和LEFT JOIN (RIGHT JOIN)搭配起来使用

employees表(主表)和customers表内联，和payments左联

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15


SELECT 
    e.lastName, 
    e.firstName, 
    c.customerName, 
    p.checkNumber, 
    p.amount
FROM
    employees as e
INNER JOIN customers as c ON 
    e.employeeNumber = c.salesRepEmployeeNumber
LEFT JOIN payments as p ON 
    p.customerNumber = c.customerNumber
ORDER BY 
    c.customerName, 
    p.checkNumber;

employees表(主表)和customers表左联，和payments内联

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15


SELECT 
    e.lastName, 
    e.firstName, 
    c.customerName, 
    p.checkNumber, 
    p.amount
FROM
    employees as e
LEFT JOIN customers as c ON 
    e.employeeNumber = c.salesRepEmployeeNumber
INNER JOIN payments as p ON 
    p.customerNumber = c.customerNumber
ORDER BY 
    c.customerName, 
    p.checkNumber;