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!
SELECT Users.first, Purchases.item FROM Users LEFT JOIN Purchases ON Users.username=Purchases.username ORDER BY Users.first
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.
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