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

作者: 沙蒿同学 | 来源:发表于2019-11-12 17:21 被阅读0次

连接
MySQL开发技巧一：JOIN从句
连接查询；外键
连接查询；外键
SQL 多表查询（内连接、外连接和交叉连接）
MySQL 内连接、外连接、左连接、右连接、全连接
Pandas数据合并
MYSQL——join内连接、左连接、右连接、外连接
sql join方式
SQL 联表查询

逻辑图

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