神奇的 SQL 之团结的力量 → JOIN，团结就是力量

神奇的 SQL 之团结的力量 → JOIN，团结就是力量

前言

　　开心一刻

　 　　闺蜜家暴富，买了一栋大别野，喊我去吃饭，菜挺丰盛的，筷子有些不给力，银筷子，好重，我说换个竹子的，闺蜜说，这种银筷子我家总共才五双，只有贵宾才能用~我咬着牙享受着贵宾待遇，终于，在第三次夹虾排滑落盘子时，我爆发了：去它喵的贵宾，我要虾排……不是……我要竹筷子！

DROP TABLE IF EXISTS t_user; CREATE TABLE t_user ( id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增主键', user_name VARCHAR(50) NOT NULL COMMENT '用户名', sex TINYINT(1) NOT NULL COMMENT '性别, 1:男，0:女', age TINYINT(3) UNSIGNED NOT NULL COMMENT '年龄', phone_number VARCHAR(11) NOT NULL DEFAULT '' COMMENT '电话号码', email VARCHAR(50) NOT NULL DEFAULT '' COMMENT '电子邮箱', create_time datetime NOT NULL COMMENT '创建时间', update_time datetime NOT NULL COMMENT '更新时间', PRIMARY KEY (id) ) COMMENT='用户表'; DROP TABLE IF EXISTS t_login_log; CREATE TABLE t_login_log ( id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增主键', user_name VARCHAR(50) NOT NULL COMMENT '用户名', ip VARCHAR(15) NOT NULL COMMENT '登录IP', client TINYINT(1) NOT NULL COMMENT '登录端, 1:android, 2:ios, 3:PC, 4:H5', create_time datetime NOT NULL COMMENT '创建时间', PRIMARY KEY (id) ) COMMENT='登录日志'; INSERT INTO t_user(user_name, sex, age, phone_number,email,create_time,update_time) VALUES ('Bruce Lee', 1, 32, '15174480987', 'brucelee@126.com', NOW(), NOW()), ('Jackie Chan', 1, 65, '15174481234', 'JackieChan@126.com', NOW(), NOW()), ('Jet Li', 1, 56, '15174481245', 'JetLi@126.com', NOW(), NOW()), ('Jack Ma', 1, 55, '15174481256', 'JackMa@126.com', NOW(), NOW()), ('Pony', 1, 48, '15174481278', 'Pony@126.com', NOW(), NOW()), ('Robin Li', 1, 51, '15174481290', 'RobinLi@126.com', NOW(), NOW()); INSERT INTO t_login_log(user_name, ip, client, create_time) VALUES ('Jackie Chan', '10.53.56.78',2, '2019-10-12 12:23:45'), ('Jackie Chan', '10.53.56.78',2, '2019-10-12 22:23:45'), ('Jet Li', '10.53.56.12',1, '2018-08-12 22:23:45'), ('Jet Li', '10.53.56.12',1, '2019-10-19 10:23:45'), ('Jack Ma', '198.11.132.198',2, '2018-05-12 22:23:45'), ('Jack Ma', '198.11.132.198',2, '2018-11-11 22:23:45'), ('Jack Ma', '198.11.132.198',2, '2019-06-18 22:23:45'), ('Robin Li', '220.181.38.148',3, '2019-10-21 09:45:56'), ('Robin Li', '220.181.38.148',3, '2019-10-26 22:23:45'), ('Pony', '104.69.160.60',4, '2019-10-12 10:23:45'), ('Pony', '104.69.160.60',4, '2019-10-15 20:23:45'); View Code

　　如果我们需要展示如下列表（需求：展示用户列表，并显示其最近登录时间、最近登录 IP），那么就需要 t_user 和 t_login_log 连表查了

SELECT * FROM t_user CROSS JOIN t_login_log; -- 与 CROSS JOIN 得到的结果相同 -- 过时的写法，不符合 SQL标准，能读懂就好，不推荐使用 SELECT * FROM t_user, t_login_log;

　　t_user 中有 6 条记录， t_login_log 中有 11 条记录，t_user CROSS JOIN t_login_log 的结果是 66（ 6 乘以 11） 条记录

-- 等值连接 SELECT * FROM t_user tu INNER JOIN t_login_log ttl ON tu.user_name = ttl.user_name; -- INNER JOIN 可以简写成 JOIN SELECT * FROM t_user tu JOIN t_login_log ttl ON tu.user_name = ttl.user_name; -- 不加连接键, 结果与 CROSS JOIN 一样 SELECT * FROM t_user tu INNER JOIN t_login_log ttl

