数据库 左连接 右连接 全连接用法小结

作者:无名 - 数据库其它 -
连接可以在SELECT 语句的FROM子句或WHERE子句中建立,似是而非在FROM子句中指出连接时有助于将连接操作与WHERE子句中的搜索条件区分开来。所以,在Transact-SQL中推荐使用这种方法。 

SQL-92标准所定义的FROM子句的连接语法格式为: 

FROM join_table join_type join_table 
[ON (join_condition)] 

其中join_table指出参与连接操作的表名,连接可以对同一个表操作,也可以对多表操作,对同一个表操作的连接又称做自连接。join_type 指出连接类型,可分为三种:内连接、外连接和交叉连接。内连接(INNER JOIN)使用比较运算符进行表间某(些)列数据的比较操作,并列出这些表中与连接条件相匹配的数据行。根据所使用的比较方式不同,内连接又分为等值连接、自然连接和不等连接三种。 

外连接分为左外连接(LEFT OUTER JOIN或LEFT JOIN)、右外连接(RIGHT OUTER JOIN或RIGHT JOIN)和全外连接(FULL OUTER JOIN或FULL JOIN)三种。与内连接不同的是,外连接不只列出与连接条件相匹配的行,而是列出左表(左外连接时)、右表(右外连接时)或两个表(全外连接时)中所有符合搜索条件的数据行。 

交叉连接(CROSS JOIN)没有WHERE 子句,它返回连接表中所有数据行的笛卡尔积,其结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。 

连接操作中的ON (join_condition) 子句指出连接条件,它由被连接表中的列和比较运算符、逻辑运算符等构成。 

无论哪种连接都不能对text、ntext和image数据类型列进行直接连接,但可以对这三种列进行间接连接。例如: 

SELECT p1.pub_id,p2.pub_id,p1.pr_info 
FROM pub_info AS p1 INNER JOIN pub_info AS p2 
ON DATALENGTH(p1.pr_info)=DATALENGTH(p2.pr_info) 

(一)内连接 

内连接查询操作列出与连接条件匹配的数据行,它使用比较运算符比较被连接列的列值。内连接分三种: 

1、等值连接:在连接条件中使用等于号(=)运算符比较被连接列的列值,其查询结果中列出被连接表中的所有列,包括其中的重复列。 

2、不等连接: 在连接条件使用除等于运算符以外的其它比较运算符比较被连接的列的列值。这些运算符包括>、>=、<=、<、!>、!<和<>。 

3、自然连接:在连接条件中使用等于(=)运算符比较被连接列的列值,但它使用选择列表指出查询结果集合中所包括的列,并删除连接表中的重复列。 

例,下面使用等值连接列出authors和publishers表中位于同一城市的作者和出版社: 

SELECT * 
FROM authors AS a INNER JOIN publishers AS p 
ON a.city=p.city 

又如使用自然连接,在选择列表中删除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 

(二)外连接 

内连接时,返回查询结果集合中的仅是符合查询条件( WHERE 搜索条件或 HAVING 条件)和连接条件的行。而采用外连接时,它返回到查询结果集合中的不仅包含符合连接条件的行,而且还包括左表(左外连接时)、右表(右外连接时)或两个边接表(全外连接)中的所有数据行。 

如下面使用左外连接将论坛内容和作者信息连接起来: 

SELECT a.*,b.* FROM luntan LEFT JOIN usertable as b 
ON a.username=b.username 

下面使用全外连接将city表中的所有作者以及user表中的所有作者,以及他们所在的城市: 

SELECT a.*,b.* 
FROM city as a FULL OUTER JOIN user as b 
ON a.username=b.username 

(三)交叉连接 

交叉连接不带WHERE 子句,它返回被连接的两个表所有数据行的笛卡尔积,返回到结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。 

例,titles表中有6类图书,而publishers表中有8家出版社,则下列交叉连接检索到的记录数将等于6*8=48行。 

SELECT type,pub_name 
FROM titles CROSS JOIN publishers 
ORDER BY type

介绍了表连接,更确切的说是inner joins內连接. 
內连接仅选出两张表中互相匹配的记录.因此,这会导致有时我们需要的记录没有包含进来。 
为更好的理解这个概念,我们介绍两个表作演示。苏格兰议会中的政党表(party)和议员表(msp)。 

party(Code,Name,Leader) 
Code: 政党代码 
Name: 政党名称 
Leader: 政党领袖 

msp(Name,Party,Constituency) 
Name: 议员名 
Party: 议员所在政党代码 
Constituency: 选区 

在介绍左连接、右连接和全连接前,有一个数据库中重要的概念要介绍一下,即空值(NULL)。 

有时表中,更确切的说是某些字段值,可能会出现空值, 这是因为这个数据不知道是什么值或根本就不存在。 
空值不等同于字符串中的空格,也不是数字类型的0。因此,判断某个字段值是否为空值时不能使用=,<>这些 
判断符。必需有专用的短语:IS NULL 来选出有空值字段的记录,同理,可用 IS NOT NULL 选出不包含空值的记录。 

例如:下面的语句选出了没有领导者的政党。(不要奇怪,苏格兰议会中确实存在这样的政党) 

SELECT code, name FROM party 
WHERE leader IS NULL 

又如:一个议员被开除出党,看看他是谁。(即该议员的政党为空值) 

SELECT name FROM msp 
WHERE party IS NULL 

好了,让我们言归正传,看看什么叫左连接、右连接和全连接。 

