Back to articles list October 25, 2016 - 8 minutes read SQL JOINs for Beginners Adrian Dembek Adrian is a CRM Analyst working closely with Vertabelo. Tags: join You’re probably already familiar with simple SQL queries, such as SELECT * FROM table. Now you are wondering what to do when you have multiple tables, and you want to join them. Exactly! JOIN is the key. In this SQL JOINs tutorial for beginners, you will learn how to connect data from multiple tables. What are SQL JOINs? Databases usually have more than one table. JOINs are an SQL construction used to join data from two or more tables. When you want to use columns from two tables in a result table, the easiest way to do it is to write a JOIN query. The syntax of an SQL JOIN is: SELECT * FROM table1 JOIN table2 ON table1.id1=table2.id2 As this is an SQL JOINs tutorial for beginners, let’s start with the basics. We’ll go over the elements individually. After the FROM keyword, you write the name of the first table that you want to take columns from. Then, you use the JOIN keyword, followed by the name of the second table that you want to use. Next, the ON statement tells the query on which columns data should be joined. (Usually this is an ID). In the above syntax, we join the tables using id1 from table1 and id2 from table2. This is written as table1.id1 and table2.id2 – after the table name there is a dot and then the column name. It is worth clarifying that the columns on which we join the data are not always in the form of an ID. They also don’t need to have the same name. For example, it is possible to join two tables based on keys called orders.post_code and cities.zip_code. As you can see, this is not an ID and the column names for postal codes are different too. However, both columns contain postal codes; two matching fields are needed to make an SQL JOIN. INNER JOIN Let’s continue with our SQL JOINs for beginners guide. We’ll see how SQL JOINs work in a simple example. Suppose we want to find out which artist recorded a certain song. In this basic data model, there are only two tables: song and artist. The song table contains the columns song_id, song_title and artist_id. The artist table consists of artist_id and artist_name. As we can see, artist_id is in both tables; we can use it to join them. The simplest JOIN (also called an INNER JOIN) takes the left table and matches information for each row from the right table using artist_id. Thus, the first song, “Dancing Queen”, has an artist_id equal to “04”; in the artist table, “04” is assigned to “ABBA”. As you can see in the artist table, there is no artist_id equal to “06”, which is assigned to the song “Rolling in the Deep”. In the result table, only songs matched with an artist_name are shown. There also aren’t any Michael Jackson songs in the result table, as there is no such song in the song table. Remember that INNER JOIN selects all rows from both tables as long as there is a match between the columns in both tables. Here’s the code: SELECT * FROM song JOIN artist ON song.artist_id=artist.artist_id LEFT JOIN The second type of join we’re gonna mention in this SQL JOINs tutorial for beginners is the LEFT JOIN. It always takes the first table (meaning the table just after the FROM keyword) as a whole (all the records in the first table). It takes only matching records from the second table. If there is no matching information, a NULL value is returned. This means that there is no information available for that particular ID. Take another look at the artist table. There is no artist_name with an artist_id equal to “06”. This is why the song “Rolling in the deep” did not occur in the INNER JOIN query result. While it is listed in the LEFT JOIN example below, it has NULL values for artist_id and artist_name. Here’s the SQL LEFT JOIN statement: SELECT * FROM song LEFT JOIN artist ON song.artist_id=artist.artist_id RIGHT JOIN The next type of join in our SQL JOINs tutorial for beginners is the RIGHT JOIN. Unlike LEFT JOIN, it takes all the records from the second table (the one immediately after the JOIN keyword) and only those records from the first table that have matching artist_ids. In this case, there are no Michael Jackson songs in the database, so there are NULLs placed for those song_ids, song_titles and artist_ids. And now “Rolling in the Deep” has disappeared, as in the right (artist) table there is no artist_id equal to “06”. Here’s the code: SELECT * FROM song RIGHT JOIN artist ON song.artist_id=artist.artist_id FULL JOIN The last type of JOIN we’re gonna discuss in our SQL JOINs for beginners guide is the FULL JOIN. It is a combination of LEFT JOIN and RIGHT JOIN, as it takes all the records from both tables and places NULL values where the information from the matching table is missing. In the example below, you can see NULLs for artist_id equal to “06” (yes, it should be Adele!) and NULLs for Michael Jackson songs with artist_id equal to “05”. (Yes, “Billie Jean” exists, but SQL does not know it!) SELECT * FROM song FULL JOIN artist ON song.artist_id=artist.artist_id Real Life Example: Stores and Products Now we will show you how to use SQL JOINs in a real business example. This time, our SQL JOINs tutorial for beginners will use an example from the retail industry. Imagine you run a store and you want to see which products are out of stock. You also want to check if your database manager keeps things in order. Let’s assume the database has only two tables: product and stock. As you can see, the first table (product) has two columns: product_id and product_name. If everything is in order, there should be an ID and a name for all the products we have ever sold. The second table (stock) represents the current stock we have in the store – e. g. there are seven items for the product_id value of “100”. Please take a closer look at product_id column in both tables. Some identifiers are present in both tables (102, 105, 107); some are only in the left one (101, 103, 106); and some are only in the right one (100, 104). This information will be used in the examples below. Disregarding Missing Information When we want to see only the products currently in stock and we already know all their names, we can use an INNER JOIN statement. It selects only these records with an id present in both tables that we want to join. As a result, we get a list of three product names and their quantities. As you can see, you can use both JOIN and INNER JOIN statement to do the same thing. JOIN is a shorthand for INNER JOIN. The other JOINs have to be specified explicitly. AN INNER JOIN SELECT product_name, stock.product_id, quantity FROM products INNER JOIN stock ON product.product_id = stock.product_id Which Products Should Be Ordered? Suppose we want to check which products are out of stock and need to be reordered. To do that, we will use a LEFT JOIN to match records from the product table to the stock table. We can visualize this as putting the product table on the left and the stock table on the right. Then we look for records in both tables that match by product_id. Records without matching product_id fields are given a NULL. (See the result table below.) A LEFT JOIN SELECT product_name, stock.product_id, quantity FROM products LEFT JOIN stock ON product.product_id = stock.product_id So, in the stock table there are no product_id values equal to “101”, “103” or “106”. This is why the table displays NULLs for orange juice, coffee, and cereal. These are the products we need to order. Do We Know What We Sell? The role of the database manager is to keep everything in our databases in order. Let’s say we want to verify if he or she is doing a good job. We’ll check if the product table is being updated with new products. For this, we will use a RIGHT JOIN. RIGHT JOIN takes all the records from the stock table and puts it to the right (just as the RIGHT JOIN would suggest). Then, it displays only the product table records that have a matching product_id. A RIGHT JOIN SELECT product_name, stock.product_id, quantity FROM products RIGHT JOIN stock ON product.product_id = stock.product_id Show Everything Available To get a full picture of the situation, we may want to see all product names and all their quantities in both tables. Then we can see which products need to be reordered and which product_ids are missing a product_name. For this, FULL JOIN comes in handy! It takes all records from the left table (product) and all records from the right table (stock) and matches only the records that have the same id (product_id); it inserts a NULL on the right if there is a missing product_name or on the left if there are no items in stock. FULL JOIN SELECT product_name, stock.product_id, quantity FROM products FULL JOIN stock ON product.product_id = stock.product_id Want to Learn More About SQL JOINs? You can find more examples of using SQL JOINs on our YouTube channel - We Learn SQL. Check it out and remember to subscribe. I hope this quick SQL JOINs tutorial for beginners has helped you understand how to join data from two tables. If you’d like more information, I recommend enrolling in Vertabelo Academy, where you can learn how to use SQL (and more!) to deal with databases. The topics are presented in a simple and very understandable way. Try Vertabelo Academy SQL courses for free today! Tags: join You may also like Improving Query Readability with Common Table Expressions What is a Common Table Expression, or CTE? Where do you use them, and why? This post answers your questions. Read more How to Practice SQL JOINs Do you want to master SQL JOINs? The best way is through practice. Here’s how to practice SQL JOINs along with some examples. Read more Learning JOINs With Real World SQL Examples JOIN statement lets you work with data stored in multiple tables. This article is a practical introduction to the SQL JOIN. Check out examples in real life. Read more SQL JOINs A SQL JOIN is a method to retrieve data from two or more database tables. Here we present a basic overview of what data from a particular SQL join will look Read more Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.