x

Agile Insider Blog

Understanding SQL Server Joins Basics (Query and Internals)

Great post by Biplab Paul on SQL joins and por/cons of each one on his blog.

 

I love to explain things in much simpler way but SQL Joins are one of those concepts which are not so simple to explain and I really spend a lot of time in scribbling on White Board for this. But finally I found an article from Jeff Atwood where he did an incredible job by simply using Venn Diagrams to explain the whole concept. I will take few extract from his article but rest you can read from his blog.

INNER JOIN:SELECT * FROM TableA

INNER JOIN TableB

ON TableA.name = TableB.name

 
OUTER JOIN (1):SELECT * FROM TableA

FULL OUTER JOIN TableB

ON TableA.name = TableB.name

 
OUTER JOIN (2):SELECT * FROM TableA

LEFT OUTER JOIN TableB

ON TableA.name = TableB.name

 
OUTER JOIN (3):SELECT * FROM TableA

LEFT OUTER JOIN TableB

ON TableA.name = TableB.name

WHERE TableB.id IS null

 
OUTER JOIN (4):SELECT * FROM TableA

RIGHT OUTER JOIN TableB

ON TableA.name = TableB.name

 
OUTER JOIN (5):SELECT * FROM TableA

FULL OUTER JOIN TableB

ON TableA.name = TableB.name

WHERE TableA.id IS null

OR TableB.id IS null

 
CROSS APPLY (1): without Where ClauseSELECT p.SalesPersonID, t.Name ASTerritory

FROM Sales.SalesPerson p

CROSS JOIN Sales.SalesTerritory t

ORDER BY p.SalesPersonID

Note: This can’t be represented by Venn Diagrams but above would multiply all the contents of First Table with all the contents of the second table.
CROSS APPLY (2): with Where ClauseSELECT p.SalesPersonID, t.Name ASTerritory

FROM Sales.SalesPerson p

CROSS JOIN Sales.SalesTerritory t

WHERE p.TerritoryID = t.TerritoryID

ORDER BY p.SalesPersonID

 

Now when we understood these different types of Joins, let’s talk about some of the internal on How these joins would work internally within SQL Server.

<snip>

 

Read the rest @> Few things which you may find helpful!! : Simplifying SQL Server Joins (Query and Internals)

Leave a comment

Learn More Today

Have questions or want to learn more about the services and solutions Agile IT has to offer?

Schedule a call with us today!

Schedule a Call
or

Request a Quote