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

文末有数据库sql

环境：mysql5.*
时间：202012

文章目录

• • 表数据
  • SQL查询
  • 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');