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 SQL SELECT statement. However, most of the time, you will need to query data from multiple tables to have a complete result set for analysis. To query data from multiple tables you use SQL JOIN statement.

There are several types of joins in SQL 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 SQL INNER JOIN works.

SQL INNER JOIN Syntax

The following illustrates SQL INNER JOIN syntax for joining 2 tables:

SELECT column1, column2...
FROM table_1
INNER JOIN table_2 ON join_condition

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 check with rows in the table_2 to find a match that satisfies the join_condition. If  a match found, the query returns the matching row that contains data (column1, column2,..) from both table_1 and table_2. If no match found, the query examines next row in the table_1 and this process continues until all rows in the table_1 are checked. If no match between table_1 and table_2 found, the query returns no row.

SQL INNER JOIN Examples

SQL INNER JOIN – querying data from two tables example

In this example, we will use 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 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:

SELECT productID,
       productName,
       categoryName
FROM products
INNER JOIN categories ON categories.categoryID = products.categoryID

How the query works.

For each row in the products table, the query finds a corresponding row in the categories table that has the same categoryid, which specify in the INNER JOIN clause after the ON keyword.

The expression:

categories.categoryID = products.categoryID

is the join condition.

If there is a match between two rows in both tables, SQL returns the matched row that contains columns specifying in the SELECT clause; otherwise, the query 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 checked.

SQL INNER JOIN – querying data from three tables

We can use the same idea for joining three tables and even more. The following query retrieves productID, productName, categoryName and supplier from 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.supplierID

Implicit SQL INNER JOIN

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

SELECT column1, column2,...
FROM table_1, table_2
WHERE join_condition

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:

SELECT productID,
       productName,
       categoryName
FROM products, categories
WHERE products.categoryID = categories.categoryID

Visualize SQL INNER JOIN using Venn diagram

We can use Venn diagram to illustrates the SQL INNER JOIN in a visual way. The SQL INNER JOIN returns all rows in table 1 (left table) that have matching rows in the table 2 (right table).

In this tutorial, you have learned how to use SQL INNER JOIN to select data from two or more tables based on join conditions.