SQL INNER JOIN

Summary: in this tutorial, you will learn how to query data from multiple tables using SQL INNER JOIN statement.

In the previous tutorial, you learned how to query data from a single table using the SELECT statement. However, you often want to query data from multiple tables to have a complete result set for analysis. To query data from multiple tables you use join statements.

SQL provides several types of joins such as inner join, outer joins ( left outer join or left join, right outer join or right join, and full outer join) and self join. In this tutorial, we will show you how to use the INNER JOIN clause.

SQL INNER JOIN syntax

The following illustrates INNER JOIN syntax for joining two tables:

SELECT 
    column1, column2
FROM
    table_1
INNER JOIN table_2 ON join_condition;Code language: SQL (Structured Query Language) (sql)

Let’s examine the syntax above in greater detail:

• The  table_1 and  table_2 are called joined-tables.
• For each row in the  table_1, the query find the corresponding row in the  table_2 that meet the join condition. If the corresponding row found, the query returns a row that contains data from both tables. Otherwise, it examines next row in the table_1, and this process continues until all the rows in the  table_1 are examined.

For joining more than two tables, the same logic was applied.

SQL INNER JOIN examples

SQL INNER JOIN – querying data from two tables example

In this example, we will use the products and categories tables in the sample database. The following picture illustrates the database diagram.

In the diagram above:

• One category can have many products.
• One product belongs to one and only one category.

Therefore, there is a many-to-one relationship between the rows in the categories table and rows in the products table. The link between the two tables is the categoryid column.

We need to query the following data from both tables:

• productID, productName from the products table.
• categoryName from the categories table.

The following query retrieves data from both tables:

SELECT 
    productID, productName, categoryName
FROM
    products
INNER JOIN 
    categories ON categories.categoryID = products.categoryID;Code language: SQL (Structured Query Language) (sql)

The join condition is specified in the INNER JOIN clause after the ON keyword as the expression:

categories.categoryID = products.categoryIDCode language: SQL (Structured Query Language) (sql)

For each row in the products table, the query finds a corresponding row in the categories table that has the same categoryid. If there is a match between two rows in both tables, it returns a row that contains columns specified in the SELECT clause i.e., product id, product name, and category name; otherwise, it checks the next row in products table to find the matching row in the categories table. This process continues until the last row of the products table is examined.

SQL INNER JOIN – querying data from three tables

We can use the same techniques for joining three tables. The following query selects productID, productName, categoryName and supplier from the products, categories and suppliers tables:

SELECT 
    productID,
    productName,
    categoryName,
    companyName AS supplier
FROM
    products
INNER JOIN
    categories ON categories.categoryID = products.categoryID
INNER JOIN
    suppliers ON suppliers.supplierID = products.supplierIDCode language: SQL (Structured Query Language) (sql)

Implicit SQL INNER JOIN

There is another form of the  INNER JOIN called implicit inner join as shown below:

SELECT 
    column1, column2
FROM
    table_1,
    table_2
WHERE
    join_condition;Code language: SQL (Structured Query Language) (sql)

In this form, you specify all joined tables in the FROM clause and put the join condition in the WHERE clause of the SELECT statement. We can rewrite the query example above using the implicit  INNER JOIN as follows:

SELECT 
    productID, productName, categoryName
FROM
    products,
    categories
WHERE
    products.categoryID = categories.categoryID;Code language: SQL (Structured Query Language) (sql)

Visualize INNER JOIN using Venn diagram

We can use the Venn diagram to illustrate how the INNER JOIN works. The SQL INNER JOIN returns all rows in table 1 (left table) that have corresponding rows in table 2 (right table).

In this tutorial, we have shown you how to use the SQL INNER JOIN clause to select data from two or more tables based on a specified join condition.