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.
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.