Business Intelligence Blogs

View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.

Joins From A Common Sense Perspective

  • 27 November 2010
  • Author: SMcDonald
  • Number of views: 7471
  • 0 Comments

In preparation for the Pragmatic Works Foundation Class in December, I was searching for information regarding Joins.  Seeing the TSQL command is one thing, but being able to have a diagram and script of joins and how they relate is another.  In today's article, I will be posting sample queries to the most commonly used / heard of joins.  I am also going to supply a 1 page pdf download of Pinal Dave's JOINS so you can see how it would appear from a visual perspective.

1.  Inner Join: 

--INNER JOIN 

SELECT 'INNER JOIN' AS JoinType, c.CustID, c.FName + ' ' + c.LName as FullName, o.OrderID

FROM Customers c

      JOIN Orders o ON c.CustID = o.CustID

  

 

 

 

  

 

2.  Left Outer Join:

--LEFT OUTER - Customers and their OrderID's 

SELECT 'LEFT OUTER JOIN' AS JoinType, c.CustID, c.FName + ' ' + c.LName as FullName, o.OrderID

FROM Customers c

      LEFT OUTER JOIN Orders o ON c.CustID = o.CustID

 

 

3.  Left Outer Join (Where Null):   

--LEFT OUTER - Customers with NO ORDERS 

SELECT 'LEFT OUTER JOIN - NO ORDERS' AS JoinType, c.CustID, c.FName + ' ' + c.LName as FullName, o.OrderID

FROM Customers c

      LEFT OUTER JOIN Orders o ON c.CustID = o.CustID

WHERE o.OrderID IS NULL

 

 

 

4.  Right Outer Join:

--RIGHT OUTER - Orders and the Customers who placed them

SELECT 'RIGHT OUTER JOIN' AS JoinType, c.CustID, c.FName + ' ' + c.LName as FullName, o.OrderID

FROM Customers c

      RIGHT OUTER JOIN Orders o ON c.CustID = o.CustID

 

 

  

5.  Right Outer Join (Where Null):

-RIGHT OUTER - Customers with no Orders

SELECT 'RIGHT OUTER JOIN With NULLS' AS JoinType, o.OrderID, c.CustID, c.FName + ' ' + c.LName as FullName

FROM Orders o

      RIGHT OUTER JOIN Customers c ON o.CustID = c.CustID

Where O.OrderID is Null

 

6.  Outer Join (Where Null):

--OUTER With NULLS - Customers who have no orders

Select *

From Orders O

      Full Outer Join Customers C

      On O.CustID = C.CustID

Where C.CustID is Null or O.CustID is Null

 

7.  Full Outer Join:

--FULL OUTER - All Customers with or without Orders

Select *

From Orders O    

      Full Outer Join Customers C

      On O.CustID = C.CustID

 

 

 

 

8.  Cross Join:

--CROSS JOIN(cartesan product) - All Possible combinations

SELECT 'CROSS JOIN' AS JoinType, c.CustID, c.FName + ' ' + c.LName as FullName, o.OrderID

FROM Customers c, Orders o

 

 

 

Here is Pinal Dave's diagrams from SQLAuthority.com  converted into a 1  page pdf file.  You can download it from here.   In this article I have shown you some examples of common Joins and how you could use them in your environment.  Please feel free to submit any questions.  Have a wonderful weekend.

 

 

 

 

Print
Categories: Miscellaneous
Tags:
Rate this article:
4.0

SMcDonaldSMcDonald

Other posts by SMcDonald

Please login or register to post comments.