SELECT * FROM t_user tu INNER JOIN t_login_log ttl ON tu.user_name <> ttl.user_name; SELECT * FROM t_user tu INNER JOIN t_login_log ttl ON tu.user_name > ttl.user_name;

　　自然连接

　　　　不需要指定连接条件，数据库系统会自动用相同的字段作为连接键，直观的表现就是关键字：NATURAL JOIN，NATURAL LEFT JOIN、NATURAL RIGHT JOIN；

　　　　连接键不直观，需要去看两张表中相同的字段有哪些；对于自然连接，了解即可，不推荐使用，反正我工作这么久，一次都没用过。

外连接

　　外连接的使用方式与内连接一样，也是通过 ON 使用连接键将两张表连接，从结果中获取我们想要的数据，但是返回的结果与内连接有区别，具体我们往下看

　　左连接

　　　　返回匹配的记录，以及左表多余的记录，关键字：LEFT JOIN（LEFT OUTER JOIN 的简写）

SELECT * FROM t_user tu LEFT OUTER JOIN t_login_log ttl ON tu.user_name = ttl.user_name;
-- LEFT JOIN 是 LEFT OUTER JOIN 的简写
SELECT * FROM t_user tu LEFT JOIN t_login_log ttl ON tu.user_name = ttl.user_name;

SELECT * FROM t_login_log ttl RIGHT OUTER JOIN t_user tu ON tu.user_name = ttl.user_name; -- RIGHT JOIN 是 RIGHT OUTER JOIN 的简写 SELECT * FROM t_login_log ttl RIGHT JOIN t_user tu ON tu.user_name = ttl.user_name;

　　　　由于我们习惯了从左往右（阅读方式、写作方式），因此在实际项目中，基本上用的都是左连接

　　全连接

　　　　返回匹配的记录，以及左表和右表各自的多余记录，关键字：FULL JOIN （FULL OUTER JOIN 的简写）

SELECT * FROM t_user tu FULL OUTER JOIN t_login_log ttl ON tu.user_name = ttl.user_name;
-- FULL JOIN 是 FULL OUTER JOIN 的简写
SELECT * FROM t_user tu FULL JOIN t_login_log ttl ON tu.user_name = ttl.user_name;

　　　　注意：MySQL 不支持 全连接，我们可以通过 左连接、右连接之后，再 UNION 来实现全连接

自连接

　　一张表，自己连接自己，简单点来理解就是，左表、右表是同一张表；连接方式可以是内连接、也可以是外连接

　　更多详情大家可以去看：项目上线后，谈一下感触比较深的一点：查询优化

需求：展示用户列表，并显示最近登录时间、最近登录 IP

　　对于此需求，大家会如何来写这个 SQL ？ 也许大家很容易想到左连接，如下所示

SELECT * FROM t_user tu LEFT JOIN t_login_log ttl ON tu.user_name = ttl.user_name;

　　可结果如下：

-- 1、连接配合子查询，注意 Bruce Lee 从未登陆过 SELECT tu.user_name, tu.sex,tu.age, tu.phone_number,tu.email,tll.create_time,tll.ip FROM t_user tu LEFT JOIN t_login_log tll ON tu.user_name = tll.user_name WHERE tll.id = (SELECT MAX(id) FROM t_login_log WHERE user_name = tu.user_name) OR tll.user_name IS NULL; -- 2、t_login_log分组统计出各个用户的最近一次登录信息后，再与 t_user 联表 SELECT tu.user_name, tu.sex,tu.age, tu.phone_number,tu.email,tll.create_time,tll.ip FROM t_user tu LEFT JOIN ( SELECT tb.* FROM( SELECT user_name, MAX(id) id FROM t_login_log GROUP BY user_name ) ta LEFT JOIN t_login_log tb ON ta.id = tb.id ) tll ON tu.user_name = tll.user_name;

　　具体的实现还得结合具体的业务和需求来实现，那样才能写出高效的 SQL；另外结合执行计划来建立合适的索引。总之，没有一成不变的、通用的高效 SQL，结合具体的业务才能写出最合适的 SQL。

总结

　　1、连接的描述方式

　　　　常用的维恩图，描述如下

描述方式，我觉得描述的更准确

　　　　CROSS JOIN

 　　　　常用 JOIN

 　　　　上图中，颜色表示匹配关系，颜色相同表示匹配。返回结果中，如果另一张表没有匹配的记录，则用 null 填充， 在上图中则表示为空白。

　　2、连接中 ON 指定连接键，连接键可以指定多个，而 WHERE  还是平时的作用，用来指定过滤条件；不推荐将连接键放于 WHERE 后；

　　3、实际工作中，用的最多的是 左连接 和 等值连接，其他的用的特别少

参考

　　《SQL基础教程》

　　《SQL进阶教程》