一、表连接
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时是最小值