JOINs

One of the most useful SQL keywords JOIN allows you to compare data across tables. There comes a time in every young developer’s life where they have created a ton of tables, but they need to match information from one to another table. For example, you may have a list of users, but you need to match them with a list of orders. JOIN makes this very possible and quite simple once you get the hang of it. However, this might be one of the most difficult tutorials for someone who is just learning SQL.

Before we get into using a JOIN, I should tell you there are actually 4 types of JOINs.

  • JOIN – Returns values when values match in both tables
  • LEFT JOIN – Returns all specified “left” values including the matches on the “right”
  • RIGHT JOIN – Returns all specified “right” values including the matches on the “left”
  • FULL JOIN – Returns all specified “left” values and “right” values

“Left” and “Right” Tables

When the creators made SQL, the envisioned sheets of paper and apparently when creating JOIN, they used what they saw. In the most primitive JOIN usage, your “left” table is the table selected first (usually after SELECT). Your “right” table is the one directly following the JOIN keyword(s). I know it is pretty complicated just bear with me until the example where it will all make sense. JOIN obviously requires two tables and they are:

Table: “Users”

id username first last
1 ohBillNotAgain Hillary Clinton
2 internetCreator Al Gore
3 dontMessWithTexas George Bush

Table: “Purchases”

id username item quantity
1 ohBillNotAgain Pickles 95
2 internetCreator Meerkat Treats 11

All political jokes aside, let’s try to get all of our users including the their purchases. Example, please!

Result
first item
Al Meerkat Treats
George  
Hillary Pickles

Beautiful! You might be confused, and I most definitely was when I first learned about JOINs. For example, Users.first? What in the world is that? Remember we are working with 2 tables and SQL will get confused very quickly if we are telling it about columns, when it doesn’t know which table that contains that column. The Users is our first table, and first is a column in the “Users” table. Now, SQL is like “Oh! That column.” We repeat that idea to get our item column in our “Purchases” table. We pick FROM Users because we want “Users” to be our first (LEFT) table. This means that we want every single first column in our “Users” table now matter what is specified in the following JOIN.

Now, we get tricky. When then declare our 2nd (RIGHT) table with LEFT JOIN Purchases. But wait, we need to tell it why we are including a 2nd table. We use the SQL keyword ON to say we want to find matching values in our username column in the Users table with our other username column in our Purchases table. Then we wrap up with saying, “Hey, sort it by their first, which is in Users, really quick. Ultimately, if you know how to use a LEFT JOIN, you can use all of the rest. Go ahead and test out the others you little querying mastermind.

If you enjoyed this resource, support me by sharing this page with others.
Share on Facebook
Facebook
Share on StumbleUpon
StumbleUpon
Digg this
Digg
Tweet about this on Twitter
Twitter
Share on Reddit
Reddit

Leave a Comment

  1. This is very helpful. I learned SQL a few years back, but never used it , so I forgot most of it. This is helping me remember. This is not to much and it is straight to the point. Thanks

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.