go backarticles

Articles of SQLschool.gr Team

How joins works in SQL Server

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

Relative Articles

Leave your comment

Login with your SQLschool.gr account if you want to comment on this article.


Follow us in
PASS chapter logo

The Official PASS Local Group for Greece

sql school greece logo
© 2010-2019 All rights reserved