一、联接查询
联结查询是一种常见的数据库操作,即在两张表(或更多表)中进行行匹配的操作。一般称之为水平操作,这是因为对几张表进行联接操作所产生的结果集可以包含这几张表中所有的列。对应于联接的水平操作,一般将集合操作视为垂直操作。
MySQL数据库支持如下的联接查询:
- CROSS JOIN(交叉联接)
- INNER JOIN(内联接)
- OUTER JOIN(外联接)
- 其他
在进行联接操作时,需要明白SQL逻辑查询解析流程,尤其是关于联接所涉及的阶段。每个联接都只发生在两个表之间,即使FROM子句中包含多个表也是如此。每次联接操作也只进行逻辑操作的前三个步骤,每次产生一个虚拟表,这个虚拟表再依次与FROM子句的下一个表进行联接,重复上述步骤,直到FROM子句中的表都被处理完为止。
Tips:需要注意的是,不同联接类型执行的步骤不同。对于CROSS JOIN,只应用第一个阶段的笛卡尔积操作。INNER JOIN应用第一和第二个步骤,OUTER JOIN应用所有的前三个步骤;即先产生笛卡尔积操作、然后应用ON过滤,最后产生外部行。
如果没有任何限制条件的话,这些表连接起来产生的笛卡尔积可能是非常巨大的。比方说3个100行记录的表连接起来产生的笛卡尔积就有100×100×100=1000000行数据!所以在连接的时候过滤掉特定记录组合是有必要的,关于JOIN具体的算法不在此篇介绍范围内,此篇文章只是介绍JOIN的一些语法操作。
二、新旧查询语法
MySQL数据库支持两种不同的联接操作语句:
1 2 3 4 5 |
# ANSI SQL 89标准 SELECT * FROM a,b WHERE a.x = b.x; # ANSI SQL 92标准 SELECT * FROM a INNER JOIN b ON a.x = b.x; |
这两条SQL语句产生的结果是一样的,在那种场合使用哪种完全取决于个人的习惯。那么两者有区别吗?下面稍微介绍一下。
美国国家标准学会(ANSI)对SQL进行了标准化后,目前为止一共产生了ANSI SQL 89, ANSI SQL 92, ANSI SQL 99, ANSI SQL 2003等标准。 其中第一种语法就是ANSI SQL 89标准引入,与新语法的区别是FROM子句中的表名之间用逗号分隔,没有JOIN关键字,也没有ON子句。另外ANSI SQL 89只支持CROSS JOIN和INNTER JOIN,不支持OUTER JOIN。新语句是由ANSI SQL 92引入的,与就旧语法的区别就是引入了JOIN关键字和ON过滤子句,并去掉了表之间的逗号。
ANSI SQL 92引入了对外部联接的支持,因此要严格区分ON过滤器和WHERE过滤器的作用。虽然有新旧两种语句,但是MySQL数据库同时支持这两种语法,不必考虑性能问题,优化器将为两者生成相同的执行计划。
三、常见联接操作
先创建两张表,分别是student(学生姓名,出生时间,性别),course(课程名称,课程编号,课程导师,学生姓名)。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
CREATE TABLE student( ID int PRIMARY KEY AUTO_INCREMENT, NAME char(10) NOT NULL, BIRTHDAY date NOT NULL, SEX enum('男','女') NOT NULL ); insert into student(NAME,BIRTHDAY,SEX) value('ZHANG','1993-10-06','男'); insert into student(NAME,BIRTHDAY,SEX) value('LI','1994-10-06','女'); insert into student(NAME,BIRTHDAY,SEX) value('XU','1994-12-06','女'); insert into student(NAME,BIRTHDAY,SEX) value('PENG','1993-2-06','男'); insert into student(NAME,BIRTHDAY,SEX) value('WANG','1993-9-06','男'); CREATE TABLE course( ID int PRIMARY KEY AUTO_INCREMENT, C_NAME char(10) NOT NULL, NUM int NOT NULL, T_NAME char(10) NOT NULL, NAME char(10) ); insert into course(C_NAME,NUM,T_NAME,NAME) value('Shuxue','1001','ERIC','ZHANG'); insert into course(C_NAME,NUM,T_NAME,NAME) value('Yuwen','1002','MARK','LI'); insert into course(C_NAME,NUM,T_NAME,NAME) value('Wuli','7002','JERRY','ZHANG'); insert into course(C_NAME,NUM,T_NAME,NAME) value('Huaxue','7003','LILI','WANG'); |
交叉联接(CROSS JOIN)
在MySQL中,CROSS JOIN对两个表执行笛卡尔积,返回两个表中所有列的组合。若左表有m行数据,右表有n行数据,则CROSS JOIN将返回m*n行的表。也就是返回左右表中每个记录一一对应,没有值的以“NULL”显示。
常见的两种操作语法如下:
1 2 3 4 5 |
# ANSI SQL 89 select * from student as a, course as b; # ANSI SQL 92 select * from student AS a cross join course AS b; |
如果是自己跟自己进行联接,一定要有表别名,否则会出现错误。
在标准ANSI SQL中,CROSS JOIN和INNER JOIN之间的区别是ON子句可以与INNER JOIN一起使用,而ON子句不能与CROSS JOIN一起使用。但是在MySQL中CROSS JOIN是可以跟ON子句一起使用,算是MySQL的一种容错机制吧。
MySQL CROSS JOIN的图文演示(摘自MySQL CROSS JOIN):
对于交叉联接可能用到的地方并不多,但可以用来快速生成重复测试数据。交叉联接的另一个作用是可以作为返回结果集的行号,如对student表得到额外的行号。
1 2 3 4 5 6 7 8 9 10 |
mysql> select (select count(1) from student a where a.id <= b.id) as row_num, NAME from student b; +---------+-------+ | row_num | NAME | +---------+-------+ | 1 | ZHANG | | 2 | PENG | | 3 | WANG | | 4 | LI | +---------+-------+ 4 rows in set (0.00 sec) |
尽管上述的SQL语句十分简单,也很好理解,可是它运行得会非常慢,当数据量大了之后。要理解其中的原因,我们看一下执行计划:
1 2 3 4 5 6 7 8 |
mysql> desc select (select count(1) from student a where a.id <= b.id) as row_num, NAME from student b; +------+--------------------+-------+-------+---------------+---------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+--------------------+-------+-------+---------------+---------+---------+------+------+--------------------------+ | 1 | PRIMARY | b | ALL | NULL | NULL | NULL | NULL | 4 | | | 2 | DEPENDENT SUBQUERY | a | index | PRIMARY | PRIMARY | 4 | NULL | 4 | Using where; Using index | +------+--------------------+-------+-------+---------------+---------+---------+------+------+--------------------------+ 2 rows in set (0.00 sec) |
首先要扫描整个表b,得到所有的行。然后将返回的每一行数据与表a进行联接操作。每次行号计算都会涉及一次子查询的扫描操作。第1次返回一行数据,第2次返回二行数据,第3次返回三行数据,第N次返回N行数据,因此扫描的总行数是1+2+3+..+N。如果student表有30万数据,那么一共需要扫描150亿行!其实这几乎已经和笛卡尔积的扫描成本一样了,对30万行的表进行笛卡尔积也就需要扫描900亿行。
对于这个问题可以用交叉联接来解决,虽然对两个N行表进行笛卡尔积会产生N的2次方行数据。但是如果是对一行表与N行表进行交叉联接,笛卡尔积返回的韩式N行数据。因此我们可以使用如下的SQL解决行号问题。
1 2 3 4 5 6 7 8 9 10 |
mysql> select @a:=@a+1 as row_num, NAME from student, (select @a:=0) as a; +---------+-------+ | row_num | NAME | +---------+-------+ | 1 | ZHANG | | 2 | PENG | | 3 | WANG | | 4 | LI | +---------+-------+ 4 rows in set (0.00 sec) |
这里的SELECT @a:=0产生了只有一行的数据,因此虽然还需要扫描表student中所有的行,但是每行只和一行数据进行交叉联接,产生一行的记录,这要远快于前面介绍的SQL查询语句。使用交叉联接求解行号问题的执行计划如下:
1 2 3 4 5 6 7 8 9 |
mysql> desc select @a:=@a+1 as row_num, NAME from student, (select @a:=0) as a; +------+-------------+------------+--------+---------------+------+---------+------+------+----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+------------+--------+---------------+------+---------+------+------+----------------+ | 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | | | 1 | PRIMARY | student | ALL | NULL | NULL | NULL | NULL | 4 | | | 2 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | +------+-------------+------------+--------+---------------+------+---------+------+------+----------------+ 3 rows in set (0.00 sec) |
可以看到,表<derived2>只有一行数据,因此用交叉联接查询就大大提高了速度。很明显, 这条SQL语句扫描成本为O(N)。
内联接(INNER JOIN)
通过INNER JOIN用户可以根据一些过滤条件来匹配表之间的数据,并列出这些表中与联接条件相匹配的数据行,组合成新的记录,也就是说,在内联接查询中,只有满足条件的记录才能出现结果关系中。在逻辑查询的前三个处理阶段中,INNER JOIN应用前两个阶段,即首先产生笛卡尔积的虚拟表,再按照ON过滤条件来进行数据的匹配操作。INNER JOIN没有第三步操作,即不添加外部行,这是和OUTER JOIN最大的区别之一。也正因为不会添加外部行,指定过滤条件在ON子句和WHERE子句中是没有任何区别的。
比如查看学生ZHANG选择了什么课程以及导师是谁,使用语法如下:
1 2 3 4 5 6 |
SELECT s.NAME, c.C_NAME, c.T_NAME FROM student AS s INNER JOIN course AS c ON s.NAME=c.NAME WHERE s.NAME='ZHANG'; |
其中INNER关键字可省略。前面说过,INNER JOIN中WHERE的过滤条件可以写在ON子句中,因此下面的SQL查询语句得到的结果是一样的。
1 2 3 4 5 |
SELECT s.NAME, c.C_NAME, c.T_NAME FROM student AS s INNER JOIN course AS c ON s.NAME=c.NAME AND s.NAME='ZHANG'; |
或使用ANSI SQL 89标准语法
1 2 3 4 5 6 7 |
SELECT s.NAME, c.C_NAME, c.T_NAME FROM student, course WHERE s.NAME=c.NAME AND s.NAME='ZHANG'; |
特别需要注意的是,在MySQL数据库中,如果INNER JOIN后不跟ON子句,也是可以通过语法解析的,这时INNER JOIN等于CROSS JOIN,即产生笛卡尔积。这一点与其他数据库不同,例如,SQL server中必须指定ON子句,否则语法解析器会抛出异常。而在MySQL中,CROSS JOIN其实和INNER JOIN是同义词的关系,因此当没有ON子句时,SQL解析器会将INNER JOIN理解为CROSS JOIN。
另外多表联接原理和使用上也是相同的。稍微不同的是,对两张表进行INNER JOIN,通常MySQL数据库的优化器都能工作得很好,会选出数据集少的表作为驱动表。但是对于有多张表参与联接的语句,MySQL数据库的优化器选择可能并不总是正确的。这时,对于有经验的DBA,要确定最优的路径,可以使用STRAIGHT_JOIN联接,强制优化器按照自己的联接顺序来进行联接操作。不过,随着MySQL数据库的不断完善,这种情况正变得越来越少。
1 2 3 4 5 6 7 8 9 |
SELECT user.aa.id, user.aa.name, storage1.dol_chara_store.id, user.user.account_id, user.user.Id FROM storage1.dol_chara_store INNER JOIN user.user INNER JOIN user.aa ON storage1.dol_chara_store.account_id=user.user.account_id AND storage1.dol_chara_store.id=user.aa.rid; |
外联接(OUTER JOIN)
通过OUTER JOIN用户可以按照一些过滤条件来匹配表之间的数据。与INNER JOIN不同的是,在通过OUTER JOIN添加的保留表中存在未找到的匹配数据。MySQL数据库支持LEFT OUTER JOIN和RIGHT OUTER JOIN。与INNER关键字一样,可以省略OUTER关键字。
需要注意一点的是目前MySQL还不支持FULL OUTER JOIN(完全外联接),像SQL server是支持的。完全外联接意思就是返回包括左右表中的所有记录,左右两边没有匹配的记录都显示为“NULL”。
OUTER JOIN应用逻辑查询的前三个步骤,即产生笛卡尔积、应用ON过滤器和添加外部行。对于保留表中的行数据,如果是未找到匹配数据而被添加的记录,其值用NULL进行填充。如果使用LEFT JOIN,那么LEFT JOIN左边的表就是保留表;如果使用RIGHT JOIN,那么RIGHT JOIN右边的表就是保留表。
OUTER JOIN只在ANSI SQL 92中得到支持,在其他一些数据库中可以使用(+)=、*=来表示LEFT JOIN,用=(+)、=*来扩展ANSI SQL 89语法使其支持OUTER JOIN。但是对MySQL数据库来说,只有一种OUTER JOIN的联接语法。
使用LEFT JOIN查看没有选择课程的学生,语法如下:
1 2 3 4 |
SELECT a.NAME, b.C_NAME FROM student a LEFT JOIN course b ON a.NAME = b.NAME; |
需要注意的是,INNER JOIN中的过滤条件都可以写在ON子句中,而OUTERR JOIN的过滤条件不可以这样处理,因为可能会得到不正确的结果。比如下面这个语句。
1 2 3 4 5 |
SELECT a.NAME, b.C_NAME FROM student a LEFT JOIN course b ON a.NAME = b.NAME AND a.NAME='LI'; |
得到的结果如下:
1 2 3 4 5 6 7 8 9 10 |
+-------+--------+ | NAME | C_NAME | +-------+--------+ | LI | Yuwen | | LI | Wuli | | ZHANG | NULL | | PENG | NULL | | WANG | NULL | +-------+--------+ 5 rows in set (0.00 sec) |
结果显然不是我们要的。另外与INNER JOIN不同的是,对于OUTER JOIN必须要指定ON过滤器,否则MySQL数据库会抛出异常,报语法错误。
1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE TABLE t ( a INT PRIMARY KEY, b VARCHAR(5) ) ENGINE=INNODB; INSERT INTO t SELECT 1, 'Z'; INSERT INTO t SELECT 2, 'P'; INSERT INTO t SELECT 3, 'G'; INSERT INTO t SELECT 5, 'E'; INSERT INTO t SELECT 6, 'F'; INSERT INTO t SELECT 7, 'B'; INSERT INTO t SELECT 9, 'V'; |
最小缺失值的问题是找出记录中不连续的最小值,在这个例子中显然是4,我们可以通过OUTER JOIN来进行查询,具体的解决方案如下:
1 2 3 4 |
SELECT MIN(x.a+1) FROM t x LEFT OUTER JOIN t y ON x.a + 1 = y.a WHERE y.a IS NULL; |
该方案的第一步是对t表应用LEFT OUTER JOIN,联接的条件是x.a+1 = y.a。因为是LEFT OUTER JOIN,所以需要涉及逻辑查询的前三个步骤,笛卡尔积、O过滤器和JOIN。看看这三步都执行完后的虚拟化表的情况。ON过滤器的条件是x表中每行a值比y表中每行a值大1,因为使用的是OUTER JOIN,所以未在保留表中存在的行将作为外部行被添加,产生的虚拟表如下:
1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> select * from t x left outer join t y on x.a+1 = y.a; +---+------+------+------+ | a | b | a | b | +---+------+------+------+ | 1 | Z | 2 | P | | 2 | P | 3 | G | | 3 | G | NULL | NULL | | 5 | E | 6 | F | | 6 | F | 7 | B | | 7 | B | NULL | NULL | | 9 | V | NULL | NULL | +---+------+------+------+ 7 rows in set (0.00 sec) |
接着,应用第四步WHERE条件,过滤条件为y.a IS NULL,产生虚拟表如下:
1 2 3 4 5 6 7 8 9 |
mysql> select * from t x left outer join t y on x.a+1 = y.a where y.a IS NULL; +---+------+------+------+ | a | b | a | b | +---+------+------+------+ | 3 | G | NULL | NULL | | 7 | B | NULL | NULL | | 9 | V | NULL | NULL | +---+------+------+------+ 3 rows in set (0.00 sec) |
最后应用MIN函数取出最小值,最小值加1就是可以取出最小不连续值4。
STRAIGHT JOIN
STRAIGHT_JOIN其实不是新的联接类型,而是用户对SQL优化器的控制,其等同于INNER JOIN。通过STRAIGHT_JOIN,MySQL数据库会强制先读取左边的表。先看一下未使用STRAIGHT_JOIN的SQL执行计划。
1 2 3 4 5 6 7 8 |
mysql> desc SELECT s.NAME, c.C_NAME, c.T_NAME FROM student AS s INNER JOIN course AS c ON s.NAME=c.NAME; +------+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------+ | 1 | SIMPLE | c | ALL | NULL | NULL | NULL | NULL | 4 | | | 1 | SIMPLE | s | ALL | NULL | NULL | NULL | NULL | 5 | Using where; Using join buffer (flat, BNL join) | +------+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------+ 2 rows in set (0.00 sec) |
可以看到,MySQL数据库先选择c表,也就是course表,然后进行匹配。这样做的好处是实际只进行了4次匹配。如果使用STRAIGHT_JOIN,则会强制使用坐表,也就是student表。例如:
1 2 3 4 5 6 7 8 |
mysql> desc SELECT s.NAME, c.C_NAME, c.T_NAME FROM student AS s STRAIGHT_JOIN course AS c ON s.NAME=c.NAME; +------+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------+ | 1 | SIMPLE | s | ALL | NULL | NULL | NULL | NULL | 5 | | | 1 | SIMPLE | c | ALL | NULL | NULL | NULL | NULL | 4 | Using where; Using join buffer (flat, BNL join) | +------+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------------------+ 2 rows in set (0.00 sec) |
可以看到优化器会强制使用左边的表进行匹配。因为左表数据多,当数据量大时会明显比使用c表作为驱动表要慢的多。
对两张表进行INNER JOIN,通常MySQL数据库的优化器都能工作得很好。但是对于有多张表参与联接的语句,MySQL数据库的优化器选择可能并不总是正确的。这时,对于有经验的DBA,要确定最优的路径,可以使用STRAIGHT_JOIN,强制优化器按照自己的联接顺序来进行联接操作。不过,随着MySQL数据库的不断完善,这种情况正变得越来越少。
<摘自>
InnoDB存储引擎