朋的博客

MySQL资料,Java技术,管理思想,博弈论,Ajax,XP极限编程,H.264,HEVC,HDR
随笔 - 86, 文章 - 59, 评论 - 1069, 引用 - 0
数据加载中……

MySQL的 连接/联结(Join)语法(原创!)

MySQL的联结(Join)语法

1.内联结、外联结、左联结、右联结的含义及区别:

 

在讲MySQLJoin语法前还是先回顾一下联结的语法,呵呵,其实连我自己都忘得差不多了,那就大家一起温习吧(如果内容有错误或有疑问,可以来信咨询:陈朋奕 chenpengyi#gmail.com),国内关于MySQL联结查询的资料十分少,相信大家在看了本文后会对MySQL联结语法有相当清晰的了解,也不会被Oracle的外联结的(“+”号)弄得糊涂了。

 

SQL标准中规划的(Join)联结大致分为下面四种:

1.  内联结:将两个表中存在联结关系的字段符合联结关系的那些记录形成记录集的联结。

2.  外联结:分为外左联结和外右联结。

左联结AB表的意思就是将表A中的全部记录和表B中联结的字段与表A的联结字段符合联结条件的那些记录形成的记录集的联结,这里注意的是最后出来的记录集会包括表A的全部记录。

右联结AB表的结果和左联结BA的结果是一样的,也就是说:

Select A.name B.name From A Left Join B On A.id=B.id

Select A.name B.name From B Right Join A on B.id=A.id执行后的结果是一样的。

3.全联结:将两个表中存在联结关系的字段的所有记录取出形成记录集的联结(这个不需要记忆,只要是查询中提到了的表的字段都会取出,无论是否符合联结条件,因此意义不大)。

4.无联结:不用解释了吧,就是没有使用联结功能呗,也有自联结的说法。

 

这里我有个比较简便的记忆方法,内外联结的区别是内联结将去除所有不符合条件的记录,而外联结则保留其中部分。外左联结与外右联结的区别在于如果用A左联结BA中所有记录都会保留在结果中,此时B中只有符合联结条件的记录,而右联结相反,这样也就不会混淆了。其实大家回忆高等教育出版社出版的《数据库系统概论》书中讲到关系代数那章(就是将笛卡儿积和投影那章)的内容,相信不难理解这些联结功能的内涵。

 

2.  MySQL联结(Join)的语法

 

MySQL支持Select和某些UpdateDelete情况下的Join语法,具体语法上的细节有:

 

table_references:

    table_reference [, table_reference] …

 

table_reference:

    table_factor

  | join_table

 

table_factor:

    tbl_name [[AS] alias]

        [{USE|IGNORE|FORCE} INDEX (key_list)]

  | ( table_references )

  | { OJ table_reference LEFT OUTER JOIN table_reference

        ON conditional_expr }

 

join_table:

    table_reference [INNER | CROSS] JOIN table_factor [join_condition]

  | table_reference STRAIGHT_JOIN table_factor

  | table_reference STRAIGHT_JOIN table_factor ON condition

  | table_reference LEFT [OUTER] JOIN table_reference join_condition

  | table_reference NATURAL [LEFT [OUTER]] JOIN table_factor

  | table_reference RIGHT [OUTER] JOIN table_reference join_condition

  | table_reference NATURAL [RIGHT [OUTER]] JOIN table_factor

 

join_condition:

    ON conditional_expr | USING (column_list)

 

上面的用法摘自权威资料,不过大家看了是否有点晕呢?呵呵,应该问题主要还在于table_reference是什么,table_factor又是什么?这里的table_reference其实就是表的引用的意思,因为在MySQL看来,联结就是一种对表的引用,因此把需要联结的表定义为table_reference,同时在SQL Standard中也是如此看待的。而table_factor则是MySQL对这个引用的功能上的增强和扩充,使得引用的表可以是括号内的一系列表,如下面例子中的JOIN后面括号:

 

SELECT * FROM t1 LEFT JOIN (t2, t3, t4) ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)

 

这个语句的执行结果和下面语句其实是一样的:

 

SELECT * FROM t1 LEFT JOIN (t2 CROSS JOIN t3 CROSS JOIN t4)

                 ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)

 

这两个例子不仅让我们了解了MySQLtable_factortable_reference含义,同时能理解一点CROSS JOIN的用法,我要补充的是在MySQL现有版本中CROSS JOIN的作用和INNER JOIN是一样的(虽然在SQL Standard中是不一样的,然而在MySQL中他们的区别仅仅是INNER JOIN需要附加ON参数的语句,而CROSS JOIN不需要)。

既然说到了ON语句,那就解释一下吧,ON语句其实和WHERE语句功能大致相当,只是这里的ON语句是专门针对联结表的,ON语句后面的条件的要求和书写方式和WHERE语句的要求是一样的,大家基本上可以把ON当作WHERE用。

大家也许也看到了OJ table_reference LEFT OUTER JOIN table_reference这个句子,这不是MySQL的标准写法,只是为了和ODBCSQL语法兼容而设定的,我很少用,Java的人更是不会用,所以也不多解释了。

