MariaDB Left Join

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

Introduction to MariaDB left join clause

Suppose that you use the left join clause to retrieve data from two tables t1 and t2. Here is the syntax of the left join clause for joining two tables:

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

In this syntax, the left join starts selecting data from the left table t1. It compares each row from t1 with every row from t2.

If both rows cause the join condition to evaluate to true, the left join combines columns of rows from both tables into a new row and includes this row in the result set.

In case the rows are not matched, the left join still creates a new row whose columns are the combination of columns from both tables. However, it uses null values for the columns in the right table.

The following diagram illustrates the left join operation:

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

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

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

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

If you want to join three or more tables, you just need to add the left join clauses to the select statement:

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

MariaDB left join clause example

Consider the following tables countries and country_stats from the sample database.

This example finds the countries and their GDP by joining the countries table with the country_stats table using the left join clause:

select 
    name, 
    year, 
    gdp 
from 
    countries c
left join country_stats s on 
    s.country_id = c.country_id
order by 
    name;
Code language: SQL (Structured Query Language) (sql)

Here is an equivalent query that uses the using syntax:

select 
    name, 
    year, 
    gdp 
from 
    countries
left join country_stats 
    using (country_id) 
order by 
    name;
Code language: SQL (Structured Query Language) (sql)

To find countries that do not have GDP information, you use the following query:

select 
    name, 
    year, 
    gdp 
from 
    countries
left join country_stats using (country_id) 
where 
    gdp is null
order by 
    name;
Code language: SQL (Structured Query Language) (sql)

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