SQL INNER JOIN

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

In 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  INNER JOIN clause.

SQL INNER JOIN syntax

The following illustrates INNER JOIN syntax for joining two tables:

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 conditionIf the corresponding row found, the query returns a rows that contains data from both tables. Otherwise it examines next row in the table_1, and this process continues until all rows in the  table_1 are examined.

For joining more than two tables, the same logic 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.

products categories tables

In the diagram above:

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

Therefore, there is many-to-one relationship between the rows in the categories table and rows in the products table. The link between the two tables is  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:

SQL INNER JOIN - 2 tables example

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

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:

SQL INNER JOIN - 3 tables example

Implicit SQL INNER JOIN

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

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

Visualize INNER JOIN using Venn diagram

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

SQL Inner Join - Venn-Diagram
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.