Back to articles list December 23, 2020 - 6 minutes read What Is an 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: sql learn sql join How do you combine data from different tables in one query? An SQL feature called JOIN is the most common operator used to create complex queries. Learn the basics in this article. SQL allows us to select data from more than one table. In fact, the whole idea of using relational databases is selecting data from related tables. And we can use SQL’s JOIN operator to do this. There are many types of JOINs in SQL. In this article, we’ll focus on INNER JOIN, which is the default JOIN command (i.e. the one you get if you use the JOIN keyword by itself). When Do You Need JOIN? Why would we need to join data from several tables? The simple answer is that sometimes one table doesn’t contain all the data you need. Imagine that the movie table stores information in the columns id, title, production_year, and director_id. idtitleproduction_yeardirector_id 1Psycho19601 2Midnight in Paris20112 3Sweet and Lowdown19932 4Talk to her20023 5The skin I live here20113 Another table, director, contains information about directors: their id, name, and birth_year. idnamebirth_year 1Alfred Hitchcock1899 2Woody Allen1935 3Pedro Almodóvar1949 Notice that each movie has only one column related to the director: director_id. The same ID number values in the table director (in the director_id column) and in the table movie (in the id column). We’d like to display each movie’s data together with the details of its director. The result is a new table with data from two tables: movie and director. The tables are combined (or joined) based on the directors’ ID numbers. In other words, movies with a specific director_id are combined with the information of the director with this ID – e.g. the movie Psycho with director_id=1 is matched by the record where id=1 (Alfred Hitchcock) in the director table. Look at the result set: idtitleproduction_yeardirector_ididnamebirth_year 1Psycho196011Alfred Hitchcock1899 2Midnight in Paris201122Woody Allen1935 3Sweet and Lowdown199322Woody Allen1935 4Talk to her200233Pedro Almodóvar1949 5The skin I live here201133Pedro Almodóvar1949 An illustrated version of a similar example is presented in the article An Illustrated Guide to the SQL INNER JOIN. How to Use JOIN in an SQL Query Now, let’s analyze the query we just used. Here it is again: SELECT * FROM movie JOIN director ON movie.director_id=director.id; JOIN is a shorter form of the INNER JOIN clause; you can use them interchangeably. After you SELECT your columns, you put the FROM keyword and the name of the first table (in this example, movie). Then you put JOIN (or INNER JOIN), followed by the name of the second table (here, director). Next is the ON keyword and the join condition (i.e. how to match records from both tables). In this case, the join condition matches the values in the column director_id in the table movie with the id column in the table director. In this way, records in the table movie are matched to the correct records in director. Now we know that the movie Sweet and Lowdown was directed by the director Woody Allen, who was born in 1935. This movie has director_id=2; there is a matching value to the corresponding column (id) in the table director. The animation below shows how using a JOIN operator works and which records are returned: A JOIN operator works logically, in two steps. The first step returns a Cartesian product, i.e. each row in the first table is combined with each row in the second table. In the next step, only the pairs of records which meet the condition in the ON clause are returned – in this example, only rows where director_id in movie equals id in director. Note that the values used in the join condition don’t have to be numbers. You can also use text values and other more complex conditions. You can find more information in the article SQL INNER JOIN Explained in Simple Words. What If Records Don’t Match? What if there are records in the first table that cannot be matched to records in the second table, or vice versa? How does the INNER JOIN work in this case? Let’s look at an example. Here is the movie table again: idtitleproduction_yeardirector_id 1Psycho19601 2Midnight in Paris20112 3Sweet and Lowdown19932 4Talk to her20023 5The skin I live in20113 6The new house2020NULL And here is director: idnamebirth_year 1Alfred Hitchcock1899 2Woody Allen1935 3Pedro Almodóvar1949 4Martin Scorsese1942 Here is the same query: SELECT title, production_year, director_id, id, name, birth_year FROM movie JOIN director ON movie.director_id=director.id; The result table contains only records that are a matching pair: idtitleproduction_yeardirector_ididnamebirth_year 1Psycho196011Alfred Hitchcock1899 2Midnight in Paris201122Woody Allen1935 3Sweet and Lowdown199322Woody Allen1935 4Talk to her200233Pedro Almodóvar1949 5The skin I live in201133Pedro Almodóvar1949 If you look at the table movie, the column director_id is NULL for the movie The new house. And in the table director, the director Martin Scorsese (id=4) is not related to any row in the table movie. Therefore, these rows are not displayed in the result set. The rule for INNER JOIN (JOIN) is that only rows with matching in both tables are returned. In the other types of JOIN operators this behavior is different. Extend this topic reading the article SQL JOIN Types Explained. Filtering Records in the Result Set You can also filter records returned by JOIN. For example, we can list only those movies and their directors where the director was born after 1940. Here’s the query: SELECT title, production_year, director_id, id, name, birth_year FROM movie JOIN director ON movie.director_id=director.id WHERE director.birth_year>1940; It returns the result: idtitleproduction_yeardirector_ididnamebirth_year 4Talk to her200233Pedro Almodóvar1949 5The skin I live in201133Pedro Almodóvar1949 Once again, we have rows from the table movie combined with rows from the table director. Initially, the join condition is the same (ON director.id=movie.director_id). However, in the next step, records with a value less than or equal to 1940 in the column birth_year are removed. Thus, you don’t find Woody Allen in the result set because he was born in 1935 (before/less than 1940). Alfred Hitchcock is not here either; he was born in 1899. We did this thanks to WHERE director.birth_year>1940. Want to Learn More About SQL JOINs? If you would like to extend your skills, try our SQL Basics course, which contains more about joining tables (including lots of practice exercises!). It is part of the track SQL Fundamentals, which will help you get your SQL knowledge to the next level. Or maybe you already know some SQL and you’d like to learn more about JOINs. In that case, I suggest you look at How to Learn SQL JOINs, SQL JOIN Types Explained, and our SQL JOIN Cheat Sheet. You can also take our SQL JOINs course. Then go practice what you know using our SQL Practice online learning path. Happy learning! Tags: sql learn sql join You may also like SQL JOIN Types Explained Learn how to join tables in SQL. Review the different SQL join types and when to use inner join, left join, right join, or full join. Read more How to Learn SQL JOINs Learn how to use SQL JOINs to effectively combine data across multiple tables and analyze sophisticated data sets. Read more How to Join the Same Table Twice Learn when and why you need to join the same table twice in SQL, including when you should use self joins. Read more SQL JOIN Cheat Sheet This 2-page SQL JOIN Cheat Sheet covers the syntax of different JOINs (even the rare ones!) Download it in PDF or PNG format. Read more How to Join 3 Tables (or More) in SQL Have you ever wondered how to join three tables in SQL? It's easy when you know the basics. Joining three tables can be as easy as joining two tables. Read more What's the Difference Between Having Multiple Tables in FROM and Using JOIN? What’s your approach to SQL JOINs? Do you use JOIN or simply list tables in FROM? Learn why SQL practitioners prefer the new syntax with the JOIN keyword. Read more Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.