Home » Tutorials » SQL » JOINs

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”

idusernamefirstlast
1ohBillNotAgainHillaryClinton
2internetCreatorAlGore
3dontMessWithTexasGeorgeBush

Table: “Purchases”

idusernameitemquantity
1ohBillNotAgainPickles95
2internetCreatorMeerkat Treats11

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
Result
firstitem
AlMeerkat Treats
George 
HillaryPickles

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.



Link/cite this page

If you use any of the content on this page in your own work, please use the code below to cite this page as the source of the content.

  • Stewart, Suzy. "JOINs". After Hours Programming. Accessed on May 20, 2022. https://www.afterhoursprogramming.com/tutorial/sql/join/.

  • Stewart, Suzy. "JOINs". After Hours Programming, https://www.afterhoursprogramming.com/tutorial/sql/join/. Accessed 20 May, 2022.

  • Stewart, Suzy. JOINs. After Hours Programming. Retrieved from https://www.afterhoursprogramming.com/tutorial/sql/join/.



0 thoughts on “JOINs”

  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 Comment

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