Cacher is the code snippet organizer for pro developers

First, let's setup some sample data so we can look at how it relates

Customers Table

CustomerId	Name	Email
1	Joe Cool	TheCoolest@aol.com
2	Steven Brave	TheBravest@yahoo.com
3	Devin Smart	314159@wi.rr.com

Orders Table

OrderId	CustomerId
1	1
2	1
3	2

Items Table

ItemId	Description
1	Baseball Gloves
2	Football Cleats
3	Soccer Ball

Order Items Table

OrderItemId	OrderId	ItemId	Quantity
1	1	1	1
2	2	2	1
3	2	3	2
4	3	3	1

---

Now we can start talking about Joins

For all joins, we are matching up different tables based on some criteria. Typically, this will be done with a foreign key, meaning these officially related in some way, but the way we join tables is using the ON keyword, which allows us to define a condition which will tell us whether the two records in question should be matched or not.

Inner Joins

Inner Joins are for matching up records between two tables and only keeping things that have a match.

Consider the following, if we are looking at the Order Items table, we may want to be able to see the Descriptions of the Items rather than just their Ids

To do so, we could do the following:

SELECT oi.OrderItemId, oi.OrderId, oi.ItemId, i.Description, oi.Quantity
FROM OrderItems oi
  INNER JOIN Items i
    ON oi.ItemId = i.ItemId

This gives us the following:

OrderItemId	OrderId	ItemId	Description	Quantity
1	1	1	Baseball Gloves	1
2	2	2	Football Cleats	1
3	2	3	Soccer Ball	2
4	3	3	Soccer Ball	1

If we wanted to get a full snapshot of information, we can also remove the ID columns and join multiple layers down

SELECT c.Name, i.Description, oi.Quantity
FROM Customers c
  INNER JOIN Orders o
    ON o.CustomerId = c.CustomerId
  INNER JOIN OrderItems oi
    ON oi.OrderId = o.OrderId
  INNER JOIN Items i
    ON i.ItemId = oi.ItemId

This query would give us the following result:

Name	Description	Quantity
Joe Cool	Baseball Gloves	1
Joe Cool	Football Cleats	1
Joe Cool	Soccer Ball	2
Steven Brave	Soccer Ball	1

When dealing with Inner Joins, it is very easy to hide rows because they do not have a matching record in the table being joined on.

Consider the following query, where we want a list of Customers:

SELECT c.CustomerId, c.Name, c.Email
FROM Customers c

This just gives us the raw Customers table

CustomerId	Name	Email
1	Joe Cool	TheCoolest@aol.com
2	Steven Brave	TheBravest@yahoo.com
3	Devin Smart	314159@wi.rr.com

But if we wanted to also get information about the Orders they have placed, we could do the following

SELECT c.CustomerId, c.Name, c.Email, o.OrderId
FROM Customers c
  INNER JOIN Orders o
    ON o.CustomerId = c.CustomerId

Which gives us the following:

CustomerId	Name	Email	OrderId
1	Joe Cool	TheCoolest@aol.com	1
1	Joe Cool	TheCoolest@aol.com	2
2	Steven Brave	TheBravest@yahoo.com	3

Notice how Devin Smart was removed from the result because he has not placed any orders, but what if we still want him to show up in the list?

Then we can do a Left Join

Left Joins

Left Joins allow you to grab data from a single table, and then add additional data by joining to another table, without worrying about hiding records because they do not have a match.

Consider the last example in the Inner Join section. We wanted to include Devin Smart in the list, even if he did not have any Orders places.

Let's look at how we'd do that with the help of a Left Join

SELECT c.CustomerId, c.Name, c.Email, o.OrderId
FROM Customers c
  LEFT JOIN Orders o
    ON o.CustomerId = c.CustomerId

If you look closely at the query above, you'll notice the only difference was changing the type of join

Now our result is the following:

CustomerId	Name	Email	OrderId
1	Joe Cool	TheCoolest@aol.com	1
1	Joe Cool	TheCoolest@aol.com	2
2	Steven Brave	TheBravest@yahoo.com	3
3	Devin Smart	314159@wi.rr.com	NULL

We now have an extra record in our result, but we can notice that the OrderId field is NULL, which is because we were unable to match the Customer with any Orders. You should be very careful when using Left Joins as they can have a lot of NULL fields due to their nature.

Right Joins and Outer Joins

We won't really be talking about these, but we can include the following diagrams for reference

More Information