• How to Create an Inner Join in SQL

      Join over 2 million students who advanced their careers with 365 Data Science. Learn from instructors who have worked at Meta, Spotify, Google, IKEA, Netflix, and Coca-Cola and master Python, SQL, Excel, machine learning, data analysis, AI fundamentals, and more.

      Start for Free
      Martin Ganchev 25 Apr 2023 11 min read

      How does an INNER JOIN in SQL work?

      If you want to learn how to create an inner join in SQL, you should have an idea what joins are in the first place. Furthermore, there are two tables that you should set up for this tutorial (see our SQL course fore more information).

      To illustrate the functionality of the inner join in SQL, we suggest you refer to the following graphics.

      Venn Diagrams

      Shown in the picture below is a Venn diagram – a mathematical tool representing all possible logical relations between a finite collection of sets.

      Venn diagram, inner join in sql

      The “dept_manager_dup” table is represented as the set on the left, while the “departments_dup” table is represented by the set, or the circle, on the right.

      Venn diagram. dept_manager_dup and departments_dup, inner join in sql

      Which will be the related column here? We know that we can join tables with columns of the same type and meaning. Therefore, the related column of these two tables will be the dept_no.

      Dept_no CHAR related column, inner join in sql

      Result Set

      The area that belongs to both circles, which is filled with red, represents all records belonging to both the “dept_manager_dup” and the “departments_dup” tables. This area is also called a result set.

      Result set in overlapping area, inner join in sql

      The inner join in SQL can help us extract this result set. With this type of join, we can match records of two or more tables. The same values appearing in the “dept_no” column in both tables are the matching values, also called matching records.

      Records matching, inner join in sql

      The values that do not match will not appear in our final output. Logically, they are called non-matching values, or non-matching records. records not matching, inner join in sql

      A Case in Point

      An example from the “employees” database can clarify the concept.

      The Department Manager Duplicate Table

      Let’s check the current state of the table on the left side of our graph, the “dept_manager_dup” table, by writing the following code:

      SELECT
    *
FROM
    dept_manager_dup
ORDER BY dept_no;

      Side note: We have ordered the output by “dept_no”, so it is easier to see which records contain data values in all fields and which don’t.

      Which records contain data and which don't, inner join in sql

      However, four records contain no information about “dept_no” and “to_date”. That’s why the values in that column appear as NULL. In addition, we lack data about department number 1.

      Some null values, inner join in sql

      Then, we have data about the departments from 2 to 9, though.

      The Departments Duplicate Table

      Let’s carry out the same analysis on the other table – the “departments_dup” table. We can write the same code, as a while ago, with a small change:

      SELECT
    *
FROM
    departments_dup
ORDER BY dept_no;

      public relations contains no department number, inner join in sql

      As you can see in the picture above, “Public Relations” contains no department number for some reason; therefore, we see “NULL” in the first column of the first row. Furthermore, we have data about the number and name of all departments from 1 to 11 with these exceptions:

      • There is nothing about department number 2 – neither a number nor a name.
      • Departments 10 and 11 do not have corresponding names.

      d010 and d011 do not have corresponding names, inner join in sql

      What is the Syntax of an Inner Join in SQL?

      To do this, we’ll need the join syntax, which is very intuitive. It serves to show columns from the different tables and specify which the related column will be.

      In a SELECT statement, write all columns you wish to see in the result. It is important to designate the tables in which the columns belong because the data is not in a single table this time.

      Select table one and two, inner join in sql

      That’s why, besides typing the keyword FROM and the name of the first table, you should proceed to write JOIN and the name of the second table.

      Relating the Tables

      To this moment, the syntax allows us to specify the fields we would like to see in the result and the tables we are matching.

      Relating the tables, inner join in sql

      But we haven’t specified how we would relate these tables.

      Let’s go ahead and type ON then quote the columns that relate the two tables. You can see how it is done in the picture below.

      ON table one and table 2, inner join in sql

      You are specifying where SQL will extract the matching values from.

      The code you see in the picture above is the main body of the syntax structure about writing joins.

      How to Use Aliases with Joins?

      Before we proceed with our example, let me share a fundamental coding practice that professionals use in the joins syntax – aliases. More precisely, I am talking about aliases of the tables’ names.

      table 2 AS t2, inner join in sql

      This means table_1 can be renamed to t1 and table_2 – to t2. When used for assigning table names, the aliases are usually added right after the original table name, without using the keyword “AS”. Then, instead of typing the entire tables’ names in the SELECT block, we can use t1 and t2, respectively.

      Change table 1 and 2 to t1 and 2, inner join in sql

      Now, let’s see how an inner join in SQL can be implemented with an example.

      How to Implement an Inner JOIN in SQL?

      Let’s apply a join syntax to the “dept_manager_dup” and “departments_dup” tables. By doing this, we will obtain a list of records containing information about the department number, the employee number, and the department name related to every manager.

      Apply an inner join syntax

      We can use the letters M and D as aliases for the two tables, respectively. Knowing their content, we can specify the fields we would like to see right after the SELECT keyword. Let’s extract the department and employee numbers from the “dept_manager_dup” table and the department name from the “departments_dup” table.

      Extract the department and employee numbers, inner join in sql

      We can achieve that by writing the following code:

      SELECT m.dept_no, m.emp_no, d.dept_name

      Joining the Tables

      Then, we must join the two tables.

      Important: While doing that, please don’t forget to add their aliases!

      So, we must type:

      SELECT 
    m.dept_no, m.emp_no, d.dept_name
