硅谷论坛 » PHP程序开发 » MySQL的联结（Join）语法

笑傲江湖 发表于 2007-8-21 11:01

MySQL的联结（Join）语法

[size=2][b][size=12pt]1[/size][/b][b][font=宋体][size=12pt]．内联结、外联结、左联结、右联结的含义及区别：[/size][/font][/b][b][size=12pt][/size][/b][/size]
[size=2] [/size]
[size=2][font=宋体]在讲[/font]MySQL[font=宋体]的[/font]Join[font=宋体]语法前还是先回顾一下联结的语法，呵呵，其实连我自己都忘得差不多了，那就大家一起温习吧（如果内容有错误或有疑问，可以来信咨询：陈朋奕[/font] chenpengyi#gmail.com[font=宋体]），国内关于[/font]MySQL[font=宋体]联结查询的资料十分少，相信大家在看了本文后会对[/font]MySQL[font=宋体]联结语法有相当清晰的了解，也不会被[/font]Oracle[font=宋体]的外联结的（“＋”号）弄得糊涂了。[/font][/size]
[size=2] [/size]
[size=2][font=宋体]在[/font]SQL[font=宋体]标准中规划的（[/font]Join[font=宋体]）联结大致分为下面四种：[/font][/size]
[size=2]1．
[font=宋体]内联结：将两个表中存在联结关系的字段符合联结关系的那些记录形成记录集的联结。[/font][/size]
[size=2]2．
[font=宋体]外联结：分为外左联结和外右联结。[/font][/size]
[size=2][font=宋体][b]左联结[/b][/font][b]A[/b][font=宋体][b]、[/b][/font][b]B[/b][font=宋体][b]表的意思[/b][/font][font=宋体]就是将表[/font]A[font=宋体]中的全部记录和表[/font]B[font=宋体]中联结的字段与表[/font]A[font=宋体]的联结字段符合联结条件的那些记录形成的记录集的联结，这里注意的是最后出来的记录集会包括表[/font]A[font=宋体]的全部记录。[/font][/size]
[size=2][font=宋体]右联结[/font]A[font=宋体]、[/font]B[font=宋体]表的结果和左联结[/font]B[font=宋体]、[/font]A[font=宋体]的结果是一样的，也就是说：[/font][/size]
[size=2]Select A.name B.name From A Left Join B On A.id=B.id [/size]
[size=2][font=宋体]和[/font]Select A.name B.name From B Right Join A on B.id=A.id[font=宋体]执行后的结果是一样的。[/font][/size]
[size=2]3[font=宋体]．全联结：将两个表中存在联结关系的字段的所有记录取出形成记录集的联结（这个不需要记忆，只要是查询中提到了的表的字段都会取出，无论是否符合联结条件，因此意义不大）。[/font][/size]
[size=2]4[font=宋体]．无联结：不用解释了吧，就是没有使用联结功能呗，也有自联结的说法。[/font][/size]
[size=2] [/size]
[size=2][font=宋体]这里我有个比较简便的记忆方法，内外联结的区别是内联结将去除所有不符合条件的记录，而外联结则保留其中部分。外左联结与外右联结的区别在于如果用[/font]A[font=宋体]左联结[/font]B[font=宋体]则[/font]A[font=宋体]中所有记录都会保留在结果中，此时[/font]B[font=宋体]中只有符合联结条件的记录，而右联结相反，这样也就不会混淆了。其实大家回忆高等教育出版社出版的《数据库系统概论》书中讲到关系代数那章（就是将笛卡儿积和投影那章）的内容，相信不难理解这些联结功能的内涵。[/font][/size]

笑傲江湖 发表于 2007-8-21 11:02

