SQL 多表查询(内连接、外连接和交叉连接,)

一、连接查询简介

　　连接查询中用来连接连个表的条件称为连接条件或连接谓词。其形式为：

[<表1>].<列名1><连接运算符>[<表2>].<列2>　　

　　常见的连接运算符包括

　　1、比较运算符：=、>、<、>=、<=、!=、between和and。

　　2、逻辑运算符:not、and、or。

　　3、使用between和and连接查询形式为[<表1>].<列名1>[<表2>].<列名2>and[<表2>].<列名3>。

二、连接按照结果集分类

　　1、内连接：表中的行互相连接。结果集的行数等于每个表满足条件的行数的乘积，参与连接的表是平等的。

　　2、外连接：参与连接的表有主次之分，主表的每一行数据去匹配从表的数据列，符合连接条件的数据将直接返回到结果集中，不符合连接条件的数据列将以null填充后返回到结果集中，其中外连接又分左外连接、右外连接和全连接3种。

　　(一)、等值连接查询

select p.*,c.* from country as c,person as p where c.countryid = p.countryid

　　等上面的等值连接中，两张表都有countryid字段，因此查出来的结果中就会有两列countryid

　　(二)、在等值结果中消除数据就是自然连接

select p.name,c.countryname from country as c,person as p where c.countryid = p.countryid

　　(三)、自身连接

　　一个数据表自己与自己建立连接称为自身连接

三、内连接查询

　　1、内连接查询的语法结构如下：

　　　　　　select <属性或表达式列表>

　　　　　　from <表名> [inner] join <表名>

　　　　　　on <连接条件>

　　　　　　[ where <限定条件> ]

　　inner可以省略，当只见到join时就是省略了inner。内连接就是传统的连接操作，这里用on子句指定连接条件，用where子句指定其他限定条件：

　　select p.name,c.countryname from country as c inner join person p on p.countryid = c.countryid

四、左外连接查询

　　1、左外连接查询的语法结构：

　　　　select <属性或表达式列表>

　　　　from <表名> left outer join <表名>

　　　　on <连接条件>

　　　　[ where <限定条件> ]

　　如：

　　select p.name,c.countryname from country as c left join person p on p.countryid = c.countryid　

　　在结果表中包含第一个表中满足条件的所有记录，如果是在连接连接上匹配的记录，则第二个表返回相应值，否则第二个表返回null。也就是说，不管第二个表有没有记录都会第一个表的所有字段都会返回，这就是外连接与内连接的区别。

五、右外连接查询

　　1、右外连接查询的语法结构如下：

　　　　select <属性或表达式列表>

　　　　from <表名> right outer join <表名>

　　　　on　<连接条件>

　　　　[ where <限定条件> ]

　　如：

select p.name,c.countryname from country as c right join person p on p.countryid = c.countryid

　　在结果表中包含第二个表中满足条件的所有记录。如果是在连接条件上匹配的记录，则第一个表返回相应值，否则第一个表返回null。

六、全外连接查询

　　1、全外连接查询的语法结构如下:

　　　select <属性或表达式列表>

　　　from <表名> full outer join <表名>

　　　on <连接条件>

　　　where <限定条件>]

　　如：

　　select p.name,c.countryname from country as c full join person p on p.countryid = c.countryid

　　在结果表中包含两个表中满足条件的所有记录。如果是在连接条件上匹配的元组，则另一个表返回相应则，没有则返回null。

七、交叉连接

　　交叉连接Corss join，用于将第一张表的所有记录与第二张表的所有记录组合一次并返回，这个东西在生成测试数据库时很有用，例如，你定义7个姓，7个名，再交叉连接就能够产生49条记录。

　　　如有如下表：姓氏表

　　　　名字表：

　　　　执行如下SQL语句：

　　select LastName + firstname from name2 cross join name1

　　　　　　 结果如下：

8、联合查询union(union all)

　　　union是一个特殊的运算符，用于将两个或两个以上的查询产生一个结果集。join将信息水平连接(添加更多列)，而union将信息垂直连接(添加更多行)。

　　　当使用union处理查询时，要注意以下几个关键点。

　　　(1)、所有union的查询必须在select列表中有相同的列数。即如果第一个查询有3个列数，第二个查询也要只有3个列数。

　　　(2)、union返回结果的标题集仅从第一个查询中获得，无论第二个查询如何命名或取别名都不会更改。

　　　(3)、查询中对应的列的数据类型必须隐式一致。注意不要求完全一致，只需要隐式一致。

