超详细mysql left join,right join,inner join用法分析
下面是例子分析
表A记录如下：
aID aNum
1a20050111
2a20050112
3a20050113
4a20050114
5a20050115
表B记录如下:
bID bName
12006032401
22006032402
32006032403
42006032404
82006032408
创建这两个表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(左联接)
SELECT*FROM a
LEFT JOIN b
ON a.aID=b.bID
结果如下:
aID aNum bID bName
1a2005011112006032401
2a2005011222006032402
3a2005011332006032403
4a2005011442006032404
5a20050115NULL NULL
（所影响的行数为5行）
结果说明:
left join是以A表的记录为基础的,A可以看成左表,B可以看成右表,left join是以左表为准的.
换句话说,左表(A)的记录将会全部表示出来,而右表(B)只会显示符合搜索条件的记录(例子中为:A.aID=B.bID).
B表记录不足的地方均为NULL.
2.right join(右联接)
SELECT*FROM a
RIGHT JOING b
ON a.aID=b.bID
结果如下:
aID aNum bID bName
1a2005011112006032401
2a2005011222006032402
3a2005011332006032403
4a2005011442006032404
NULL NULL82006032408
（所影响的行数为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
1a2005011112006032401
2a2005011222006032402
3a2005011332006032403
4a2005011442006032404
结果说明:
很明显,这里只显示出了 A.aID=B.bID的记录.这说明innerjoin并不以谁为基础,它只显示符合条件的记录.
LEFT JOIN操作用于在任何的FROM子句中，
组合来源表的记录。使用LEFT JOIN运算来创建一个左边外部联接。左边外部联接将包
含了从第一个（左边）开始的两个表中的全部记录，即
使在第二个（右边）表中并没有相符值的记录。
语法：FROM table1LEFT JOIN table2ON table1.field1compoprtable2.field2
说明：table1,table2参数用于指定要将记录组合的表的名称。
field1,field2参数指定被联接的字段的名称。且这些字段必须有相同的数据类型及包含相同类型的数据，但它们不需要有相同的名称。
compopr参数指定关系比较运算符：”=”，“<”，“>”，“<=”，“>=”或“<>”。如果在INNER JOIN操作中要联接包含Memo 数据类型或OLE Object数据类型数据的字段，将会发生错误。
MySQL中的各种JOIN
1.笛卡尔积(交叉连接)
在MySQL中可以为CROSS JOIN或者省略CROSS即JOIN，或者使用','
如
SELECT*FROM table1CROSS JOIN table2 SELECT*FROM table1JOIN table2 SELECT*FROM table1,table2
由于其返回的结果为被连接的两个数据表的乘积，因此当有WHERE,ON或USING条件的时候一般不建议使用，因为当数据表项目太多的时候，会非常慢。
一般使用LEFT[OUTER]JOIN或者RIGHT [OUTER]JOIN
2.内连接INNER JOIN
在MySQL中把INNER JOIN叫做等值连接，即需要指定等值连接条件
在MySQL中CROSS和INNER JOIN被划分在一起，不明白。
参看MySQL帮助手册
https://www.doczj.com/doc/1613445432.html,/doc/refman/5.0/e n/join.html
join_table:
table_reference[INNER|CROSS] JOIN table_factor[join_condition]
3.MySQL中的外连接，分为左外连接和右连接，
即除了返回符合连接条件的结果之外，还要返回左表(左连接)或者右表(右连接)中不符合连接条件的结果，相对应的使用NULL 对应。
a.LEFT[OUTER]JOIN
SELECT column_name FROM table1LEFT [OUTER]JOIN table2
ONtable1.column=table2.column
除了返回符合连接条件的结果之外，还需要显示左表中不符合连接条件的数据列，相对应使用NULL对应
b.RIGHT[OUTER]JOIN
SELECT column_name FROM table1RIGHT [OUTER]JOIN table2
ONtable1.column=table2.column
RIGHT与LEFTJOIN相似不同的仅仅是除了
显示符合连接条件的结果之外，还需要显示右表中不符合连接条件的数据列，相应使用NULL对应
--------------------------------------------
添加显示条件WHERE,ON,USING
1.WHERE子句
2.ON
https://www.doczj.com/doc/1613445432.html,ING子句，如果连接的两个表连接条件的两个列具有相同的名字的话可以使用USING
例如
SELECT FROM LEFT JOIN USING()
连接多余两个表的情况
举例：
mysql>
SELECT artists.Artist,cds.title, genres.genre
->FROM cds
->LEFT JOIN genres
->ON cds.genreID=genres.genreID
->LEFT JOIN artists
->ON cds.artistID=
artists.artistID;
或者
mysql>SELECT artists.Artist,
cds.title,genres.genre
->FROM cds
->LEFT JOIN genres
->ON cds.genreID=genres.genreID
->LEFT JOIN artists
->ON cds.artistID=
artists.artistID
->WHERE(genres.genre='Pop'); --------------------------------------------
另外需要注意的地方
在MySQL中涉及到多表查询的时候，需要根据查询的情况，想好使用哪种连接方式效率更高。
1.交叉连接(笛卡尔积)或者内连接
[INNER|CROSS]JOIN
2.左外连接LEFT[OUTER]JOIN或者右外连接RIGHT[OUTER]JOIN
注意指定连接条件WHERE,ON，USING.
用自然连接，在选择列表中删除authors和publishers表中重复列(city和state)：
SELECT a.*?p.pub_id?p.pub_name?p.country
FROM authors AS a INNER JOIN publishers AS p ON a.city=p.city
----------------------------
三。外连接(OUTER JOIN):
与内连接不同的是，外连接不只列出与连接条件相匹配的行，
而是列出左表(左外连接时)、右表(右外连接时)或两个表(全外连接时)
中所有符合搜索条件的数据行。
外连接是对内连接的扩充,除了将两个数据集合中重叠部分以内的那些数据行连接起来之外,
还可以根据要求返回左侧或右侧数据集合中非匹配的数据或全部的数据.
即左外连接(LEFT OUTER JOIN);
右外连接(RIGHT OUTER JOIN);
全部连接(FULL OUTER JOIN).
外连接返回的结果集中的一些数据看起来和内连接返回的数据完全一样,
但有一些数据也与内连接返回的数据不同,这些数据行的某些部分是来自于表中的数据，
另一部分是NULL值,产生这些NULL值的原因是因为另一表中不包含与指定表相匹配的数据项
内连接时，返回查询结果集合中的仅是符合查询条件(WHERE搜索条件或HAVING条件)和
连接条件的行。而采用外连接时，它返回到查询结果集合中的不仅包含符合连接条件的行，
而且还包括左表(左外连接时)、右表(右外连接时)或两个边接表(全外连接)中的所有数据行。
注：外连接强以使不满足条件的记录也出现在结果集中。
外连接语法：
Select字段名列表from表名Left|Right|Full[Outer]join表名ON连接条件
外连接分为:
1。左外连接(LEFT OUTER JOIN或LEFT JOIN)：
在结果表中包含第一个表中满足条件的所有记录。
如果是在连接条件上匹配的记录，则第二个表返回相应值，否则第二个表返回空值。
select学生表.学号,学生表.姓名,成绩表.课程代号,成绩表.课程成绩
from学生表left outer join成绩表
on学生表.学号=成绩表.学号
2。右外连接(RIGHT OUTER JOIN或RIGHT JOIN)：
在结果表中包含第二个表中满足条件的所有记录。
如果是在连接条件上匹配的记录，则第一个表返回相应值，否则第一个表返回空值。
select学生表.学号,学生表.姓名,成绩表.课程代号,成绩表.课程成绩
from学生表right outer join成绩表
on学生表.学号=成绩表.学号
3。全外连接(FULL OUTER JOIN或FULL JOIN):
在结果表中包含两个表中满足条件的所有记录。
如果是在连接条件上匹配的元组，则另一个表返回相应值，否则另一个表返回空值。
select学生表.学号,学生表.姓名,成绩表.课程代号,成绩表.课程成绩
from学生表full outer join成绩表
on学生表.学号=成绩表.学号
例子：
SELECT a.*,b.*FROM student as a left JOIN sclass as b
ON a.sno=b.sno and a.sno=''9502101''
go
SELECT a.*,b.*FROM student as a right JOIN sclass as b
ON a.sno=b.sno and a.sno=''9502101''
go
SELECT a.*,b.*FROM student as a full JOIN sclass as b
ON a.sno=b.sno
go
----------------------------
四。交叉连接:
交叉连接不使用任何连接条件来限制结果集合,将各表的记录以“笛卡尔”积的方式组合起来,
是分别使用两个数据源中的行以所有可能的方式进行组合,
即数据集中的每一行都要与另一表每一行组成一个新的行.
例如:一表中有三条记录,另一表有4条记录,交叉连接后,结果集合将由12条记录组成.
交叉连接(CROSS JOIN)没有WHERE子句，它返回被连接的两个表所有数据行的笛卡尔积，
返回到结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中
符合查询条件的数据行数。
例，
titles表中有6类图书，而publishers表中有8家出版社，
则下列交叉连接检索到的记录数将等于6*8=48行。
select*from student,sclass
SELECT*FROM student a CROSS JOIN sclass ORDER BY a.sno
----------------------------
五。联合查询
UNION运算符可以将两个或两个以上上SELECT语句的查询结果集合合并成一个结果集合显示，
即执行联合查询。
UNION的语法格式为：
select_statement
UNION[ALL]selectstatement
[UNION[ALL]selectstatement][…n]
其中selectstatement为待联合的SELECT查询语句。
ALL选项表示将所有行合并到结果集合中。不指定该项时，
被联合查询结果集合中的重复行将只保留一行。
联合查询时，查询结果的列标题为第一个查询语句的列标题。
因此，要定义列标题必须在第一个查询语句中定义。要对联合查询结果排序时，也必须使用第一查询语句中的列名、列标题或者列序号。
在使用UNION运算符时，应保证每个联合查询语句的选择列表中有相同数量的表达式，
并且每个查询选择表达式应具有相同的数据类型，或是可以自动将它们转换为相同的数据类型
。
在自动转换时，对于数值类型，系统将低精度的数据类型转换为高精度的数据类型。
在包括多个查询的UNION语句中，其执行顺序是自左至右，使用括号可以改变这一执行顺序。
例如：
查询1UNION(查询2UNION查询3)
select topicbody,posttime from bbs_topic
union all
select replybody,posttime from bbs_reply
----------------------------
六。复杂查询
---复合连接
select a.学号,a.姓名,b.课程代号,b.课程成绩,c.课程名称,d.教师代号
from学生表a,成绩表b,课程表c,教学表d
where(a.学号=b.学号)
and(b.课程代号=c.课程代号)
and(c.课程代号=d.课程代号)
三个表以上的连接
SELECT dbo.kb.xq,dbo.kbk.kcmc,dbo.kbk.lbdh,dbo.kbk.jsmc, dbo.kb.jse,dbo.bj.bj,
dbo.kb.jc,2AS num,dbo.kb.zc,
CASE dbo.kb.ds WHEN''单''THEN''1''WHEN''双''THEN''2'' WHEN''''THEN''0''END AS ds,
dbo.kb.zc1,dbo.kb.zc2
FROM dbo.kb INNER JOIN
dbo.bj ON dbo.kb.bh=dbo.bj.bh INNER JOIN
dbo.kbk ON dbo.kb.xq=dbo.kbk.xq AND dbo.kb.bh=
dbo.kbk.bh AND
dbo.kb.kcdm=dbo.kbk.kcdm
WHERE(dbo.kb.jc=1)OR
(dbo.kb.jc=3)OR
(dbo.kb.jc=5)OR
(dbo.kb.jc=7)OR
(dbo.kb.jc=9)OR
(dbo.kb.jc=11)
----------------------------
总结：
无论哪种连接都不能对text、ntext和image数据类型列进行直接连接，但可以对这三种列进行间接连接。
例如：
SELECT p1.pub_id?p2.pub_id?p1.pr_info
FROM pub_info AS p1INNER JOIN pub_info AS p2
ON DATALENGTH(p1.pr_info)=DATALENGTH(p2.pr_info)