[size=2][b][size=12pt]2．
[/size][/b][b][size=12pt]MySQL[/size][/b][b][font=宋体][size=12pt]联结（[/size][/font][/b][b][size=12pt]Join[/size][/b][b][font=宋体][size=12pt]）的语法[/size][/font][/b][b][size=12pt][/size][/b][/size]
[size=10.5pt][size=2] [/size][/size]
[size=2][size=10.5pt]MySQL[/size][font=宋体]支持[/font][size=10.5pt]Select[/size][font=宋体]和某些[/font][size=10.5pt]Update[/size][font=宋体]和[/font][size=10.5pt]Delete[/size][font=宋体]情况下的[/font][size=10.5pt]Join[/size][font=宋体]语法，具体语法上的细节有：[/font][size=10.5pt][/size][/size]
[size=10.5pt][size=2] [/size][/size]
[size=10.5pt][size=2]table_references:[/size][/size]
[size=10.5pt][size=2]
table_reference [, table_reference] …[/size][/size]
[size=10.5pt][size=2] [/size][/size]
[size=10.5pt][size=2]table_reference:[/size][/size]
[size=10.5pt][size=2]
table_factor[/size][/size]
[size=10.5pt][size=2]
| join_table[/size][/size]
[size=10.5pt][size=2] [/size][/size]
[size=10.5pt][size=2]table_factor:[/size][/size]
[size=10.5pt][size=2]
tbl_name [[AS] alias][/size][/size]
[size=10.5pt][size=2]
[{USE|IGNORE|FORCE} INDEX (key_list)][/size][/size]
[size=10.5pt][size=2]
| ( table_references )[/size][/size]
[size=10.5pt][size=2]
| { OJ table_reference LEFT OUTER JOIN table_reference[/size][/size]
[size=10.5pt][size=2]
ON conditional_expr }[/size][/size]
[size=10.5pt][size=2] [/size][/size]
[size=10.5pt][size=2]join_table:[/size][/size]
[size=10.5pt][size=2]
table_reference [INNER | CROSS] JOIN table_factor [join_condition][/size][/size]
[size=10.5pt][size=2]
| table_reference STRAIGHT_JOIN table_factor[/size][/size]
[size=10.5pt][size=2]
| table_reference STRAIGHT_JOIN table_factor ON condition[/size][/size]
[size=10.5pt][size=2]
| table_reference LEFT [OUTER] JOIN table_reference join_condition[/size][/size]
[size=10.5pt][size=2]
| table_reference NATURAL [LEFT [OUTER]] JOIN table_factor[/size][/size]
[size=10.5pt][size=2]
| table_reference RIGHT [OUTER] JOIN table_reference join_condition[/size][/size]
[size=10.5pt][size=2]
| table_reference NATURAL [RIGHT [OUTER]] JOIN table_factor[/size][/size]
[size=10.5pt][size=2] [/size][/size]
[size=10.5pt][size=2]join_condition:[/size][/size]
[size=10.5pt][size=2]
ON conditional_expr | USING (column_list)[/size][/size]
[size=10.5pt][size=2] [/size][/size]
[size=2][font=宋体]上面的用法摘自权威资料，不过大家看了是否有点晕呢？呵呵，应该问题主要还在于[/font][size=10.5pt]table_reference[/size][font=宋体]是什么，[/font][size=10.5pt]table_factor[/size][font=宋体]又是什么？这里的[/font][size=10.5pt]table_reference[/size][font=宋体]其实就是表的引用的意思，因为在[/font][size=10.5pt]MySQL[/size][font=宋体]看来，联结就是一种对表的引用，因此把需要联结的表定义为[/font][size=10.5pt]table_reference[/size][font=宋体]，同时在[/font][size=10.5pt]SQL Standard[/size][font=宋体]中也是如此看待的。而[/font][size=10.5pt]table_factor[/size][font=宋体]则是[/font][size=10.5pt]MySQL[/size][font=宋体]对这个引用的功能上的增强和扩充，使得引用的表可以是括号内的一系列表，如下面例子中的[/font][size=10.5pt]JOIN[/size][font=宋体]后面括号：[/font][size=10.5pt][/size][/size]
[size=10.5pt][size=2] [/size][/size]
[size=10.5pt][size=2]SELECT * FROM t1 LEFT JOIN (t2, t3, t4) ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)[/size][/size]
[size=10.5pt][size=2] [/size][/size]
[size=2][font=宋体]这个语句的执行结果和下面语句其实是一样的：[/font][size=10.5pt][/size][/size]
[size=10.5pt][size=2] [/size][/size]
[size=10.5pt][size=2]SELECT * FROM t1 LEFT JOIN (t2 CROSS JOIN t3 CROSS JOIN t4) [/size][/size]
[size=10.5pt][size=2]
ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)[/size][/size]
[size=10.5pt][size=2] [/size][/size]
[size=2][font=宋体]这两个例子不仅让我们了解了[/font][size=10.5pt]MySQL[/size][font=宋体]中[/font][size=10.5pt]table_factor[/size][font=宋体]和[/font][size=10.5pt]table_reference[/size][font=宋体]含义，同时能理解一点[/font][size=10.5pt]CROSS JOIN[/size][font=宋体]的用法，我要补充的是在[/font][size=10.5pt]MySQL[/size][font=宋体]现有版本中[/font][size=10.5pt]CROSS JOIN[/size][font=宋体]的作用和[/font][size=10.5pt]INNER JOIN[/size][font=宋体]是一样的（虽然在[/font][size=10.5pt]SQL Standard[/size][font=宋体]中是不一样的，然而在[/font][size=10.5pt]MySQL[/size][font=宋体]中他们的区别仅仅是[/font][size=10.5pt]INNER JOIN[/size][font=宋体]需要附加[/font][size=10.5pt]ON[/size][font=宋体]参数的语句，而[/font][size=10.5pt]CROSS JOIN[/size][font=宋体]不需要）。[/font][size=10.5pt][/size][/size]
[size=2][font=宋体]既然说到了[/font][size=10.5pt]ON[/size][font=宋体]语句，那就解释一下吧，[/font][size=10.5pt]ON[/size][font=宋体]语句其实和[/font][size=10.5pt]WHERE[/size][font=宋体]语句功能大致相当，只是这里的[/font][size=10.5pt]ON[/size][font=宋体]语句是专门针对联结表的，[/font][size=10.5pt]ON[/size][font=宋体]语句后面的条件的要求和书写方式和[/font][size=10.5pt]WHERE[/size][font=宋体]语句的要求是一样的，大家基本上可以把[/font][size=10.5pt]ON[/size][font=宋体]当作[/font][size=10.5pt]WHERE[/size][font=宋体]用。[/font][size=10.5pt][/size][/size]
[size=2][font=宋体]大家也许也看到了[/font][size=10.5pt]OJ table_reference LEFT OUTER JOIN table_reference[/size][font=宋体]这个句子，这不是[/font][size=10.5pt]MySQL[/size][font=宋体]的标准写法，只是为了和[/font][size=10.5pt]ODBC[/size][font=宋体]的[/font][size=10.5pt]SQL[/size][font=宋体]语法兼容而设定的，我很少用，[/font][size=10.5pt]Java[/size][font=宋体]的人更是不会用，所以也不多解释了。[/font][size=10.5pt][/size][/size]
[size=2][font=宋体]那下面就具体讲讲简单的[/font][size=10.5pt]JOIN[/size][font=宋体]的用法了。首先我们假设有[/font][size=10.5pt]2[/size][font=宋体]个表[/font][size=10.5pt]A[/size][font=宋体]和[/font][size=10.5pt]B[/size][font=宋体]，他们的表结构和字段分别为：[/font][size=10.5pt][/size][/size]
[size=10.5pt][size=2] [/size][/size]
[size=2][font=宋体]表[/font][size=10.5pt]A[/size][font=宋体]：[/font][size=10.5pt][/size][/size]
[table][tr][td=1,1,67][size=10.5pt][size=2]ID[/size][/size]
[/td][td=1,1,96][size=10.5pt][size=2]Name[/size][/size]
[/td][/tr][tr][td=1,1,67][size=10.5pt][size=2]1[/size][/size]
[/td][td=1,1,96][size=10.5pt][size=2]Tim[/size][/size]
[/td][/tr][tr][td=1,1,67][size=10.5pt][size=2]2[/size][/size]
[/td][td=1,1,96][size=10.5pt][size=2]Jimmy[/size][/size]
[/td][/tr][tr][td=1,1,67][size=10.5pt][size=2]3[/size][/size]
[/td][td=1,1,96][size=10.5pt][size=2]John[/size][/size]
[/td][/tr][tr][td=1,1,67][size=10.5pt][size=2]4[/size][/size]
[/td][td=1,1,96][size=10.5pt][size=2]Tom[/size][/size]
[/td][/tr][/table][size=2][font=宋体]表[/font][size=10.5pt]B[/size][font=宋体]：[/font][size=10.5pt][/size][/size]
[table][tr][td=1,1,67][size=10.5pt][size=2]ID[/size][/size]
[/td][td=1,1,96][size=10.5pt][size=2]Hobby[/size][/size]
[/td][/tr][tr][td=1,1,67][size=10.5pt][size=2]1[/size][/size]
[/td][td=1,1,96][size=10.5pt][size=2]Football[/size][/size]
[/td][/tr][tr][td=1,1,67][size=10.5pt][size=2]2[/size][/size]
[/td][td=1,1,96][size=10.5pt][size=2]Basketball[/size][/size]
[/td][/tr][tr][td=1,1,67][size=10.5pt][size=2]2[/size][/size]
[/td][td=1,1,96][size=10.5pt][size=2]Tennis[/size][/size]
[/td][/tr][tr][td=1,1,67][size=10.5pt][size=2]4[/size][/size]
[/td][td=1,1,96][size=10.5pt][size=2]Soccer[/size][/size]
[/td][/tr][/table][size=10.5pt][size=2] [/size][/size]
[size=2]1．
[font=宋体]内联结：[/font][size=10.5pt][/size][/size]
[size=2][size=10.5pt]Select A.Name B.Hobby from A, B where A.id = B.id[/size][font=宋体]，这是隐式的内联结，查询的结果是：[/font][size=10.5pt][/size][/size]
[table][tr][td=1,1,67][size=10.5pt][size=2]Name[/size][/size]
[/td][td=1,1,96][size=10.5pt][size=2]Hobby[/size][/size]
[/td][/tr][tr][td=1,1,67][size=10.5pt][size=2]Tim[/size][/size]
[/td][td=1,1,96][size=10.5pt][size=2]Football[/size][/size]
[/td][/tr][tr][td=1,1,67][size=10.5pt][size=2]Jimmy[/size][/size]
[/td][td=1,1,96][size=10.5pt][size=2]Basketball[/size][/size]
[/td][/tr][tr][td=1,1,67][size=10.5pt][size=2]Jimmy[/size][/size]
[/td][td=1,1,96][size=10.5pt][size=2]Tennis[/size][/size]
[/td][/tr][tr][td=1,1,67][size=10.5pt][size=2]Tom[/size][/size]
[/td][td=1,1,96][size=10.5pt][size=2]Soccer[/size][/size]
[/td][/tr][/table][size=2][font=宋体]它的作用和[/font][size=10.5pt] Select A.Name from A INNER JOIN B ON A.id = B.id[/size][font=宋体]是一样的。这里的[/font][size=10.5pt]INNER JOIN[/size][font=宋体]换成[/font][size=10.5pt]CROSS JOIN[/size][font=宋体]也是可以的。[/font][size=10.5pt][/size][/size]

