SQL 经典面试题 - 一题搞懂各种数据库连接查询

面试题

先来看一道面试题：根据表A，表B写出以下SQL的查询结果集

1：SELECT * FROM  A  LEFT  JOIN B ON A.aID = b.bID;
2：SELECT * FROM  A  RIGHT JOIN B ON A.aID = b.bID;
3：SELECT * FROM  A  INNER JOIN B ON A.aID = b.bID;

语法学习

要想写出正确的结果，首先我们要知道LEFT JOIN 、RIGHT JOIN、INNER JOIN的语法：**LEFT JOIN：**左连接，会读取左表全部数据，即便右表没有对应数据。左连接从左表产生一套完整的记录,和右表匹配的记录 .如果右边没有匹配记录,右侧结果集字段将为null。结果集图示如下：

RIGHT JOIN： 右连接，以右表为基础，与LEFT JOIN相反。结果集图示如下：

**INNER JOIN ：**内连接,或等值连接，可以获取两个表中字段匹配关系的记录。结果集图示如下：

题目改造

掌握了以上的语法，不难得到上面题目的查询结果。为了更方便大家理解，我们改造题目如下：

非常勿扰节目来了6位女嘉宾和6位男嘉宾，他们的信息和配对情况分别保存在grils表和boys表中。其中id分别是两个表的主键，boyNum、girlNum分别男女嘉宾的编号，matchNum是男女嘉宾的配对编号，如果男生和女生的配对编号相对，则表示配对成功，表数据如下所示：

根据以上结构新建girls表和boys表并初始化两个表的数据：
初始化表：

DROP TABLE IF EXISTS girls;
-- 1:创建girls表
CREATE TABLE girls(
 id INT(10) PRIMARY KEY auto_increment,  -- 主键
 girlNum VARCHAR(20), -- 女嘉宾编号
 matchNum INT(10) -- 配对编号，男女配对编号相同表示配对成功);

-- 2:初始化girls数据
INSERT INTO girls(girlNum, matchNum)VALUES
	("gril-001",1),
	("gril-002",2),
	("gril-003",3),
	("gril-004",4),
	("gril-005",5),
	("gril-006",6);

-- 初始化boys表
DROP TABLE IF EXISTS boys;
-- 1:创建boys表
CREATE TABLE boys(
 id INT(10) PRIMARY KEY auto_increment,  -- 主键
 boyNum VARCHAR(20), -- 男嘉宾编号
 matchNum INT(10) -- 配对编号，男女配对编号相同表示配对成功);
-- 2:初始化boys数据
INSERT INTO boys(boyNum, matchNum)VALUES
	("boy-001",1),
	("boy-002",2),
	("boy-003",3),
	("boy-004",7),
	("boy-005",8),
	("boy-006",9);

通过查询，我们来看一下 LEFT JOIN,RIGHT JOIN,INNER JOIN的数据：

LEFT JOIN

SELECT * FROM girls LEFT JOIN boys ON girls.matchNum = boys.matchNum;

RIGHT JOIN

SELECT * FROM girls RIGHT JOIN boys ON girls.matchNum = boys.matchNum;

INNER JOIN

可以看到LEFT JOIN 和RIGHT JOIN对于没有匹配的记录行，字段会用NULL来表示。

对于关系型数据库，连接查询一直是数据库学生的重点和难点，通过下面例子大家可以看看一些常见的数据库连接查询方式，查漏补缺：

实战练习

查询配对成功的男女嘉宾信息(等值连接，常见的4种方式)

SELECT * FROM girls t1,boys t2 WHERE t1.matchNum = t2.matchNum;
SELECT * FROM girls t1 join boys t2 ON t1.matchNum = t2.matchNum;
SELECT * FROM girls t1 INNER JOIN boys t2 ON t1.matchNum = t2.matchNum;
SELECT * FROM girls as t1 cross join boys as t2 ON t1.matchNum = t2.matchNum;

查看女嘉宾信息和其牵手情况

SELECT * FROM girls LEFT  JOIN boys ON girls.matchNum = boys.matchNum;

查看男嘉宾信息和其牵手情况

SELECT * FROM girls RIGHT JOIN boys ON girls.matchNum = boys.matchNum;

查看未牵手的女嘉宾信息

SELECT * FROM girls LEFT JOIN boys ON girls.matchNum = boys.matchNum
WHERE boys.id IS NULL;

查看未牵手的男嘉宾信息

select *from girls t1 RIGHT JOIN boys t2 on t1.matchNum = t2.matchNum 
WHERE t1.id IS NULL;

查询所有未牵手成功的男嘉宾和女嘉宾信息

SELECT girls.id,girls.girlNum num 
FROM girls LEFT  JOIN boys ON girls.matchNum = boys.matchNum
WHERE boys.id IS NULL
UNION 
SELECT t2.id,t2.boyNum num
FROM girls t1 RIGHT JOIN boys t2 on t1.matchNum = t2.matchNum 
WHERE t1.id IS NULL;

测试派 Give testers a home.