--使用Northwind
--连接查询
--内连接(Inner Join)
select p.* from Products p inner join [Order Details] o on p.ProductID=o.ProductID
go
select p.*,c.* from Products p inner join Categories c on c.CategoryID=p.CategoryID
go
--1、左外连接
select p.*,c.* from Categories c left outer join Products p on p.CategoryID=c.CategoryID order by p.ProductID
--2、右外连接
select p.*,c.* from Categories c right outer join Products p on p.CategoryID=c.CategoryID order by p.ProductID
--3、全外连接
select p.*,c.* from Categories c full outer join Products p on p.CategoryID=c.CategoryID order by p.ProductID
--子查询
--1、使用比较运算符的子查询
select * from Products where unitprice>(select avg(unitprice) from Products)
--2、使用IN的子查询
select * from Products where categoryID in (select categoryID from categories)
--3、使用some和any的子查询
select * from Products where unitprice<some(select avg(unitprice) from Products)
--4、使用All的子查询
select * from Products where unitprice<>All(select avg(unitprice) from Products)
--5、使用Exists的子查询
select * from Products where exists (select * from categories where categories.categoryID=Products.categoryID and categories.categoryID=2)
