深入理解SQL的四种连接-左外连接、右外连接、内连接、全连接
1、内联接(典型的联接运算,使用像= 或<>之类的比较运算符)。包括相等联接和自然联接。
内联接使用比较运算符根据每个表共有的列的值匹配两个表中的行。例如,检索students和courses表中学生标识号相同的所有行。
2、外联接。外联接可以是左向外联接、右向外联接或完整外部联接。
在FROM子句中指定外联接时,可以由下列几组关键字中的一组指定:
左向外联接的结果集包括 LEFTOUTER子句中指定的左表的所有行,而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行,则在相关联的结果集行中右表的所有选择列表列均为空值。
右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行,则将为左表返回空值。
完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时,则另一个表的选择列表列包含空值。如果表之间有匹配行,则整个结果集行包含基表的数据值。
3、交叉联接
交叉联接返回左表中的所有行,左表中的每一行与右表中的所有行组合。交叉联接也称作笛卡尔积。
FROM子句中的表或视图可通过内联接或完整外部联接按任意顺序指定;但是,用左或右向外联接指定表或视图时,表或视图的顺序很重要。有关使用左或右向外联接排列表的更多信息,请参见使用外联接。
例子:
-------------------------------------------------
a表 id name b表 id job parent_id
1 张3 1 23 1
2 李四 2 34 2
3 王武 3 34 4
a.id同parent_id 存在关系
--------------------------------------------------
select a.*,b.* from a inner join b on a.id=b.parent_id
结果是
1 张3 1 23 1
2 李四 2 34 2
select a.*,b.* from a left join b on a.id=b.parent_id
结果是
1 张3 1 23 1
2 李四 2 34 2
3 王武 null
select a.*,b.* from a right join b on a.id=b.parent_id
结果是
1 张3 1 23 1
2 李四 2 34 2
null 3 34 4
select a.*,b.* from a full join b on a.id=b.parent_id
结果是
1 张3 1 23 1
2 李四 2 34 2
null 3 34 4
3 王武 null
--------------------------------------------------------------------------------------------
例如:下面的语句1和语句2的结果是相同的。
语句1:隐式的交叉连接,没有CROSSJOIN。
SELECTO.ID,O.ORDER_NUMBER,C.ID,C.NAME
FROMORDERSO,CUSTOMERSC
WHEREO.ID=1;
语句2:显式的交叉连接,使用CROSSJOIN。
SELECTO.ID,O.ORDER_NUMBER,C.ID,
C.NAME
FROMORDERSOCROSSJOINCUSTOMERSC
WHEREO.ID=1;
语句1和语句2的结果是相同的,查询结果如下:
例如:下面的语句3和语句4的结果是相同的。
语句3:隐式的内连接,没有INNERJOIN,形成的中间表为两个表的笛卡尔积。
SELECTO.ID,O.ORDER_NUMBER,C.ID,C.NAME
FROMCUSTOMERSC,ORDERSO
WHEREC.ID=O.CUSTOMER_ID;
语句4:显示的内连接,一般称为内连接,有INNERJOIN,形成的中间表为两个表经过ON条件过滤后的笛卡尔积。
SELECTO.ID,O.ORDER_NUMBER,C.ID,C.NAME
FROMCUSTOMERSCINNERJOINORDERSOONC.ID=O.CUSTOMER_ID;
语句3和语句4的查询结果:
三者的共同点是都返回符合连接条件和查询条件(即:内连接)的数据行。不同点如下:
左外连接还返回左表中不符合连接条件单符合查询条件的数据行。
右外连接还返回右表中不符合连接条件单符合查询条件的数据行。
全外连接还返回左表中不符合连接条件单符合查询条件的数据行,并且还返回右表中不符合连接条件单符合查询条件的数据行。全外连接实际是上左外连接和右外连接的数学合集(去掉重复),即“全外=左外UNION右外”。
说明:左表就是在“(LEFTOUTERJOIN)”关键字左边的表。右表当然就是右边的了。在三种类型的外连接中,OUTER关键字是可省略的。
下面举例说明:
语句5:左外连接(LEFTOUTERJOIN)
SELECTO.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME
FROMORDERSOLEFTOUTERJOINCUSTOMERSCONC.ID=O.CUSTOMER_ID;
语句6:右外连接(RIGHTOUTERJOIN)
SELECTO.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME
FROMORDERSORIGHTOUTERJOINCUSTOMERSCONC.ID=O.CUSTOMER_ID;
注意:WHERE条件放在ON后面查询的结果是不一样的。例如:
语句7:WHERE条件独立。
SELECTO.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME
FROMORDERSOLEFTOUTERJOINCUSTOMERSCONC.ID=O.CUSTOMER_ID
WHEREO.ORDER_NUMBER<>"MIKE_ORDER001";
语句8:将语句7中的WHERE条件放到ON后面。
SELECTO.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME
FROMORDERSOLEFTOUTERJOINCUSTOMERSCONC.ID=O.CUSTOMER_IDANDO.ORDER_NUMBER<>"MIKE_ORDER001";
从语句7和语句8查询的结果来看,显然是不相同的,语句8显示的结果是难以理解的。因此,推荐在写连接查询的时候,ON后面只跟连接条件,而对中间表限制的条件都写到WHERE子句中。
语句9:全外连接(FULLOUTERJOIN)。
SELECTO.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME
FROMORDERSOFULLOUTERJOINCUSTOMERSCONC.ID=O.CUSTOMER_ID;
注意:MySQL是不支持全外的连接的,这里给出的写法适合Oracle和DB2。但是可以通过左外和右外求合集来获取全外连接的查询结果。下图是上面SQL在Oracle下执行的结果:
语句10:左外和右外的合集,实际上查询结果和语句9是相同的。
SELECTO.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME
FROMORDERSOLEFTOUTERJOINCUSTOMERSCONC.ID=O.CUSTOMER_ID
UNION
SELECTO.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME
FROMORDERSORIGHTOUTERJOINCUSTOMERSCONC.ID=O.CUSTOMER_ID;
语句9和语句10的查询结果是相同的,如下:
语句11:联合查询(UNIONJOIN)例句,还没有找到能执行的SQL环境。
SELECTO.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME
FROMORDERSOUNIONJOINCUSTOMERSCONC.ID=O.CUSTOMER_ID
语句12:语句11在DB2下的等价实现。还不知道DB2是否支持语句11呢!
SELECTO.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME
FROMORDERSOFULLOUTERJOINCUSTOMERSCONC.ID=O.CUSTOMER_ID
EXCEPT
SELECTO.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME
FROMORDERSOINNERJOINCUSTOMERSCONC.ID=O.CUSTOMER_ID;
语句13:语句11在Oracle下的等价实现。
SELECTO.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME
FROMORDERSOFULLOUTERJOINCUSTOMERSCONC.ID=O.CUSTOMER_ID
MINUS
SELECTO.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME
FROMORDERSOINNERJOINCUSTOMERSCONC.ID=O.CUSTOMER_ID;
查询结果如下:
语句14:
SELECT*
FROMORDERSONATURALINNERJOINCUSTOMERSC;
语句15:
SELECT*
FROMORDERSONATURALLEFTOUTERJOINCUSTOMERSC;
语句16:
SELECT*
FROMORDERSONATURALRIGHTOUTERJOINCUSTOMERSC;
语句17:
SELECT*
FROMORDERSONATURALFULLOUTERJOINCUSTOMERSC;
第一、
第二、两表连接查询:对两表求积(笛卡尔积)并用ON条件和连接连接类型进行过滤形成中间表;然后根据WHERE条件过滤中间表的记录,并根据SELECT指定的列返回查询结果。
第三、多表连接查询:先对第一个和第二个表按照两表连接做查询,然后用查询结果和第三个表做连接查询,以此类推,直到所有的表都连接上为止,最终形成一个中间的结果表,然后根据WHERE条件过滤中间表的记录,并根据SELECT指定的列返回查询结果。
理解SQL查询的过程是进行SQL优化的理论依据。
WHERE条件:在有ON条件的SELECT语句中是过滤中间表的约束条件。在没有ON的单表查询中,是限制物理表或者中间查询结果返回记录的约束。在两表或多表连接中是限制连接形成最终中间表的返回结果的约束。
从这里可以看出,将WHERE条件移入ON后面是不恰当的。推荐的做法是:
ON只进行连接操作,WHERE只过滤中间表的记录。
2、Col_L是Col_R的子集时用右外连接。
3、Col_R是Col_L的子集时用左外连接。
4、Col_R和Col_L彼此有交集但彼此互不为子集时候用全外。
5、求差操作的时候用联合查询。
多个表查询的时候,这些不同的连接类型可以写到一块。例如:
SELECTT1.C1,T2.CX,T3.CY
FROMTAB1T1
INNERJOINTAB2T2ON(T1.C1=T2.C2)
INNERJOINTAB3T3ON(T1.C1=T2.C3)
LEFTOUTERJOINTAB4ON(T2.C2=T3.C3);
WHERET1.X>T3.Y;
上面这个SQL查询是多表连接的一个示范。
相关文章
- SQL开发知识:Sql中存储过程的定义、修改和删除操作
- SQLServer 错误 233 已成功与服务器建立连接,但是在登录过程中发生错误。 (提供程序:共享内存提供程序,错误: 0 – 在管道的另一端没有进程。) (Microsoft SQL Server,错误: 233) 故障 处理 修复 支持远程
- Oracle 等待事件 SQL*Net message from client 官方解释,作用,如何使用及优化方法
- Java Statement.execute()方法:执行SQL语句
- Exploring the Power of Oracle SQL(oracle.sql)
- sql登場:顶尖 Oracle SQL(toporacle)
- 深入解析:Mysql 复杂SQL优化指南(mysql复杂sql)
- 深入学习PL/SQL 远程连接Oracle数据库(plsql远程连接oracle)
- 手把手教你使用Oracle 进行SQL除法操作(oracle除法sql)
- Oracle数据表的完整备份:使用SQL.(oracle备份表sql)
- serverOracle 与 SQL Server之间的友谊连接(oracle连接sql)
- 换SQL Server链接名称变更:路径改变轨迹(sqlserver链接名)
- 使用SQL Server添加新列:一种尝试(sqlserver追加列)
- 深入了解Oracle SQL管理技巧(oracle管理sql)
- MySQL中如何导入SQL文件(mysql如何导入sql文件)
- MySQL 中 SQL 比较详解常用语句及其实现方法(mysql中sql比较)
- Oracle与SQL连接之路追求数据的完美结合(oracle与sql链接)
- 深入比较Oracle与SQL的异同(oracle与sql比较)
- Oracle SQL不容忽视的不为空判断(oracle不为空sql)
- 优化深入探究Oracle下的SQL优化之道(oracle下sql)
- SQL语句优化方法30例(推荐)
- 常用SQL语句(嵌套子查询/随机等等)详细整理
- 必须会的SQL语句(一)创建数据库与删除数据库