　　　(4)、与其他非union不同，union的默认返回选项为distinct，而不是all。union all语句与union的不同点仅仅在于遇到相同的记录，全部保留而已。

　　　例如还是用第7条的例子，执行如下语句：

select * from name1 unionselect * from name2

　　　返回结果为：

　　　由于union默认是distinct查询，因此想要获得所有的记录时，可以用union all，这样就算上述例子中两个表都有'王'，也会两条记录一起返回。

　　　现在来看一下综合示例：

　　　为了展示一下，建了两张表，并添加了几条记录，如下：

　　　　person表

　　　　country表

　　　1、查询一个列表，该列表要包含国家名称列表与该国家下的person总数。

select c.countryname,count(p.id) from country as c inner join person as p on c.countryid = p.countryid group by c.countryname

　　　　输出结果：

　　　　现在在来加一个条件，要求人口数按升序排列：

select c.countryname,count(p.id) as cofrom country as c inner join person as p on c.countryid = p.countryid group by c.countrynameorder by co asc

　　　　输出结果：

　　　　再加一个条件，要求只输出person数大于2的记录:

select c.countryname,count(p.id) as cofrom country as c inner join person as p on c.countryid = p.countryid group by c.countrynamehaving count(p.id) > 2order by co asc

　　　　输出结果为：

sql语句中出现笛卡尔乘积 SQL查询入门篇  

2013-05-29 16:38:36|  分类： 默认分类 |  标签：sql  笛卡尔乘积  |举报|字号 订阅

本篇文章中，主要说明SQL中的各种连接以及使用范围，以及更进一步的解释关系代数法和关系演算法对在同一条查询的不同思路。

多表连接简介

在关系数据库中，一个查询往往会涉及多个表，因为很少有数据库只有一个表，而如果大多查询只涉及到一个表的，那么那个表也往往低于第三范式，存在大量冗余和异常。

因此，连接(Join)就是一种把多个表连接成一个表的重要手段.

比如简单两个表连接学生表(Student)和班级(Class)表，如图:

进行连接后如图：

笛卡尔积

笛卡尔积在SQL中的实现方式既是交叉连接(Cross Join)。所有连接方式都会先生成临时笛卡尔积表，笛卡尔积是关系代数里的一个概念，表示两个表中的每一行数据任意组合,上图中两个表连接即为笛卡尔积(交叉连接)

在实际应用中，笛卡尔积本身大多没有什么实际用处，只有在两个表连接时加上条件，才会有实际意义，下面看内连接

SQL 多表查询（内连接、外连接和交叉连接，）

俺是菜鸟在这里写博就是被了跟大家交流，如果那里不对的地方请指出，小弟加以改正！

多表查询分为 内、外连接

外连接分为左连接（left join 或left outer join）、右连接（right join 或者 right outer join）、和完整外部连接 （full join 或者 full outer join）

 左连接（left join 或 left outer join）的结果就是left join子句中的左表的所有行，而不仅仅是链接列所匹配的行，如果左表中的某行在右表中没有匹配，则在相关联的结果行中右表的所有选择列均为空值（NULL）

SQL语法　select * from  table1 left join table2 on table1.条件列名 = table2.条件列名；

注释：  显示的就是table1中的所有列和能匹配的列

右连接（right join 或 right outer join ）在这里不做多说这左连接很象但是是相反的，只说一下语法

   select *from table1 right join table2 on table1. 条件列= table2.条件列

完全外部连接（full join 或 full outer join）

显示左右表中的所有行，当某一个表中没有匹配的行时，则另一个表的选择列表列包含空值（NULL）如果有则显示全部数据

SQL语法：

select *from table1 full join table2 on  table1.条件列名= table2.条件列名

内连接：

概念：内连接就是用比较运算符比较要用连接列的值的连接

内连接（join 或者inner join ）

SQL语法：

select *fron table1 join table2  on table1.条件列名 = table2.条件列名

返回符合匹配条件的两表列

等价于：

           select A* ,B* from table1 A ,table2 B where A.条件列名 =B.条件列名

           select *form table1 cross join table2 where table1.条件列名 = table2.条件列名（注： Cross join 后面不能跟on 只能用where）

  交叉连接（完全）

概念：没有用where子句的交叉连接将产生连接所涉及的笛卡尔积第一个表的行数乘以第二个表的行数等于笛卡尔积和结果集的大小

交叉连接： Cross join（不带条件where，如果带返回或显示的是匹配的行数）

SQL语法：

select *from  table1 cross join table2

如果有条件（where）

select * from table1  cross join table2 where table1. 条件列名= table2.条件列名

等价于

select *from table1,table2 (不带where)