SQL中的表连接及子查询

一、表连接

SQL Server支持多种连接包括：内连接，左连接，右连接，交叉连接，全外连接。

每种连接类型指定SQL Server如何使用一个表中的数据来选择另一个表中的行

1.内连接

内连接是查询出两个表相关联的部分。

select select_list
from T1
inner join T2 on join_predicate

内连接关键字：inner join ......on......，on后面跟查询条件。from后面跟的表为主表，inner join后面跟的表为副表。（后面的左连接，右连接，交叉连接，自连接也同样适用）

2.左连接

left join子句用于查询多个表的数据。它返回左表中的所有行和右表中的匹配行，如果右表中找不到匹配行，则返回NULL值

select
    select_list
from
    T1
left join T2 on
    join_predicate

关键字：left join.....on......

3.右连接

select
    select_list
from
    T1
left join T2 on
    join_predicate

关键字：right join.....on......

4.交叉连接

cross join连接两个或多个不相关的表

以下是两个表的SQL Server join的语法cross join将第一个表中的每一行与第二个表中的每一行连接起来。换句话说，交叉连接返回两个表中行的笛卡尔积。与inner join或left join不同，交叉连接不会再连接的表之间建立关系

SELECT
	select_list
FROM
	T1
CROSS JOIN T2

5.自连接

自连接不在五大连接之内。自连接用于将表连接到自身（同一个表）。它对于查询分层数据或比较同一个表中的行很有用。

自连接使用内连接或左连接子句。由于使用自连接的查询引用同一个表。因此表别名用于为查询中的表分配不同的名称。

如果在不使用表别名的情况下多次引用同一个表，则会出现错误

SELECT
    select_list
FROM 
    T t1
[inner|left] join T t2 on
    join_predicate;

6.全外连接

关键字：full outer join

当左表或右表中存在匹配项时，该命令将返回所有的行

select * from pm.projects p
full outer join pm.members m
on p.id=m.project_id

原本两个表有关联，但是名称不一样

进行全外链接后得到

约等于左连接和右连接进行合并，关联不上的都给null值

二、子查询

1.子查询

select * from 
	sales.orders 
where 
	customer_id in( 
select 
 	customer_id from 
	sales.customers 
where 
 	city='New York' 
 ) 

子查询时嵌套在另一个语句中如：（select,insert,update或delete）中的查询

  

2.嵌套子查询

子查询可以嵌套在另一个子查询中，SQL Server最多支持32个嵌套级别

--查找价格高于'上海永久','凤凰'品牌的所有产品的平均定价的产品
select * from production.products
where list_price >(
select 
    avg(list_price)
from 
    production.products
where 
    brand_id in (
  select
     brand_id
    from
     production.brands
    where
     brand_name in('上海永久','凤凰')
)
)

 最终的出的结果如下图所示，共161条数据

 

3.相关子查询

相关子查询是外部查询的值的【子查询】。换句话说，它取决于外部有查询的值，由于这种依赖性，相关子查询不能作为简单的子查询独立执行

此外，对外部查询评估的每一行重复执行一次相关子查询

相关子查询也称为重复子查询

---示例查找价格等于其类别的最高价格的产品
---子查询
select * from production.products p1
inner join(
    select category_id,max(list_price)max_price
    from production.products
    group by category_id
    )p2
   on p1.category_id=p2.category_id
   and p1.list_price=p2.max_price
   
---相关子查询
select p1.* from production.products p1
where p1.list_price in(
    select max(list_price)max_price
    from production.products p2
    where p1.category_id=p2.category_id
    group by category_id
)
select p1.* from production.products p1
order by category_id

4.Exists运算符

Exists运算符是一个逻辑运算符，用于检查子查询是否放回任何行，如果子查询返回一行或多行，则Exists运算符返回true以下是SQL Server Exists运算符的语法：

Exists(subquery)

在此语法中，子查询仅是Select语句。子查询返回行后，Exists运算符返回true并立即停止处理

请注意，即使子查询返回Null值，Exists运算符也会计算为true

select * from
	production.products p1
where exists(
	select * from
    	production.products p2
	where
    	p1.category_id = p2.category_id
		and p1.list_price>8000
	)

 

5.any运算符

--查找其他品牌价格大于‘优米优品牌的任意产品的价格的产品’
select * from
	production.products
where
	list_price>any
(select 
     list_price 
 from 
 	production.products
 where
 	brand_id =9)
and
	brand_id!=9
order by list_price

某个值大于any（）指大于any中任意一个值，有可能大于最小值，有可能大于最大值

6.all运算符

--查找其他品牌价格大于‘优米优’品牌任何产品的价格的产品
select * from
	production.products
where
	list_price>all
(select
        list_price from production.products where brand_id=9)
and
	brand_id!=9
order by list_price

 

当要求的数据大于all时是大于最大值，小于all时是最小值