FROM 
    dept_manager_dup m
        INNER JOIN
    departments_dup d

      The Columns Relating the Tables

      To conclude the join syntax, we must specify the columns relating the two tables. Let’s do that by typing :

      SELECT 
    m.dept_no, m.emp_no, d.dept_name
FROM 
    dept_manager_dup m
        INNER JOIN
    departments_dup d ON m.dept_no = d.dept_on;

      These are the “dept_no” field from the “dept_manager_dup” table and the “dept_no” field from the “departments_dup” table.

      The dept_no from the dept_manager_dup table and Departments_dup table

      Side note: The relating columns of the two tables need not be named in the same way. However, it helps avoid confusion and is more convenient if they are. That’s why, in our example, both fields carry the same name.

      Sorting the Output

      Before running this interesting query, let’s order the results according to their “dept_no”. This will improve the output’s representation. Since we obtain this column from the “dept_manager_dup” table, we will sort the information in the ORDER BY clause by the “dept_no” from the M table.

      Sorting the output

      The 20 rows of information that we obtained after executing the query are the result from the following code:

      SELECT
    m.dept_no, d.dept_name
FROM
    dept_manager_dup m
        INNER JOIN
    departments_dup d ON m.dept_no = d.dept_no
ORDER BY m.dept_no;

      The Retrieved Data

      We retrieved data about the departments from 3 to 9 only. There is no data about departments 1, 2, 10, or 11 or about the ones labelled as null values.

      Records that do not match do not show

      Why is this happening?

      Important: Inner joins only extract records where the values in the related columns match. Null values, or values appearing in just one of the two tables and not appearing in the other, are not displayed.

      Analysis

      The “dept_manager_dup” has a few null-valued department numbers and has no information about departments 1, 10, and 11. Therefore, inner joins do not present null values; the first four rows do not appear in the inner join we executed.

      inner joins do not present null values

      We have 26 records in this table altogether, and 26 - 4 = 22. Hence, there must be two records in the inner join we do not see because it only displays 20 rows!

      2 records in the inner join aren't seen

      Re-checking the Content of the “departments_dup” Table

      It has a null-valued department number, too. We don’t see it in the inner join output.

      departments dup has null value records too

      And why didn’t SQL match the null values of the first table with this null value here? Because it would make no sense. Only non-null matching values are in play.

      Moving further, department number 2 doesn’t participate in any record. Do we have it in the “dept_manager_dup” table?

      Yes, we do. There are two records with it in there.

      Moreover, these were the two records we did not see in the final output. There were no values from the “departments_dup” table to be matched with them in the inner join.

      SQL doesn't match null matching values

      What Else is Dropped from the Outcome

      Finally, the “departments_dup” table has 2 more records. They are departments 10 and 11, but as we mentioned, they have no values to be matched with from the “dept_manager_dup” table.

      2 records didn't match

      I hope this analysis makes it clearer and explains why we say an inner join is represented by the common area of the two circles in the Venn diagram. This area is namely the set of the matching values of both tables, which are the only ones displayed in the inner-join output.

      10 and 11 have no values to be matched

      And what if such matching values did not exist? Simply, the result set will be empty. There will be no link between the two tables.

      Understanding How to Use Inner Joins

      In conclusion, if you need to relate tables via a column they have in common, an inner join in SQL will do the job. If there is missing data, it must be a null value or it isn’t the same in both tables. Therefore, we used a Venn Diagram to make it easier to understand. So, if you are eager to learn several details about the syntax of joins in SQL, feel free to dive into the tutorial explaining this.

      ***

      Eager to hone your SQL skills? Learn how to put theory into practice with our hands-on tutorials!

      Next Tutorial: Joins Syntax in SQL

      Martin Ganchev

      Instructor at 365 Data Science

      Martin holds an MSc degree in Economic and Social Sciences from Bocconi University. His diverse academic and research experience combined with his friendly and explanatory approach to teaching have made him one of the most beloved instructors on our team. Some of the courses he has authored include: SQL, SQL + Tableau, SQL+Tableau+Python, Introduction to Python, Introduction to Jupyter, to name a few.

      We Think you'll also like

      Introducing the RIGHT JOIN in SQL

      SQL Tutorials

      Introducing the RIGHT JOIN in SQL

      Article by Martin Ganchev by Martin Ganchev 8 min read
      Learning How to Use the SQL SELF JOIN

      SQL Tutorials

      Learning How to Use the SQL SELF JOIN

      Article by Martin Ganchev by Martin Ganchev 9 min read
      Working with the LEFT JOIN in SQL

      SQL Tutorials

      Working with the LEFT JOIN in SQL

      Article by Martin Ganchev by Martin Ganchev 12 min read
      Data Manipulation: Integrating SQL and Tableau

      SQL Tutorials

      Data Manipulation: Integrating SQL and Tableau

      Article by Martin Ganchev by Martin Ganchev 5 min read
      Top