A left join(左连接)包含所有的左边表中的记录甚至是右边表中没有和它匹配的记录。 
同理,也存在着相同道理的 right join(右连接),即包含所有的右边表中的记录甚至是左边表中没有和它匹配的记录。 
而full join(全连接)顾名思义,左右表中所有记录都会选出来。 

讲到这里,有人可能要问,到底什么叫:包含所有的左边表中的记录甚至是右边表中没有和它匹配的记录。 
Ok,我们来看一个实例: 

SELECT msp.name, party.name 
FROM msp JOIN party ON party=code 

这个是我们上一节所学的Join(注意:也叫inner join),这个语句的本意是列出所有议员的名字和他所属政党。 
你可以在 http://sqlzoo.cn/4.htm 亲自执行一下该语句,看看结果是什么。 

很遗憾,我们发现该查询的结果少了两个议员:Canavan MSP, Dennis。为什么,因为这两个议员不属于任 
和政党,即他们的政党字段(Party)为空值。那么为什么不属于任何政党就查不出来了?这是因为空值在 
作怪。因为议员表中政党字段(Party)的空值在政党表中找不到对应的记录作匹配,即 
FROM msp JOIN party ON party=code 没有把该记录连接起来,而是过滤出去了。 
在该短语中,msp在Join的左边,所有称为左表。party在Join的右边,所有称为右表。 

Ok,现在再看看这句话,“包含所有的左边表中的记录甚至是右边表中没有和它匹配的记录”, 
意思应该很明白了吧。执行下面这个语句,那两个没有政党的议员就漏不了了。 

SELECT msp.name, party.name 
FROM msp LEFT JOIN party ON party=code 

关于右连接,看看这个查询就明白了: 

SELECT msp.name, party.name 
FROM msp RIGHT JOIN party ON msp.party=party.code 

这个查询的结果列出所有的议员和政党,包含没有议员的政党,但不包含没有政党的议员。 

那么既要包含没有议员的政党,又要包含没有政党的议员该怎么办呢,对了,全连接(full join)。 

SELECT msp.name, party.name 
FROM msp FULL JOIN party ON msp.party=party.code
当检索数据时,通过连接操作查询出存放在多个表中的不同实体的信息。连接操作给用户带来很大的灵活性,他们可以在任何时候增加新的数据类型。为不同实体创建新的表,尔后通过连接进行查询。

IT人知识库 该篇知识地址:http://www.itpeo.net/12848/392699.html





本地SQL注射攻略分析曝光

何为本地注射?简单地讲,就是本来你在服务器上无法进行SQL注入,因为服务器上的WEB程序很安全,对request("id... ...

mssql注入躲避IDS的方法

1.关于openrowset和opendatasource    可能这个技巧早有人已经会了,就是利用open... ...

SQLite不支持Right Join的解决办法GROUP BY

我想用这个,很不幸SQLite没有。。 http://www.sqlite.org/omitted.html 被omit... ...

SQL服务器面临的危险和补救.读[十种方法]后感.

危险:没有进行安全漏洞扫描. 后果:操作系统和各网络程序本身的漏洞被黑客发现,连累数据库被攻破. 补救:永远保持最新的安... ...

数据库删除完全重复和部分关键字段重复的记录

1、第一种重复很容易解决,不同数据库环境下方法相似:  以下为引用的内容: Mysql  creat... ...

Access转换成SQL Server需要注意事项整理

一,首先,我说的是在ACCESS2000,SQL2000之间转换,其他的我也还没有尝试过,希望大家多多试验,肯定是有办法... ...

sql server中datetime字段去除时间代码收藏

select CONVERT(varchar, getdate(), 120 )... ...

SQL SERVER的字段类型说明

SQL SERVER提供的说明。  bit:0或1的整型数字  int:从-2^31(-2... ...

比较实用SQL语句总结

id name  1 a  2 b  3 c&nb... ...

MSSQL内连接inner join查询方法

今天项目遇到一直不知道怎么解决,在经典网友以及无忧网友的热心帮助下,解决。 比如A表: sid  &... ...

以前架征途时的合区的SQL语句代码备份

su - mysql -c “mysqld_safe –skip-na... ...

sql语句中where 1=1的作用

在看程序时时,发现基本只要是拼凑的SQL语句,基本上都会这样写: where 1 =&nb... ...

推荐Sql server一些常见性能问题的解决方法

1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。 2.应尽量... ...

SQL语句详细说明[部分]

SQL 语句教程 (11) Group By 我们现在回到函数上。记得我们用SUM这个指令来算出所有的 Sales (... ...

三表左连接查询的sql语句写法

今天要查询所有realname的username,psw,gname,tel 表结构: 表t1 字段名:t1_id,us... ...

MSSQL自动同步设置方法

MSSQL自动同步 这里以117和75为例说明:117发布&分发服务器、75订阅服务器 一、准备工作: 1、117&75... ...

SQL嵌套查询总结

IT也有一段时间了,刚开始的时候`````` 的困难主要是在编程语言上,数组,逻辑,算法,... 这些都过来了之后,困难... ...

rfedfre

sql2005可实时监测数据库版本控制SQL的触发器

代码如下:/****** 对象: DdlTrigger [Trig_DBVersionController] 脚本日期... ...

sql 中将日期中分秒化为零的语句

代码如下:select cast(convert(nvarchar(10),getdate(),112) as dat... ...

SQL 注入式攻击的本质

更没想到的是这么老掉牙的东西居然还能跑出来搅风搅雨,而且造成了如此大的破坏,有文章还说注入式攻击还会有“第三波”攻击潮,... ...