第三章 联接查询(1)

use TSQLFundamentals2008;
go

--3.1 ½»²æÁ¬½Ó
--cross join
select c.custid, e.empid
from Sales.Customers as c
cross join HR.Employees as e;

select c.custid, e.empid
from Sales.Customers as c,
HR.Employees as e

select e1.empid, e1.firstname, e1.lastname,
e2.empid, e2.firstname, e2.lastname
from HR.Employees as e1 
cross join HR.Employees as e2;

use tempdb;
if OBJECT_ID('dbo.Digits', 'u') is not null
drop table dbo.Digits

create table dbo.Digits(digit int not null primary key);

insert into dbo.Digits(digit)
values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

--Éú³É1-1000
select d3.digit*100 + d2.digit*10 + d1.digit + 1 as num
from dbo.Digits as d3 
cross join dbo.Digits as d2
cross join dbo.Digits as d1
order by num

use TSQLFundamentals2008;
go

--3.2 ÄÚÁ¬½Ó
--inner join
select e.empid, e.firstname, e.lastname, o.orderid
from hr.employees as e
inner join 
Sales.Orders as o
on e.empid = o.empid;

select e.empid, e.firstname, e.lastname, o.orderid
from HR.Employees as e, Sales.Orders as o
where o.empid = e.empid

--3.3 ÌØÊâµÄÁª½ÓʵÀý
--×éºÏÁª½Ó
select e.empid, e.firstname, e.lastname, o.orderid
from hr.employees as e
inner join 
Sales.Orders as o
on e.empid = o.empid
and o.shipcountry = e.country

--²»µÈÁª½Ó
select e.empid, e.firstname, e.lastname, o.empid, o.firstname, o.lastname
from HR.Employees as e
inner join 
HR.Employees as o
on e.empid < o.empid

--¶à±íÁª½Ó
select c.custid, c.companyname, o.orderid, od.productid, od.qty
from Sales.Customers as c
join Sales.Orders as o
on c.custid = o.custid
join Sales.OrderDetails as od
on od.orderid = o.orderid

--3.4 ÍâÁª½Ó
--ÔÚÍâÁª½ÓÖÐ,Òª°ÑÒ»¸ö±í±ê¼ÇΪ"±£ÁôµÄ"±í,¿ÉÒÔÔÚ±íÃûÖ®¼äʹÓùؼü×Öleft outer join¡¢
--right outer joinÒÔ¼°full outer join£¬ÆäÖÐouter¹Ø¼ü×ÖÊÇ¿ÉÑ¡µÄ¡£
--Left¹Ø¼ü×Ö±íʾ×ó±ßµÄ±íµÄÐÐÊDZ£ÁôµÄ£¬right¹Ø¼ü×Ö±íʾÓұ߱íµÄÐÐÊDZ£ÁôµÄ£¬¶øfull¹Ø
--¼ü×ÖÔò±íʾ×óÓÒÁ½±ß±íµÄÐж¼ÊDZ£ÁôµÄ¡£ÍâÁª½ÓµÄµÚÈý¸öÂß¼­²éѯ´¦Àí²½Öè¾ÍÊÇҪʶ±ð±£Áô
--±íÖа´ÕÕonÌõ¼þÔÚÁíÒ»¸ö±íÕÒ²»µ½Óë֮ƥÅäµÄÄÇЩÐУ¬ÔÙ°ÑÕâЩÐÐÌí¼Óµ½Áª½ÓµÄÇ°Á½¸ö²½Öè
--Éú³ÉµÄ½á¹û±íÖУ»¶ÔÓÚÀ´×ÔÁª½ÓµÄ·Ç±£Áô±íµÄÄÇЩÁУ¬×·¼ÓµÄÍⲿÐÐÖеÄÕâЩÁÐÔòÓÃnull×÷
--Ϊռλ·û¡£
select c.custid, c.companyname, o.orderid
from Sales.Customers as c
left outer join Sales.Orders as o
on c.custid = o.custid

--ÔÚÍâÁª½ÓÖÐ,on×Ó¾äÖеĹýÂËÌõ¼þ²»ÊÇ×îÖÕµÄ,where×Ó¾äÊÇ×îÖÕµÄ
--·µ»ØûÓÐÏÂÈκζ©µ¥µÄ¿Í»§
select c.custid, c.companyname 
from Sales.Customers as c
left outer join Sales.Orders as o
on c.custid=o.custid
where o.orderid is null;