MYSQL——join内连接、左连接、右连接、外连接

逻辑图

SQL JOINS
图片来源，表示看不懂的小朋友可以下方留言，我后续加入通俗易懂的语言和场景。

join说明

• 内连接：Inner Join 公共部分
• 左外连接：Left Join
• 右外连接：Right Join
• 全外连接：Full Outer Join
• 笛卡尔集合查询（a * b）：Cross Join

示例

表

• vz_test1

INSERT INTO `test`.`vz_test1`(`id`, `name`, `over`) VALUES (1, '和同学', '东莞理工学院');
INSERT INTO `test`.`vz_test1`(`id`, `name`, `over`) VALUES (2, '黎同学', '清华大学');
INSERT INTO `test`.`vz_test1`(`id`, `name`, `over`) VALUES (3, '李同学', '湖南大学');
INSERT INTO `test`.`vz_test1`(`id`, `name`, `over`) VALUES (4, '小甜心', '南京大学');

• vz_test2

INSERT INTO `test`.`vz_test2`(`id`, `name`, `over`) VALUES (1, '和同学', '清华高中');
INSERT INTO `test`.`vz_test2`(`id`, `name`, `over`) VALUES (2, '黎同学', '北京高中');
INSERT INTO `test`.`vz_test2`(`id`, `name`, `over`) VALUES (3, '里同学', '北京大学');
INSERT INTO `test`.`vz_test2`(`id`, `name`, `over`) VALUES (4, '啊同学', '清华大学');

• vz_test_nums

INSERT INTO `test`.`vz_test_nums`(`id`, `user_id`, `time`, `nums`) VALUES (1, 1, '2019-11-08 14:26:00', 4);
INSERT INTO `test`.`vz_test_nums`(`id`, `user_id`, `time`, `nums`) VALUES (3, 2, '2019-11-14 14:26:26', 1);
INSERT INTO `test`.`vz_test_nums`(`id`, `user_id`, `time`, `nums`) VALUES (4, 3, '2019-11-13 14:26:34', 10);
INSERT INTO `test`.`vz_test_nums`(`id`, `user_id`, `time`, `nums`) VALUES (5, 4, '2019-11-09 14:26:42', 9);
INSERT INTO `test`.`vz_test_nums`(`id`, `user_id`, `time`, `nums`) VALUES (6, 4, '2019-11-15 14:26:54', 11);
INSERT INTO `test`.`vz_test_nums`(`id`, `user_id`, `time`, `nums`) VALUES (7, 5, '2019-11-13 14:27:01', 4);
INSERT INTO `test`.`vz_test_nums`(`id`, `user_id`, `time`, `nums`) VALUES (8, 1, '2019-11-13 14:33:36', 6);
INSERT INTO `test`.`vz_test_nums`(`id`, `user_id`, `time`, `nums`) VALUES (9, 1, '2019-11-12 16:55:41', 2);
INSERT INTO `test`.`vz_test_nums`(`id`, `user_id`, `time`, `nums`) VALUES (10, 1, '2019-12-07 17:00:43', 6);

内连接

SELECT a.name,a.over,b.over over2 FROM vz_test1 a JOIN vz_test2 b ON a.name = b.name

image.png

左连接

SELECT a.name,a.over,b.over over2 FROM vz_test1 a LEFT JOIN vz_test2 b ON a.name = b.name

image.png

右连接

SELECT b.name,b.over,a.over over1 FROM vz_test1 a RIGHT JOIN vz_test2 b ON a.name = b.name;

image.png

外连接

SELECT a.`name`,a.`over`FROM vz_test1 a LEFT JOIN vz_test2 b ON a.`name` = b.`name`
UNION ALL
SELECT b.`name`,b.`over` FROM vz_test1 a RIGHT JOIN vz_test2 b ON a.`name` = b.`name`

image.png

如何更新使用过滤条件中包括自身的表

UPDATE vz_test1 a JOIN (SELECT b.name FROM vz_test1 a JOIN vz_test2 b ON a.`name` = b.`name`) c ON a.name = c.name set a.over = '东莞理工学院';

优化子查询

优化前
SELECT a.name,a.over,(SELECT b.over FROM vz_test2 b WHERE a.name = b.name) over2 FROM vz_test1 a; 
优化后
SELECT a.name,a.over,b.over as over1 FROM vz_test1 a LEFT JOIN vz_test2 b ON a.name = b.name;

优化聚合子查询

优化前
SELECT b.user_id, a.name,b.time FROM vz_test1 a JOIN vz_test_nums b ON a.id = b.user_id WHERE b.nums = (SELECT MAX(nums) FROM vz_test_nums b WHERE a.id = b.user_id);
优化后
SELECT
    a.NAME,
    b.time,
    b.nums 
FROM
    vz_test1 a
    JOIN vz_test_nums b ON a.id = b.user_id
    JOIN vz_test_nums c ON b.user_id = c.user_id 
GROUP BY
    a.NAME,
    b.time,
    b.nums 
HAVING
    b.nums = MAX( c.nums );

实现分组选择

#优化前 单个查询
SELECT a.name, b.time,b.nums FROM vz_test1 a JOIN vz_test_nums b ON a.id = b.user_id WHERE a.name = '和同学' ORDER BY b.nums DESC LIMIT 2;
#优化后 先自连接查询 vz_test_nums 每一行 小于等于自身nums值的数量 ，然后与vz_test1进行内连接
SELECT d.name,c.time,nums 
FROM (
    SELECT user_id,time,nums,(
        SELECT COUNT(*) 
        FROM vz_test_nums b
        WHERE b.user_id = a.user_id AND a.nums <= b.nums
    ) AS cnt
    FROM vz_test_nums a
    GROUP BY user_id,time,nums
) c JOIN vz_test1 d ON c.user_id = d.id
WHERE cnt <= 2