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;
Let’s examine the syntax above in greater detail:
table_2are called joined-tables.
- For each row in the
table_1, the query find the corresponding row in the
table_2that 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
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
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
We need to query the following data from both tables:
The following query retrieves data from both tables:
SELECT productID, productName, categoryName FROM products INNER JOIN categories ON categories.categoryID = products.categoryID;
The join condition is specified in the
INNER JOIN clause after the
ON keyword as the expression:
categories.categoryID = products.categoryID
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
supplier from the
Code language: PHP (php)
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
INNER JOIN called implicit inner join as shown 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 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 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.