2020 一张图告诉你SQL使用inner join，left join 等 SQL join

​

​文末有数据库sql​

​

环境：mysql5.*

时间：202012

文章目录

• ​​表数据​​
• ​​SQL查询​​

• ​​inner join​​
• ​​union​​

• ​​union all​​

• ​​left join​​

• ​​IS NULL​​

• ​​right join​​

• ​​is null​​

• ​​FULL OUTER JOIN​​

• ​​sql文件​​

表数据

新建了俩张表

SQL查询

inner join

求交集

SELECT * FROM user1 a INNER JOIN user2 b ON a.name = b.name;

union

求并集

SELECT * FROM user1 UNION SELECT * FROM user2;

union all

并集

SELECT * FROM user1 UNION ALL SELECT * FROM user2;

left join

SELECT * FROM user1 a LEFT JOIN user2 b ON a.​

​name​

​=b.​

​name​

​;

IS NULL

SELECT * FROM user1 a LEFT JOIN user2 b ON a.​

​name​

​​=b.​

​name​

​​ WHERE b.​

​name​

​ IS NULL;

right join

SELECT * FROM user1 a RIGHT JOIN user2 b ON a.​

​name​

​​=b.​

​name​

​;

is null

SELECT * FROM user1 a RIGHT JOIN user2 b ON a.​

​name​

​​=b.​

​name​

​​ WHERE a.​

​name​

​ IS NULL;

FULL OUTER JOIN

SELECT * FROM user1 FULL OUTER JOIN user2 ON user1.name=user2.name;

sql文件

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for user1
-- ----------------------------
DROP TABLE IF EXISTS `user1`;
CREATE TABLE `user1` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of user1
-- ----------------------------
INSERT INTO `user1` VALUES ('1', 'zhangsan');
INSERT INTO `user1` VALUES ('2', 'lisi');
INSERT INTO `user1` VALUES ('3', 'wangwu');

-- ----------------------------
-- Table structure for user2
-- ----------------------------
DROP TABLE IF EXISTS `user2`;
CREATE TABLE `user2` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of user2
-- ----------------------------
INSERT INTO `user2` VALUES ('1', 'zhangsan');
INSERT INTO `user2` VALUES ('2', 'lisi');
INSERT INTO `user2` VALUES ('3', 'maliu');