Joins From A Common Sense Perspective

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.