MariaDB: Joins

This MariaDB tutorial explains how to use MariaDB JOINS (inner and outer) with syntax, visual illustrations, and examples.

Description

MariaDB JOINS are used to retrieve data from multiple tables. A MariaDB JOIN is performed whenever two or more tables are joined in a SQL statement.

There are different types of MariaDB joins:

• MariaDB INNER JOIN (or sometimes called simple join)
• MariaDB LEFT OUTER JOIN (or sometimes called LEFT JOIN)
• MariaDB RIGHT OUTER JOIN (or sometimes called RIGHT JOIN)

So let's discuss MariaDB JOIN syntax, look at visual illustrations of MariaDB JOINS, and explore MariaDB JOIN examples.

INNER JOIN (simple join)

Chances are, you've already written a statement that uses a MariaDB INNER JOIN. It is the most common type of join. MariaDB INNER JOINS return all rows from multiple tables where the join condition is met.

Syntax

The syntax for the INNER JOIN in MariaDB is:

SELECT columns
FROM table1 
INNER JOIN table2
ON table1.column = table2.column;

Visual Illustration

In this visual diagram, the MariaDB INNER JOIN returns the shaded area:

The MariaDB INNER JOIN would return the records where table1 and table2 intersect.

Example

Here is an example of a MariaDB INNER JOIN:

SELECT sites.site_id, sites.site_name, pages.page_title
FROM sites 
INNER JOIN pages
ON sites.site_id = pages.site_id;

This MariaDB INNER JOIN example would return all rows from the sites and pages tables where there is a matching site_id value in both the sites and pages tables.

Let's look at some data to explain how the INNER JOINS work:

We have a table called sites with two fields (site_id and site_name). It contains the following data:

We have another table called pages with three fields (page_id, site_id, and file_size). It contains the following data:

If we run the MariaDB SELECT statement (that contains an INNER JOIN) below:

SELECT sites.site_id, sites.site_name, pages.page_id, pages.page_title
FROM sites 
INNER JOIN pages
ON sites.site_id = pages.site_id;

Our result set would look like this:

The row for Google.com from the sites table would be omitted, since the site_id of 5000 does not exist in both tables. The row for 4 (page_id) from the pages table would be omitted, since the site_id of 5000 does not exist in the sites table.

Old Syntax

As a final note, it is worth mentioning that the MariaDB INNER JOIN example above could be rewritten using the older implicit syntax as follows (but we still recommend using the INNER JOIN keyword syntax):

SELECT sites.site_id, sites.site_name, pages.page_id, pages.page_title
FROM sites, pages
WHERE sites.site_id = pages.site_id;

LEFT OUTER JOIN

Another type of join is called a MariaDB LEFT OUTER JOIN. This type of join returns all rows from the LEFT-hand table specified in the ON condition and only those rows from the other table where the joined fields are equal (join condition is met).

Syntax

The syntax for the MariaDB LEFT OUTER JOIN is:

SELECT columns
FROM table1
LEFT [OUTER] JOIN table2
ON table1.column = table2.column;

In some databases, the LEFT OUTER JOIN keywords are replaced with LEFT JOIN.

Visual Illustration

In this visual diagram, the MariaDB LEFT OUTER JOIN returns the shaded area:

The MariaDB LEFT OUTER JOIN would return the all records from table1 and only those records from table2 that intersect with table1.

Example

Here is an example of a MariaDB LEFT OUTER JOIN:

SELECT sites.site_id, sites.site_name, pages.page_id, pages.page_title
FROM sites 
LEFT JOIN pages
ON sites.site_id = pages.site_id;

This LEFT OUTER JOIN example would return all rows from the sites table and only those rows from the pages table where the joined fields are equal.

If a site_id value in the sites table does not exist in the pages table, all fields in the pages table will display as <null> in the result set.

Let's look at some data to explain how LEFT OUTER JOINS work:

We have a table called sites with two fields (site_id and site_name). It contains the following data:

We have a second table called pages with three fields (page_id, site_id, and page_title). It contains the following data:

If we run the SELECT statement (that contains a LEFT OUTER JOIN) below:

SELECT sites.site_id, sites.site_name, pages.page_id, pages.page_title
FROM sites 
LEFT JOIN pages
ON sites.site_id = pages.site_id;

Our result set would look like this:

The rows for Google.com would be included because a LEFT OUTER JOIN was used. However, you will notice that the page_id and page_title fields for those records contains a <null> value.

RIGHT OUTER JOIN

Another type of join is called a MariaDB RIGHT OUTER JOIN. This type of join returns all rows from the RIGHT-hand table specified in the ON condition and only those rows from the other table where the joined fields are equal (join condition is met).

Syntax

The syntax for the MariaDB RIGHT OUTER JOIN is:

SELECT columns
FROM table1
RIGHT [OUTER] JOIN table2
ON table1.column = table2.column;

In some databases, the RIGHT OUTER JOIN keywords are replaced with RIGHT JOIN.

Visual Illustration

In this visual diagram, the MariaDB RIGHT OUTER JOIN returns the shaded area:

The MariaDB RIGHT OUTER JOIN would return the all records from table2 and only those records from table1 that intersect with table2.

Example

Here is an example of a MariaDB RIGHT OUTER JOIN:

SELECT sites.site_id, sites.site_name, pages.page_id, pages.page_title
FROM sites 
RIGHT JOIN pages
ON sites.site_id = pages.site_id;

This RIGHT OUTER JOIN example would return all rows from the pages table and only those rows from the sites table where the joined fields are equal.

If a site_id value in the pages table does not exist in the sites table, all fields in the sites table will display as <null> in the result set.

Let's look at some data to explain how RIGHT OUTER JOINS work:

We have a table called sites with two fields (site_id and site_name). It contains the following data:

We have a second table called pages with three fields (page_id, site_id, and page_title). It contains the following data:

If we run the SELECT statement (that contains a RIGHT OUTER JOIN) below:

SELECT sites.site_id, sites.site_name, pages.page_id, pages.page_title
FROM sites 
RIGHT JOIN pages
ON sites.site_id = pages.site_id;

Our result set would look like this:

The row for 5 (page_id) would be included because a RIGHT OUTER JOIN was used. However, you will notice that the site_id and site_name fields for that record contains a <null> value.