那下面就具体讲讲简单的JOIN的用法了。首先我们假设有2个表AB,他们的表结构和字段分别为:

 

A

ID

Name

1

Tim

2

Jimmy

3

John

4

Tom

B

ID

Hobby

1

Football

2

Basketball

2

Tennis

4

Soccer

 

1.  内联结:

Select A.Name B.Hobby from A, B where A.id = B.id,这是隐式的内联结,查询的结果是:

Name

Hobby

Tim

Football

Jimmy

Basketball

Jimmy

Tennis

Tom

Soccer

它的作用和 Select A.Name from A INNER JOIN B ON A.id = B.id是一样的。这里的INNER JOIN换成CROSS JOIN也是可以的。

2.  外左联结

Select A.Name from A Left JOIN B ON A.id = B.id,典型的外左联结,这样查询得到的结果将会是保留所有A表中联结字段的记录,若无与其相对应的B表中的字段记录则留空,结果如下:

Name

Hobby

Tim

Football

Jimmy

BasketballTennis

John

 

Tom

Soccer

所以从上面结果看出,因为A表中的John记录的ID没有在B表中有对应ID,因此为空,但Name栏仍有John记录。

3.  外右联结

如果把上面查询改成外右联结:Select A.Name from A Right JOIN B ON A.id = B.id,则结果将会是:

Name

Hobby

Tim

Football

Jimmy

Basketball

Jimmy

Tennis

Tom

Soccer

这样的结果都是我们可以从外左联结的结果中猜到的了。

说到这里大家是否对联结查询了解多了?这个原本看来高深的概念一下子就理解了,恍然大悟了吧(呵呵,开玩笑了)?最后给大家讲讲MySQL联结查询中的某些参数的作用:

 

1USING (column_list):其作用是为了方便书写联结的多对应关系,大部分情况下USING语句可以用ON语句来代替,如下面例子:

 

a LEFT JOIN b USING (c1,c2,c3),其作用相当于下面语句

a LEFT JOIN b ON a.c1=b.c1 AND a.c2=b.c2 AND a.c3=b.c3

 

只是用ON来代替会书写比较麻烦而已。

 

2NATURAL [LEFT] JOIN:这个句子的作用相当于INNER JOIN,或者是在USING子句中包含了联结的表中所有字段的Left JOIN(左联结)。

 

3STRAIGHT_JOIN:由于默认情况下MySQL在进行表的联结的时候会先读入左表,当使用了这个参数后MySQL将会先读入右表,这是个MySQL的内置优化参数,大家应该在特定情况下使用,譬如已经确认右表中的记录数量少,在筛选后能大大提高查询速度。

 

最后要说的就是,在MySQL5.0以后,运算顺序得到了重视,所以对多表的联结查询可能会错误以子联结查询的方式进行。譬如你需要进行多表联结,因此你输入了下面的联结查询:

 

SELECT t1.id,t2.id,t3.id

    FROM t1,t2

    LEFT JOIN t3 ON (t3.id=t1.id)

    WHERE t1.id=t2.id;

 

但是MySQL并不是这样执行的,其后台的真正执行方式是下面的语句:

 

SELECT t1.id,t2.id,t3.id

    FROM t1,(  t2 LEFT JOIN t3 ON (t3.id=t1.id)  )

    WHERE t1.id=t2.id;

 

这并不是我们想要的效果,所以我们需要这样输入:

 

SELECT t1.id,t2.id,t3.id

    FROM (t1,t2)

    LEFT JOIN t3 ON (t3.id=t1.id)

    WHERE t1.id=t2.id;

 

在这里括号是相当重要的,因此以后在写这样的查询的时候我们不要忘记了多写几个括号,至少这样能避免很多错误(因为这样的错误是很难被开发人员发现的)。如果对上面内容有疑问可以来信查询:陈朋奕 chenpengyi#gmail.com,转载请注明出处及作者。

posted on 2005-10-17 22:53 benchensz 阅读(51935) 评论(24)  编辑  收藏 所属分类: 随便写写(比较有用,值得看看)

评论

# re: MySQL的联结(Join)语法(原创!)  回复  更多评论   

果然清楚,谢谢楼上给这么好的讲解,却是现在网上对这个的解释太少了,即使有也是比较无聊的。谢谢了。
2005-10-18 12:04 | tim163

# re: MySQL的联结(Join)语法(原创!)  回复  更多评论   

谢谢你的讲解,以前是学的SQL SERVER的连接,看来MYSQL的连接也差不多,更深刻的理解了。
2005-10-21 21:25 | mpshun

# re: MySQL的联结(Join)语法(原创!)  回复  更多评论   

我也顶一个
很简洁明了,喜欢这个,不知道楼主现在是做什么工作?
2005-10-21 22:31 | kknd

# re: MySQL的 连接/联结(Join)语法(原创!)  回复  更多评论   

这个是我见过讲解联接最好的了。
2005-11-01 17:33 | bill lee

# re: MySQL的 连接/联结(Join)语法(原创!)  回复  更多评论   

引用:**********************************
譬如你需要进行多表联结,因此你输入了下面的联结查询:

