MariaDB Join

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

So far, you have learned how to retrieve data from a single table using a simple select statement.

To select data from multiple related tables, you use the select statement with join clauses. The join clauses associate the rows in one table with the rows in another table based on a specified condition.

MariaDB supports various kinds of joins such as inner join, left join, right join, and cross join.

Note that MariaDB doesn’t support the full outer join or full join.

To demonstrate join clauses, we will use the guests and vips tables from the sample database:

The guests table:

select * from guests;Code language: SQL (Structured Query Language) (sql)

The vips table:

select * from vips;Code language: SQL (Structured Query Language) (sql)

MariaDB inner join clause

Suppose that you want to join two tables t1 and t2. The following shows the syntax of the inner join clause that joins the two tables:

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

The inner join compares each row from the table t1 with every row from the table t2.

If values of both rows cause the join condition to evaluate to true, the inner join creates a new row with columns that contain columns, from both rows, specified by the select list, and includes this new row in the result set.

Typically, you match rows from the table t1 with rows from the table t2 using the equal operator (=):

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

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

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

The following example uses the inner join to find people that appear on both tables guests and vips:

select 
    g.guest_id, 
    g.name, 
    v.vip_id, 
    v.name
from guests g
inner join vips v 
    on v.name = g.name;
Code language: SQL (Structured Query Language) (sql)

Since the column for matching rows in both tables vips and guests have the same name, you can use the using clause syntax as follows:

select 
    g.guest_id, 
    g.name, 
    v.vip_id, 
    v.name
from guests g
inner join vips v using (name);
Code language: SQL (Structured Query Language) (sql)

The following picture illustrates the inner join operation:

MariaDB left join clause

The left join introduces the concepts of left and right tables.

Support that t1 and t2 are the left and right tables respectively.

The following illustrates the syntax of the left join:

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

Similar to the inner join, if you use the equal operator (=) to match rows and the names of columns used for matching are the same, you can use the using clause syntax:

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

The left join retrieves data starting from the left table. It compares each row from the left table (t1) with every row from the right table (t2). If both rows cause the join condition to evaluate to true, the left join creates a new row whose columns include columns of both rows specified by the select list.

If left join doesn’t find any matching row in the right table, it still creates a new row whose columns include columns of the row from the left table and null values for columns of the row from the right table.

Simply put, the left join retrieves all rows from the left table (t1) whether they have matching rows from the right table or not.

The following example uses the left join to join the guests table with the vips table:

select 
    g.guest_id, 
    g.name, 
    v.vip_id, 
    v.name
from guests g
left join vips v 
    on v.name = g.name;
Code language: SQL (Structured Query Language) (sql)

The following query uses the using syntax:

select 
    g.guest_id, 
    g.name, 
    v.vip_id, 
    v.name
from guests g
left join vips v
    using(name);
Code language: SQL (Structured Query Language) (sql)

The following picture illustrates the left join:

MariaDB right join clause

The right join is just a reversed version of the left join:

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

For example, the following statement uses the right join clause to join the guests with the vips tables:

select 
    g.guest_id, 
    g.name, 
    v.vip_id, 
    v.name
from guests g
right join vips v 
    on v.name = g.name;	
Code language: SQL (Structured Query Language) (sql)

The following picture illustrates the right join operation:

MariaDB cross join clause

The cross join makes a Cartesian product of rows from the joined tables.

Unlike the inner join, left join, and right join, the cross join doesn’t have a join predicate:

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

In this syntax, the cross join combines each row from t1 table with every row from the t2 table to form the result set.

If t1 has n rows, t2 has m rows, the cross join of t1 and t2 will result in nxm rows.

The following example uses the cross join to join the guests with vips table:

select 
    g.guest_id, 
    g.name, 
    v.vip_id, 
    v.name
from guests g
cross join vips v;
Code language: SQL (Structured Query Language) (sql)

It returns 25 rows (5×5):

In this tutorial, you have learned how to use MariaDB join clause to select data from multiple tables.