go backsqlschool blogs list

How joins works in SQL Server

by Antonios Chatzipavlis





Presentation Code

CHECKPOINT; 
DBCC FREEPROCCACHE; 
DBCC DROPCLEANBUFFERS;
go

select o.orderid, d.qty, c.contactname, p.productname
from sales.Orders as o
inner join sales.Customers as c on o.custid=c.custid
inner join sales.OrderDetails as d on o.orderid = d.orderid
inner join Production.Products as p on p.productid = d.productid
where c.contactname = 'Benito, Almudena'
and p.productname = 'Product VJZZH';
go




CHECKPOINT; 
DBCC FREEPROCCACHE; 
DBCC DROPCLEANBUFFERS;
go

with a as
(
    select o.orderid, c.contactname
    from sales.Orders as o
    inner join sales.Customers as c on o.custid=c.custid

), b as
(
    select d.qty, p.productname ,d.orderid
    from sales.OrderDetails as d 
    inner join Production.Products as p on p.productid = d.productid

)
select a.orderid, b.qty, a.contactname, b.productname
from a
inner join b on a.orderid = b.orderid
where a.contactname = 'Benito, Almudena'
and b.productname = 'Product VJZZH';
go





CHECKPOINT; 
DBCC FREEPROCCACHE; 
DBCC DROPCLEANBUFFERS;
go

with a as
(
    select o.orderid, c.contactname
    from sales.Orders as o
    inner join sales.Customers as c on o.custid=c.custid

), b as
(
    select d.qty, p.productname ,d.orderid
    from sales.OrderDetails as d 
    inner join Production.Products as p on p.productid = d.productid

)
select a.orderid, b.qty, a.contactname, b.productname
from a
inner join b on a.orderid = b.orderid
where a.contactname = 'Benito, Almudena'
and b.productname = 'Product VJZZH'
option (FORCE ORDER)
go





CHECKPOINT; 
DBCC FREEPROCCACHE; 
DBCC DROPCLEANBUFFERS;
go

select o.orderid, d.qty, c.contactname, p.productname
from 
    (
        sales.Orders as o
        inner join sales.Customers as c on o.custid=c.custid 
    )
inner join 
    (
        sales.OrderDetails as d 
        inner join Production.Products as p on p.productid = d.productid
    ) on o.orderid = d.orderid
where c.contactname = 'Benito, Almudena'
and p.productname = 'Product VJZZH'
option (FORCE ORDER);
go
Ημερομηνία: 07 September 2018 22:43
Αξιολόγηση: ( 1 )
Κατηγορίες:
Share it:

Αφήστε το σχόλιο σας - Leave your comment

ΟΝΟΜΑΤΕΠΩΝΥΜΟ - FULL NAME

EMAIL ADDRESS

Θα φαίνεται το Gravatar εικονίδιο σας

WEB SITE

COMMENT


Πληκτρολογήστε τον αριθμό που βλέπετε :
captcha