MariaDB Inner Join

Summary: in this tutorial, you will learn how to use the MariaDB inner join clause to query data from multiple tables.

Introduction to MariaDB inner join clause

The inner join clause is a type of joins that allows you to query data from multiple tables.

Suppose that you use the inner join clause to retrieve data from two tables t1 and t2; the following illustrates the syntax of the inner join:

select select_list
from t1
inner join t2 on join_condition;
Code language: SQL (Structured Query Language) (sql)

In this syntax, the inner join clause compares each row from the t1 table with every row from the t2 table. If a pair of rows causes the join_condition to evaluate to true, the inner join clause creates a new row whose columns are derived from rows in both tables and includes this row in the result set.

The following diagram illustrates the inner join operation:

Typically, you join tables by matching rows using the equality operator (=) and use primary key columns of the first table (t1) to match with foreign key columns of the second table (t2).

select select_list
from t1
inner join t2 on t1.column1 = t2.column1;
Code language: SQL (Structured Query Language) (sql)

If columns for equality matching have the same names, you can use the using syntax:

select select_list
from t1
inner join t2 using (column1);
Code language: SQL (Structured Query Language) (sql)

To join more three or more tables, you just need to add more inner join clauses as shown in the following query:

select select_list
from t1
inner join t2 on join_condition2
inner join t3 on join_condition2
...;
Code language: SQL (Structured Query Language) (sql)

MariaDB inner join clause examples

Let’s take some examples of using the inner join clause.

A) Using the MariaDB inner join to join two tables example

The following statement uses the inner join clause to retrieve data from the tables countries and regions:

select 
    c.name country, 
    r.name region
from countries c
inner join regions r 
    on r.region_id = c.region_id
order by c.name;
Code language: SQL (Structured Query Language) (sql)

In this example, the values in the region_id columns in both tables countries and regions are used for matching.

Because the column names for matching have the same name, you can use the using syntax:

select 
    c.name country, 
    r.name region
from 
    countries c
inner join regions r using (region_id)
order by 
    c.name;
Code language: SQL (Structured Query Language) (sql)

B) Using the MariaDB inner join to join three tables example

Consider the following three tables countries, regions, and continents from the sample database:

The following example uses the inner join clauses to retrieve data from three tables countries, regions, and continents:

select 
    c.name country, 
    r.name region,
    t.name continent
from 
    countries c
inner join regions r using (region_id)
inner join continents t using (continent_id)
order by 
    c.name;
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you will learn how to use the MariaDB inner join clause to query data from multiple tables.