INNER JOIN

The INNER JOIN matches each row in one table with every row in other tables and allows you to query rows that contain columns from both tables.

The INNER JOIN is an optional clause of the SELECT statement. It appears immediately after the FROM clause. Here is the syntax of the INNER JOIN clause:

SELECT
    select_list
FROM t1
INNER JOIN t2 ON join_condition1
INNER JOIN t3 ON join_condition2
...;

In this syntax:

• First, specify the main table that appears in the FROM clause (t1).

• Second, specify the table that will be joined with the main table, which appears in the INNER JOIN clause (t2, t3,…).

• Third, specify a join condition after the ON keyword of the INNER JOIN clause. The join condition specifies the rule for matching rows between the main table and the table appeared in the INNER JOIN clause.

Assuming that you want to join two tables t1 and t2.

The following statement illustrates how to join two tables t1 and t2 using the INNER JOIN clause:

SELECT 
     select_list
FROM 
     t1
INNER JOIN t2 ON join_condition;

The INNER JOIN clause compares each row in the t1 table with every row in the t2 table based on the join condition.

If rows from both tables cause the join condition to evaluate to TRUE, the INNER JOIN creates a new row whose columns contain all columns of rows from the tables and includes this new row in the result set. Otherwise, the INNER JOIN just ignores the rows.

In case no row between tables causes the join condition to evaluate to TRUE, the INNER JOIN returns an empty result set. This logic is also applied when you join more than 2 tables.

The following Venn diagram illustrates how the INNER JOIN clause works:

MySQL INNER JOIN Venn Diagram

MySQL INNER JOIN examples

Let’s look at the products and productlines tables in the sample database.

products productlines tables

In this diagram, the table products has the column productLine that references the column productline of the table productlines . The column productLine in the table products is called the foreign key column.

Typically, you join tables that have foreign key relationships like the productlines and products tables.

Suppose you want to get:

• The productCode and productName from the products table.

• The textDescription of product lines from the productlines table.

To do this, you need to select data from both tables by matching rows based on values in the productline column using the INNER JOIN clause as follows:

SELECT 
    productCode, 
    productName, 
    textDescription
FROM
    products t1
INNER JOIN productlines t2 
    ON t1.productline = t2.productline;