SELECT t1.id,t2.id,t3.id
FROM t1,t2
LEFT JOIN t3 ON (t3.id=t1.id)
WHERE t1.id=t2.id;

但是MySQL并不是这样执行的,其后台的真正执行方式是下面的语句:

SELECT t1.id,t2.id,t3.id
FROM t1,( t2 LEFT JOIN t3 ON (t3.id=t1.id) )
WHERE t1.id=t2.id;
结束引用********************************

请问大虾您是怎么知道mysql后台的执行方式?能不能给点线索,小弟实在不明白好端端的输入怎么就给改了方式运行哪?谢谢
2006-01-02 21:03 | 一叶小舟

# re: MySQL的 连接/联结(Join)语法(原创!)  回复  更多评论   

3x,温习了一下,真的很清楚
2006-01-04 14:31 | lisa

# re: MySQL的 连接/联结(Join)语法(原创!)  回复  更多评论   

解释清晰。非常感谢。
2006-02-11 15:36 | Foon

# re: MySQL的 连接/联结(Join)语法(原创!)  回复  更多评论   

谢谢楼主,好东西
2009-03-11 10:49 | ronald

# re: MySQL的 连接/联结(Join)语法(原创!)  回复  更多评论   

谢谢分享,写得很清楚,很容易看懂
2009-03-30 18:30 | SiemenLiu

# re: MySQL的 连接/联结(Join)语法(原创!)[未登录]  回复  更多评论   

看了很多,在这里才真的明白了,谢谢
2009-04-14 15:46 | Jun

# re: MySQL的 连接/联结(Join)语法(原创!)  回复  更多评论   

受益匪浅!
2009-04-29 10:36 | fenix

# re: MySQL的 连接/联结(Join)语法(原创!)  回复  更多评论   

受益匪浅
2010-09-25 16:32 | ai

# 提问  回复  更多评论   

在“2.外左联结”中的结果第二行Hobby有两个值,应该怎样把这两个值从结果集中取出来呢?
2011-08-08 15:06 | ts

# re: MySQL的 连接/联结(Join)语法(原创!)  回复  更多评论   

简单明了
2011-09-25 15:00 | nx

# re: MySQL的 连接/联结(Join)语法(原创!)  回复  更多评论   

写的太好了,学习啦
2011-11-02 17:39 | 瞬间的永恒

# re: MySQL的 连接/联结(Join)语法(原创!)  回复  更多评论   

SELECT b. * , k.id AS likeid , f.id AS followid ,m.username,m.domain FROM `anran_blog` AS b LEFT JOIN `anran_likes` AS k ON ( b.bid = k.bid AND k.uid ='' ) LEFT JOIN `anran_follow` AS f ON ( b.uid = f.touid and f.uid = '' ) LEFT JOIN `anran_member` as m on b.uid = m.uid where b.open = 1
2011-12-04 17:33 | mr.doooger

# re: MySQL的 连接/联结(Join)语法(原创!)  回复  更多评论   

可以帮忙解释这句sql语句吗?谢谢
SELECT b. * , k.id AS likeid , f.id AS followid ,m.username,m.domain FROM `anran_blog` AS b LEFT JOIN `anran_likes` AS k ON ( b.bid = k.bid AND k.uid ='' ) LEFT JOIN `anran_follow` AS f ON ( b.uid = f.touid and f.uid = '' ) LEFT JOIN `anran_member` as m on b.uid = m.uid where b.open = 1
2011-12-04 17:34 | mr.doooger

# re: MySQL的 连接/联结(Join)语法(原创!)  回复  更多评论   

多谢,讲的不错
2012-08-20 15:49 | juffun

# re: MySQL的 连接/联结(Join)语法(原创!)[未登录]  回复  更多评论   

大概懂了些..谢谢了
2012-11-29 21:01 | cone

# re: MySQL的 连接/联结(Join)语法(原创!)  回复  更多评论   

谢谢大哥,大哥辛苦了!
2013-09-08 20:13 | Ice_Xue

# re: MySQL的 连接/联结(Join)语法(原创!)  回复  更多评论   

很激动啊,解决了我一个大问题,上学的时候没好好学,现在要用才搞懂了!
2013-09-08 20:23 | Ice_Xue

# re: MySQL的 连接/联结(Join)语法(原创!)[未登录]  回复  更多评论   

Select A.Name from A Left JOIN B ON A.id = B.id

这个sql查询列中没有hobby,但是查询结果中却出现了hobby,确定不是sql写错了?
2014-11-14 10:41 | tiger

# re: MySQL的 连接/联结(Join)语法(原创!)[未登录]  回复  更多评论   

STRAIGHT_JOIN是不是解释错了?。。。是强制读取左边的表
STRAIGHT_JOIN is similar to JOIN, except that the left table is always read before the right table. This can be used for those (few) cases for which the join optimizer puts the tables in the wrong order.
在左表数据量小,但优化器顺序错误先读右表的情况下,强制先读左表吧。。。
2016-01-08 16:29 | null

只有注册用户登录后才能发表评论。


网站导航: