第三章 联接查询

use tsql2012

--cross join

select c.custid, e.empid

from sales.customers as c

cross join hr.employees as e;

--self-join

select

e1.empid, e1.firstname, e1.lastname,

e2.empid, e2.firstname, e2.lastname

from hr.employees as e1

cross join hr.employees as e2;

--number(1-1000) table

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);

select D3.digit * 100 + D2.digit * 10 + D1.digit + 1 as n

from dbo.digits as D3

cross join dbo.digits as d2

cross join dbo.digits as d1

order by n;

--inner join

use tsql2012

select e.empid, e.firstname, e.lastname, o.orderid

from hr.employees as e

join sales.orders as o

on e.empid = o.empid;

use tsql2012

if object_id('Sales.orderdetailsAudit', 'U') is not null

drop table sales.orderdetailsaudit;

create table sales.orderdetailsaudit

(

lsn int not null identity,

orderid int not null,

productid int not null,

dt datetime not null,

loginname sysname not null,

columnname sysname not null,

oldval sql_variant,

newval sql_variant,

constraint PK_OrderDetailsAudit primary key(lsn),

constraint fk_orderdetailsaudit_orderdetails foreign key(orderid, productid)

references sales.orderdetails(orderid, productid)

);

--composite join

select od.orderid, od.productid, od.qty,

oda.dt, oda.loginname, oda.oldval, oda.newval

from sales.orderdetails as od

join sales.orderdetailsaudit as oda

on od.orderid=oda.orderid

and od.productid=oda.productid

where oda.columnname=N'qty';

--non-equi join

select E1.empid, e1.firstname, e1.lastname,

e2.empid, e2.firstname, e2.lastname

from hr.employees as e1

join hr.employees as e2

on e1.empid < e2.empid;

--multi-table join

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;

--left outer join

select c.custid, c.companyname, o.orderid

from sales.customers as c

left outer join sales.orders as o

on c.custid=o.custid;

--left outer join 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;

set nocount on;

use tsql2012

if object_id('dbo.nums', 'U') is not null

drop table dbo.nums;

create table dbo.nums(n int not null primary key);

declare @i as int =1;

begin tran

while @i <= 100000

begin

insert into dbo.nums values (@i);

set @i =@i+1;

end

commit tran

set nocount off;

select dateadd(day, nums.n-1, '20060101') as orderdate,

o.orderid, o.custid, o.empid

from dbo.nums

left outer join sales.orders as o

on dateadd(day, nums.n-1, '20060101') = o.orderdate

where nums.n <= datediff(day, '20060101', '20081231')+1

order by orderdate;

use tsql2012;

select c.custid, o.orderid, od.productid, od.qty

from sales.customers as c

left outer join sales.orders as O

on c.custid=o.custid

left outer join sales.orderdetails as od

on o.orderid = od.orderid

where o.custid is null;

select c.custid, o.orderid, od.productid, od.qty

from sales.orders as o

join sales.orderdetails as od

on o.orderid = od.orderid

right outer join sales.customers as c

on c.custid = o.custid

where o.custid is null;

--count left outer join

select c.custid, count(o.orderid) as numorders

from sales.customers as c

left outer join sales.orders as o

on c.custid = o.custid

group by c.custid;