Back to articles list July 20, 2017 - 5 minutes read An Illustrated Guide to the SQL INNER JOIN Dorota Wdzięczna Dorota is an IT engineer and works as a Data Science Writer for Vertabelo. She has experience as a Java programmer, webmaster, teacher, lecturer, IT specialist, and coordinator of IT systems. In her free time, she loves working in the garden, taking photos of nature, especially macro photos of insects, and visiting beautiful locations in Poland. Tags: join SQL basics SQL clauses What is an SQL INNER JOIN, and how does it work? Let's find out! In my last article, I discussed the CROSS JOIN operation in SQL. Today, we'll look at INNER JOIN and how to use it. Is it the same as a JOIN? How many tables can you link with an INNER JOIN? These are all good questions. Let's look at the answers! What is an INNER JOIN? INNER JOIN combines data from multiple tables by joining them based on a matching record. This kind of join requires a joining condition, which we will explain in a moment. To illustrate how INNER JOIN works, we will use some simple tables. Two of them, color and shoes are shown below: color id name 1 yellow 2 green 3 pink shoes id size color_id 1 seven 2 2 eight 2 3 nine 2 4 seven 1 5 nine 1 6 seven 3 7 ten NULL The color table stores an ID number and a name for each color. The shoes table stores an ID number for each pair of shoes, the shoe size, and an ID number that refers to a color in the color table. How INNER JOIN Works In this example, INNER JOIN joins records from the shoes table with records from the color table through the color_id column from the shoes table. The values in this column are the same as those in the id column of the color table, so it produces the intended result set. Have a look at the following query: SELECT * FROM shoes INNER JOIN color ON color.id = shoes.color_id; The SELECT statement takes all records from the table listed after the FROM clause – in this case, the shoes table. Then there is an INNER JOIN with the name of the table we want to match records with (i.e. the color table). The ON predicate states the matching condition, which records from both tables must have. Here the condition is that the id field from the color table and the color_id field from the shoes table must have matching values. If a record doesn't have a match, it will be left out of the results. Take a look at the result set from this query: id size color_id id name 1 seven 2 2 green 2 eight 2 2 green 3 nine 2 2 green 4 seven 1 1 yellow 5 nine 1 1 yellow 6 seven 3 3 pink Now we can see the color of each pair of shoes, thanks to the INNER JOIN. But notice that the shoes record with NULL in the color_id column is not shown: it has not matched any of the records in the color table. JOIN or INNER JOIN? You can omit the INNER part of INNER JOIN; JOIN works the same way. Look at the statement below. SELECT * FROM shoes JOIN color ON color.id = shoes.color_id; Joining with WHERE You can also use a WHERE clause like an INNER JOIN. (This is an older version of SQL syntax (ANSI-89); the JOIN commands use ANSI-92.) Here is how using a WHERE works: SELECT * FROM shoes, color WHERE color.id = shoes.color_id ; The result is the same: id size color_id id name 1 seven 2 2 green 2 eight 2 2 green 3 nine 2 2 green 4 seven 1 1 yellow 5 nine 1 1 yellow 6 seven 3 3 pink Using an INNER JOIN on Multiple Tables INNER JOIN can be used on more than two tables. Look at the three tables below: color id name 1 yellow 2 green 3 pink material id name 1 leather 2 cotton 3 NULL shoes id size color_id material_id 1 seven 2 1 4 seven 1 2 5 nine 1 1 6 seven 3 NULL 7 ten NULL 1 Let's use an INNER JOIN to combine information in all three tables. The query below retrieves records for all shoes that have color and material information: SELECT * FROM shoes JOIN color ON color.id = shoes.color_id JOIN material ON material.id = shoes.material_id ; Notice that only shoes with non-NULL records in the color_id and material_id columns are shown in the result set. id size color_id material_id id name id name 1 seven 2 1 2 green 1 leather 4 seven 1 2 1 yellow 2 cotton 5 nine 1 1 1 yellow 1 leather INNER JOIN vs. CROSS JOIN As we have seen, INNER JOIN combines information from two or more records that have matching fields. What happens when you use CROSS JOIN to join the shoes and color tables? CROSS JOIN doesn't take any ON conditions, which means it returns a Cartesian product. Have a look at the query and result set shown below: SELECT * FROM shoes CROSS JOIN color ; Result? id size color_id id name 1 seven 2 1 yellow 2 eight 2 1 yellow 3 nine 2 1 yellow 4 seven 1 1 yellow 5 nine 1 1 yellow 6 seven 3 1 yellow 7 ten NULL 1 yellow 1 seven 2 2 green 2 eight 2 2 green 3 nine 2 2 green 4 seven 1 2 green 5 nine 1 2 green 6 seven 3 2 green 7 ten NULL 2 green 1 seven 2 3 pink 2 eight 2 3 pink 3 nine 2 3 pink 4 seven 1 3 pink 5 nine 1 3 pink 6 seven 3 3 pink 7 ten NULL 3 pink All records from the shoes table have been joined with all records from the color table. This creates some errors. For example, some shoes aren't listed with the correct color: size-eight shoes are only available in green (their color_id value is 2), but in this join instance they are matched with other colors. The NULL records in the shoes table are matched with colors as well, even though they do not have a comparable value in the color_id field. Learn More About INNER JOINs INNER JOINs are quite common in SQL. Our goal in this article was to introduce the concepts behind INNER JOIN, but there is a lot more to learn. Why not check out LearnSQL's SQL Basics course to find out more? Tags: join SQL basics SQL clauses You may also like An Illustrated Guide to Multiple Join Did you wonder what is the most efficient way of making multiple joins in SQL? We did as well - come by and check out our illustrated guide! Read more An Illustrated Guide to the SQL CROSS JOIN CROSS JOIN returns a Cartesian product, or all records joined to all records in all tables. Take a look at the example queries. Read more An Illustrated Guide to the SQL Non Equi Join SQL join doesn’t have to be based on identical matches! In this post, we look at the non-equi join, which uses ‘non-equal’ operators to match records. Read more An Illustrated Guide to the SQL OUTER JOIN An SQL JOIN clause links the data from two or more database tables. Let’s find out what is the OUTER JOIN and how does it work! Read more An Illustrated Guide to the SQL Self Join What is a SQL self join and how does it work? When should you use a self join in SQL? In this article, you’ll find answers to these questions! Read more An Illustrated Guide to the SQL OUTER JOIN An SQL JOIN clause links the data from two or more database tables. Let’s find out what is the OUTER JOIN and how does it work! Read more A Beginner’s Guide to SQL Aggregate Functions Aggregate functions are powerful SQL tools that can be used in conjunction with the GROUP BY statement. See how it work. Easy examples in LearnSQL.com Read more Introducing SQL Set Operators: Union, Union All, Minus, and Intersect Ever heard about union and intersection in SQL? They're set operators that come in handy when you need to combine information from multiple tables. 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 Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.