mysql之join

最新推荐文章于 2024-02-20 10:31:03 发布

bai_lu_sz

最新推荐文章于 2024-02-20 10:31:03 发布

阅读量875

点赞数

分类专栏： mysql

mysql 专栏收录该内容

49 篇文章

订阅专栏

http://blog.sina.com.cn/s/blog_56d8ea9001011lod.html

1、交叉连接（CROSS JOIN）：有两种，显式的和隐式的，不带ON子句，返回的是两表的乘积，也叫笛卡尔积，返回记录的个数应当是a和b表中符合记录的和。

　　显式：select [cols_list] from a cross join b where[condition]

　　隐式：select [cols_list] from a， b where [condition]

2、内连接（INNER JOIN）：有两种，显式的和隐式的，返回连接表中符合连接条件和查询条件的数据行，和我们写普通的sql相同。

　　显式：select [cols_list] from a inner join b on [condition] where[condition] 如果如此用，必须带on

　　隐式：select [cols_list] from a， b where [condition]

3、外连接（OUTERJOIN）：外连不但返回符合连接和查询条件的数据行，还返回不符合条件的一些行。外连接分三类：左外连接（LEFT OUTERJOIN）、右外连接（RIGHT OUTER JOIN）和全外连接（FULL OUTER JOIN）。

　　三者的共同点是都返回符合连接条件和查询条件（即：内连接）的数据行。不同点如下：

　　左外连接还返回左表中不符合连接条件单符合查询条件的数据行。

　　右外连接还返回右表中不符合连接条件单符合查询条件的数据行。

　　全外连接还返回左表中不符合连接条件单符合查询条件的数据行，并且还返回右表中不符合连接条件单符合查询条件的数据行。全外连接实际是上左外连接和右外连接的数学合集（去掉重复），即“全外=左外UNION 右外”。

　　说明：左表就是在“（LEFT OUTER JOIN）”关键字左边的表。右表当然就是右边的了。在三种类型的外连接中，OUTER关键字是可省略的

　　如：select [cols_list] from a left join b on [condition] where[condition]

　　多个表的情况可以用：

　　select [cols_list] from a left join b on [condition] innerjoin c on [condition] where [condition]

http://www.jb51.net/article/15386.htm

下面是例子分析
表A记录如下：
aID aNum
1 a20050111
2 a20050112
3 a20050113
4 a20050114
5 a20050115

表B记录如下:
bID bName
1 2006032401
2 2006032402
3 2006032403
4 2006032404
8 2006032408

创建这两个表SQL语句如下：
CREATE TABLE a
aID int( 1 ) AUTO_INCREMENT PRIMARY KEY ,
aNum char( 20 )
)
CREATE TABLE b(
bID int( 1 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
bName char( 20 )
)

INSERT INTO a
VALUES ( 1, 'a20050111' ) , ( 2, 'a20050112' ) , ( 3, 'a20050113' ) , ( 4, 'a20050114' ) , ( 5, 'a20050115' ) ;

INSERT INTO b
VALUES ( 1, ' 2006032401' ) , ( 2, '2006032402' ) , ( 3, '2006032403' ) , ( 4, '2006032404' ) , ( 8, '2006032408' ) ;

实验如下:
1.left join(左联接)

sql语句如下:
SELECT * FROM a
LEFT JOIN b
ON a.aID =b.bID

结果如下:
aID aNum bID bName
1 a20050111 1 2006032401
2 a20050112 2 2006032402
3 a20050113 3 2006032403
4 a20050114 4 2006032404
5 a20050115 NULL NULL
（所影响的行数为 5 行）

结果说明:
left join是以A表的记录为基础的,A可以看成左表,B可以看成右表,left join是以左表为准的.
换句话说,左表(A)的记录将会全部表示出来,而右表(B)只会显示符合搜索条件的记录(例子中为: A.aID = B.bID).
B表记录不足的地方均为NULL.

2.right join(右联接)

sql语句如下:
SELECT * FROM a
RIGHT JOING b
ON a.aID = b.bID

结果如下:
aID aNum bID bName
1 a20050111 1 2006032401
2 a20050112 2 2006032402
3 a20050113 3 2006032403
4 a20050114 4 2006032404
NULL NULL 8 2006032408
（所影响的行数为 5 行）

结果说明:
仔细观察一下,就会发现,和left join的结果刚好相反,这次是以右表(B)为基础的,A表不足的地方用NULL填充.

3.inner join(相等联接或内联接)

sql语句如下:
SELECT * FROM a
INNER JOIN b
ON a.aID =b.bID

等同于以下SQL句:
SELECT *
FROM a,b
WHERE a.aID = b.bID

结果如下:
aID aNum bID bName
1 a20050111 1 2006032401
2 a20050112 2 2006032402
3 a20050113 3 2006032403
4 a20050114 4 2006032404

结果说明:
很明显,这里只显示出了 A.aID = B.bID的记录.这说明inner join并不以谁为基础,它只显示符合条件的记录.
LEFT JOIN操作用于在任何的 FROM 子句中，

组合来源表的记录。使用 LEFT JOIN 运算来创建一个左边外部联接。左边外部联接将包含了从第一个（左边）开始的两个表中的全部记录，即
使在第二个（右边）表中并没有相符值的记录。

语法：FROM table1 LEFT JOIN table2 ON table1.field1 compopr table2.field2
说明：table1, table2参数用于指定要将记录组合的表的名称。
field1, field2参数指定被联接的字段的名称。且这些字段必须有相同的数据类型及包含相同类型的数据，但它们不需要有相同的
名称。
compopr参数指定关系比较运算符："="， "<"， ">"， "<="， ">=" 或 "<>"。
如果在INNER JOIN操作中要联接包含Memo 数据类型或 OLE Object 数据类型数据的字段，将会发生错误。

http://www.blogjava.net/chenpengyi/archive/2005/10/17/15747.html

1．内联结、外联结、左联结、右联结的含义及区别：

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

在SQL标准中规划的（Join）联结大致分为下面四种：

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

2．外联结：分为外左联结和外右联结。

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

右联结A、B表的结果和左联结B、A的结果是一样的，也就是说：

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左联结B则A中所有记录都会保留在结果中，此时B中只有符合联结条件的记录，而右联结相反，这样也就不会混淆了。其实大家回忆高等教育出版社出版的《数据库系统概论》书中讲到关系代数那章（就是将笛卡儿积和投影那章）的内容，相信不难理解这些联结功能的内涵。

2． MySQL联结（Join）的语法

MySQL支持Select和某些Update和Delete情况下的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)

这两个例子不仅让我们了解了MySQL中table_factor和table_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的标准写法，只是为了和ODBC的SQL语法兼容而设定的，我很少用，Java的人更是不会用，所以也不多解释了。

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

表A：