SQL 语句学习二

 

名称	名称	描叙
外连接	左（外）连接	LEFT  JOIN或LEFT  OUTER  JOIN 左向外联接的结果集包括 子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值
	右（外）连接	RIGHT JOIN或RIGHT  OUTER  JOIN 右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值
	全（外）连接	FULL  JOIN  或 FULL  OUTER  JOIN 完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。
内连接		inner join  只返回两个表中条件匹配的行
交叉连接		cross join 交叉联接返回左表中的所有行，左表中的每一行与右表中的所有行组合。交叉联接也称作笛卡尔积。 没有 WHERE 子句的交叉联接将产生联接所涉及的表的笛卡尔积。第一个表的行数乘以第二个表的行数等于笛卡尔积结果集的大小。也就是说在没有 WHERE 子句的情况下，若表 A 有 3 行记录，表 B 有 6 行记录 : SELECT  A.*,B.*  FROM 表A  CROSS  JOIN 表B 那以上语句会返回 18 行记录。

 

 

1.外连接与内连接的差别在于：

仅当至少有一个同属于两表的行符合联接条件时，内联接才返回行。内联接消除与另一个表中的任何行不匹配的行。而外联接会返回    FROM    子句中提到的至少一个表或视图的所有行，只要这些行符合任何    WHERE    或    HAVING    搜索条件。将检索通过左向外联接引用的左表的所有行，以及通过右向外联接引用的右表的所有行。完整外部联接中两个表的所有行都将返回.

 

2.下面的写法功能是一样的

在where 中指定 连接

select count(*) from web_owner_new a ,web_website_new b
where a.owner_id=b.owner_id and a.is_auditing=b.is_auditing
在from 中指定连接
select count(*) from web_owner_new a inner join web_website_new b
on (a.owner_id=b.owner_id and b.is_auditing=a.is_auditing)

 

3. select count(*) from web_owner_new a ,web_website_new b
where a.owner_id=b.owner_id and a.is_auditing=b.is_auditing

 

select count(*) from web_owner_new a ,web_website_new b
where a.owner_id=b.owner_id and a.is_auditing=b.is_auditing having count(b.owner_id)>34

查询结果一样，因为表B 中的符合连接的记录数大于34,所以having count(b.owner_id) 相当于没有起作用。

 

4.将表进行连接后，后面的检索条件作用范围是连接后的大表，这个时候可以在其后面加上检索条件。比如下面两条sql语句的效果是一样的。

select count(*) from web_owner_new a inner join web_website_new b
on (a.owner_id=b.owner_id and b.is_auditing=a.is_auditing and b.website_id>30000)

select count(*) from web_owner_new a inner join web_website_new b
on (a.owner_id=b.owner_id and b.is_auditing=a.is_auditing ) where  b.website_id>30000

 

5. a 表 a.owner_id中可能对应b表中多条记录，所以左连接返回的记录数会大于左边表的记录数。但是可以肯定的是A表中的所有行（确切的说，应该是行值）都有返回，但因为一对多的关系，导致出现如下结果。左连接可以看作是内连接的一个扩展版。左连接首先做的就是内连接，然后将在在A表中不在B表中的余下记录集(B表中的所有字段均为null)union到内连接表上。

select count(*) from web_owner_new

查询结果 55887

select count(*) from web_website_new

查询结果 84883

select count(*) from web_owner_new a left join web_website_new b
on (a.owner_id=b.owner_id )

查询结果  94473 

select count(*) from web_owner_new a inner join web_website_new b
on (a.owner_id=b.owner_id )

查询结果  84850

select count(*) from web_owner_new a   cross join  web_website_new b

查询结果 4744504204

select count(*) from web_owner_new a   join  web_website_new b
on a.owner_id=b.owner_id

查询结果 84855

 

6.关于记录数的说明，这里可以简单这么看，假设A表记录为a,表B的记录数为b如果：

 

表A跟表B是1对1的关系，且A表中的的连接条件在B中存在n（a>n>0）条记录，那么

A inner  join B 的记录数=n

A left    join B 的记录数=a（其中有a-n条记录表B的字段为空）

A right   join B 的记录数=b（其中有b-n条记录表A的字段为空）

A full     join B 的记录数=a*b

如果n=a,全连接就等于交叉连接

 

表A跟表B是1对3的关系，且A表中的的连接条件在B中存在n（a>n>0）条记录，那么

A inner  join B 的记录数=n*3

A left    join B 的记录数=n*3+(a-n)（其中有a-n条记录表B的字段为空）

A right  join B 的记录数=b （其中有b-n*3条记录表A的字段为空）

A full    join B 的记录数=3*a*b-n*b=b(3a-n)

如果n=a,全连接就等于交叉连接

 

 

7.连接条件不只是等于号，其它运算符合也可以，如下所示

select a.*, b.*  from tab1 a left outer join tab2 b  on a.sn = b.sn(+) and  a.tx > b.tx(+)

 

 

8.全外连接的补充说明：

全外连接
1、全外连接实际上是对两个分别进行左右连接后，再将结果集联合到一块形成的。
2、进行联合的两个结果集的列必须相同。
3、全外连接的语法： 左外连接 union 右外连接
4、union只能有一个order by子句，并且它必须位于union的最后一行中。这个子句按指定顺序对union所有行进行排序。
5、union联合时，会删除重复的行，而不论重复的行是否来自同一个表。

 

9. union与union all的区别
1、与union非常相似，唯一的区别是union all不删除重复的行，也不对行进行自动排序。
2、union all比union省计算资源，应尽量使用union all

 

 

10. select * from aa,bb where aa.a=bb.a(+) and aa.c=5

select * from aa left join bb on aa.a=bb.a where aa.c=5

以上两种写法结果集一样，尽管使用了左外连接，但是where条件会把左外连接返回的结果集进行相应的过滤

假如使用(+)做左外连接，必须在where的两个条件里面都加上(+)

select * from aa left join bb on aa.a=bb.a and bb.c=7

select * from aa,bb where aa.a=bb.a(+) and bb.c(+)=7