笑傲江湖 发表于 2007-8-21 11:03

[size=2]2．
[font=宋体]外左联结[/font][size=10.5pt][/size][/size]
[size=2][size=10.5pt]Select A.Name from A Left JOIN B ON A.id = B.id[/size][font=宋体]，典型的外左联结，这样查询得到的结果将会是保留所有[/font][size=10.5pt]A[/size][font=宋体]表中联结字段的记录，若无与其相对应的[/font][size=10.5pt]B[/size][font=宋体]表中的字段记录则留空，结果如下：[/font][size=10.5pt][/size][/size]
[table][tr][td=1,1,67][size=10.5pt][size=2]Name[/size][/size]
[/td][td=1,1,120][size=10.5pt][size=2]Hobby[/size][/size]
[/td][/tr][tr][td=1,1,67][size=10.5pt][size=2]Tim[/size][/size]
[/td][td=1,1,120][size=10.5pt][size=2]Football[/size][/size]
[/td][/tr][tr][td=1,1,67][size=10.5pt][size=2]Jimmy[/size][/size]
[/td][td=1,1,120][size=2][size=10.5pt]Basketball[/size][font=宋体]，[/font][size=10.5pt]Tennis[/size][/size]
[/td][/tr][tr][td=1,1,67][size=10.5pt][size=2]John[/size][/size]
[/td][td=1,1,120][size=10.5pt][size=2] [/size][/size]
[/td][/tr][tr][td=1,1,67][size=10.5pt][size=2]Tom[/size][/size]
[/td][td=1,1,120][size=10.5pt][size=2]Soccer[/size][/size]
[/td][/tr][/table][size=2][font=宋体]所以从上面结果看出，因为[/font][size=10.5pt]A[/size][font=宋体]表中的[/font][size=10.5pt]John[/size][font=宋体]记录的[/font][size=10.5pt]ID[/size][font=宋体]没有在[/font][size=10.5pt]B[/size][font=宋体]表中有对应[/font][size=10.5pt]ID[/size][font=宋体]，因此为空，但[/font][size=10.5pt]Name[/size][font=宋体]栏仍有[/font][size=10.5pt]John[/size][font=宋体]记录。[/font][size=10.5pt][/size][/size]
[size=2]3．
[font=宋体]外右联结[/font][size=10.5pt][/size][/size]
[size=2][font=宋体]如果把上面查询改成外右联结：[/font][size=10.5pt]Select A.Name from A Right JOIN B ON A.id = B.id[/size][font=宋体]，则结果将会是：[/font][size=10.5pt][/size][/size]
[table][tr][td=1,1,67][size=10.5pt][size=2]Name[/size][/size]
[/td][td=1,1,84][size=10.5pt][size=2]Hobby[/size][/size]
[/td][/tr][tr][td=1,1,67][size=10.5pt][size=2]Tim[/size][/size]
[/td][td=1,1,84][size=10.5pt][size=2]Football[/size][/size]
[/td][/tr][tr][td=1,1,67][size=10.5pt][size=2]Jimmy[/size][/size]
[/td][td=1,1,84][size=10.5pt][size=2]Basketball[/size][/size]
[/td][/tr][tr][td=1,1,67][size=10.5pt][size=2]Jimmy[/size][/size]
[/td][td=1,1,84][size=10.5pt][size=2]Tennis[/size][/size]
[/td][/tr][tr][td=1,1,67][size=10.5pt][size=2]Tom[/size][/size]
[/td][td=1,1,84][size=10.5pt][size=2]Soccer[/size][/size]
[/td][/tr][/table][size=2][font=宋体]这样的结果都是我们可以从外左联结的结果中猜到的了。[/font][size=10.5pt][/size][/size]
[size=2][font=宋体]说到这里大家是否对联结查询了解多了？这个原本看来高深的概念一下子就理解了，恍然大悟了吧（呵呵，开玩笑了）？最后给大家讲讲[/font][size=10.5pt]MySQL[/size][font=宋体]联结查询中的某些参数的作用：[/font][size=10.5pt][/size][/size]
[size=10.5pt][size=2] [/size][/size]
[size=2][size=10.5pt]1[/size][font=宋体]．[/font][size=10.5pt]USING (column_list)[/size][font=宋体]：其作用是为了方便书写联结的多对应关系，大部分情况下[/font][size=10.5pt]USING[/size][font=宋体]语句可以用[/font][size=10.5pt]ON[/size][font=宋体]语句来代替，如下面例子：[/font][size=10.5pt][/size][/size]
[size=10.5pt][size=2] [/size][/size]
[size=2][size=10.5pt]a LEFT JOIN b USING (c1,c2,c3)[/size][font=宋体]，其作用相当于下面语句[/font][size=10.5pt][/size][/size]
[size=10.5pt][size=2]a LEFT JOIN b ON a.c1=b.c1 AND a.c2=b.c2 AND a.c3=b.c3[/size][/size]
[size=10.5pt][size=2] [/size][/size]
[size=2][font=宋体]只是用[/font][size=10.5pt]ON[/size][font=宋体]来代替会书写比较麻烦而已。[/font][size=10.5pt][/size][/size]
[size=10.5pt][size=2] [/size][/size]
[size=2][size=10.5pt]2[/size][font=宋体]．[/font][size=10.5pt]NATURAL [LEFT] JOIN[/size][font=宋体]：这个句子的作用相当于[/font][size=10.5pt]INNER JOIN[/size][font=宋体]，或者是在[/font][size=10.5pt]USING[/size][font=宋体]子句中包含了联结的表中所有字段的[/font][size=10.5pt]Left JOIN[/size][font=宋体]（左联结）。[/font][size=10.5pt][/size][/size]
[size=10.5pt][size=2] [/size][/size]
[size=2][size=10.5pt]3[/size][font=宋体]．[/font][size=10.5pt]STRAIGHT_JOIN[/size][font=宋体]：由于默认情况下[/font][size=10.5pt]MySQL[/size][font=宋体]在进行表的联结的时候会先读入左表，当使用了这个参数后[/font][size=10.5pt]MySQL[/size][font=宋体]将会先读入右表，这是个[/font][size=10.5pt]MySQL[/size][font=宋体]的内置优化参数，大家应该在特定情况下使用，譬如已经确认右表中的记录数量少，在筛选后能大大提高查询速度。[/font][size=10.5pt][/size][/size]
[size=10.5pt][size=2] [/size][/size]
[size=2][font=宋体]最后要说的就是，在[/font][size=10.5pt]MySQL5.0[/size][font=宋体]以后，运算顺序得到了重视，所以对多表的联结查询可能会错误以子联结查询的方式进行。譬如你需要进行多表联结，因此你输入了下面的联结查询：[/font][size=10.5pt][/size][/size]
[size=10.5pt][size=2] [/size][/size]
[size=10.5pt][size=2]SELECT t1.id,t2.id,t3.id [/size][/size]
[size=10.5pt][size=2]
FROM t1,t2 [/size][/size]
[size=10.5pt][size=2]
LEFT JOIN t3 ON (t3.id=t1.id)[/size][/size]
[size=10.5pt][size=2]
WHERE t1.id=t2.id;[/size][/size]
[size=10.5pt][size=2] [/size][/size]
[size=2][font=宋体]但是[/font][size=10.5pt]MySQL[/size][font=宋体]并不是这样执行的，其后台的真正执行方式是下面的语句：[/font][size=10.5pt][/size][/size]
[size=10.5pt][size=2] [/size][/size]
[size=10.5pt][size=2]SELECT t1.id,t2.id,t3.id [/size][/size]
[size=10.5pt][size=2]
FROM t1,(
t2 LEFT JOIN t3 ON (t3.id=t1.id)
)[/size][/size]
[size=10.5pt][size=2]
WHERE t1.id=t2.id;[/size][/size]
[size=10.5pt][size=2] [/size][/size]
[size=2][font=宋体]这并不是我们想要的效果，所以我们需要这样输入：[/font][size=10.5pt][/size][/size]
[size=10.5pt][size=2] [/size][/size]
[size=10.5pt][size=2]SELECT t1.id,t2.id,t3.id [/size][/size]
[size=10.5pt][size=2]
FROM (t1,t2)[/size][/size]
[size=10.5pt][size=2]
LEFT JOIN t3 ON (t3.id=t1.id)[/size][/size]
[size=10.5pt][size=2]
WHERE t1.id=t2.id;[/size][/size]
[size=10.5pt][size=2] [/size][/size]
[size=2][font=宋体]在这里括号是相当重要的，因此以后在写这样的查询的时候我们不要忘记了多写几个括号，至少这样能避免很多错误（因为这样的错误是很难被开发人员发现的）。如果对上面内容有疑问可以来信查询：[/font][font=宋体]陈朋奕[/font] chenpengyi#gmail.com[font=宋体]，转载请注明出处及作者。[/font][size=10.5pt][/size][/size]

想飞的石头 发表于 2007-9-4 22:06

好详细.
已经明白了不少.

查看完整版本: MySQL的联结（Join）语法