SQL JOIN 理解

最新推荐文章于 2022-06-04 19:00:12 发布

dengjili

最新推荐文章于 2022-06-04 19:00:12 发布

阅读量164

点赞数

分类专栏： mysql

随意转载。

本文链接：https://blog.csdn.net/dengjili/article/details/106839456

版权

mysql 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

在这里插入图片描述

实践

创建mysql表

-- ----------------------------
-- Table structure for `author`
-- ----------------------------
DROP TABLE IF EXISTS `author`;
CREATE TABLE `author` (
  `username` varchar(255) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `bio` varchar(255) DEFAULT NULL,
  `favourite_section` varchar(255) DEFAULT NULL,
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of author
-- ----------------------------
INSERT INTO `author` VALUES ('zhangsan', '@WSX3edc', 'aaaa@qq.com', 'test', 'play', '1');
INSERT INTO `author` VALUES ('lisi', '123456', 'jili.aa@mail.com', 'aaaa', 'music', '2');
INSERT INTO `author` VALUES ('wangji', '111111', 'ttt@tianmao.com', 'a', 'sell', '4');

-- ----------------------------
-- Table structure for `blog`
-- ----------------------------
DROP TABLE IF EXISTS `blog`;
CREATE TABLE `blog` (
  `title` varchar(255) DEFAULT NULL,
  `author_id` varchar(255) DEFAULT NULL,
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of blog
-- ----------------------------
INSERT INTO `blog` VALUES ('java', '1', '1');
INSERT INTO `blog` VALUES ('c++', '1', '4');
INSERT INTO `blog` VALUES ('python', '2', '2');
INSERT INTO `blog` VALUES ('php', '3', '3');

相关sql

--A/B
select A.*, B.* from Author A LEFT JOIN Blog B on B.author_id = A.id;
select A.*, B.* from Author A RIGHT JOIN Blog B on B.author_id = A.id
--A-B
select A.*, B.* from Author A LEFT JOIN Blog B on B.author_id = A.id where B.author_id is NULL;
--A交B
select A.*, B.* from Author A INNER JOIN Blog B on B.author_id = A.id;
--A并B
select A.*, B.* from Author A FULL OUTER JOIN Blog B on B.author_id = A.id;
select A.*, B.* from Author A LEFT JOIN Blog B on B.author_id = A.id UNION select A.*, B.* from Author A RIGHT JOIN Blog B on B.author_id = A.id;
--A-B并B-A
select A.*, B.* from Author A LEFT JOIN Blog B on B.author_id = A.id where B.author_id is NULL UNION select A.*, B.* from Author A RIGHT JOIN Blog B on B.author_id = A.id where A.id is NULL;;