login

Subscribe to SQL Tutorial websiteRSS Feed
 » SQL Tutorial » SQL INNER JOIN

Getting data from multiple tables using SQL INNER JOIN

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

In the previous tutorial, you learned how to retrieve data from a single table using SQL SELECT statement. However, most of the times you will need to retrieve data from multiple tables to have a complete data for analysis. SQL provides you JOIN statement to help you accomplish this. There are several types of joins such as inner join, outer join, right join, left join and self join. In this tutorial, we will show you how INNER JOIN works.

Using INNER JOIN to get data from two tables

We will use a couple of brief examples to illustrate the basic ideas. The following examples use the PRODUCTS and CATEGORIES table in our sample database. Here is the database diagram demonstrating table relationship.

SQL Inner Join

  • One category have multiple products
  • One product belongs to one and only one category

Therefore, the cardinality is one-to-many relation between CATEGORIES table and PRODUCTS table. The link between CATEGORIES and PRODUCTS table is categoryid. The requirement is combining data from both tables and give the following information:

  • productid, productname  from PRODUCTS table
  • categoryname from CATEGORIES table

The query to get data from both tables as follows:

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

For each product record in the PRODUCTS table, SQL finds a corresponding category record in the CATEGORIES table that has the same categoryid specifying in the INNER JOIN clause after ON keyword. This expression is also called join condition. If there is a match, SQL returns the match record; otherwise, it checks another record in product table. This process continues until the last record of the products table is checked.

SQL INNER JOIN Example

We can generalize our query above for the general INNER JOIN syntax as below:

SELECT selection_list
FROM table_A
INNER JOIN table_B ON join_condition
WHERE row_conditions.

In the query above:

  • TABLE_A and TABLE_B are known as joined-tables.
  • The INNER JOIN gets all the records from the TABLE_A and finds the matching records in the TABLE_B according to the join condition.
  • The join condition determines if record from TABLE_A matches with record in TABLE_B. If there is no match found, no record returns.

Implicit INNER JOIN

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

SELECT selection_list
FROM table_A, table_B
WHERE join_condition

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

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

Using INNER JOIN to get data from three tables

We can use the same ideas for joining three tables and even more. We can get productid, productname, categoryname and supplier using the following query:

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

SQL INNER JOIN - 3 Tables Example

Visualize INNER JOIN using Venn diagram

The INNER JOIN returns all records in table A (left table) that have a matching record in the table B (right table).

SQL INNER JOIN - Venn Diagram


In this tutorial, you have learned how to use INNER JOIN to get data from two or more tables using join conditions.