How to query and merge records using MariaDB JOIN

In a relational database management system, you can compare data records in different tables with each other. This makes it possible to create connections and extract matching values from two different tables. This task is performed with JOIN in MariaDB. The statement is used in combination with SELECT and can be divided into different categories. We’ll present INNER JOIN, LEFT OUTER JOIN and RIGHT OUTER JOIN in more detail below.

What’s the syntax for JOIN?

To help you understand the various JOIN statements in MariaDB, we’ll start by presenting the basic syntax of the statement. Here’s the syntax for INNER JOIN:

SELECT column
FROM table_1
INNER JOIN table_2
ON table_1.column = table_2.column;

Using SELECT, you specify the column (or columns) you want to include. Replace the placeholder ‘table_1’ with the first table and ‘table_2’ with the second table that you want to join with the first. The INNER JOIN operation compares each row in the first table with each row in the second table. Only matching records (those present in both tables) will be displayed in the result table. Non-matching records are excluded from the output.

How to use INNER JOIN in MariaDB

To demonstrate how INNER JOIN works in MariaDB, let’s take a look at a straightforward example. We’ll use a database that has two tables. The first table is named ‘Customer List’, and the second is ‘Orders’. We can create the ‘CustomerList’ table using MariaDB CREATE TABLE. This table includes the columns ‘Customer ID’, ‘LastName’, ‘FirstName’, ‘City’, and ‘CreationDate’. Here’s what the code looks like:

CREATE TABLE CustomerList (
	CustomerID INT PRIMARY KEY,
	LastName VARCHAR(50),
	FirstName VARCHAR(50),
	City VARCHAR(50),
	CreationDate DATE
);

Now, let’s fill this table with some values. To do this, we use INSERT INTO:

INSERT INTO CustomerList VALUES
(1, 'Smith', 'Martina', 'London', '2022-07-19'),
(2, 'Rodman', 'Daniel', 'Aberdeen', '2023-03-03'),
(3, 'Meyer', 'Peter', 'Cardiff', '2023-07-09'),
(4, 'Garcia', 'Laura', 'Preston', '2023-12-10'),
(5, 'Davis', 'Lisa', 'Bournemouth', '2024-01-17');

We then create the ‘Orders’ table. This contains the columns ‘OrderID’, ‘ProductID’, ‘CustomerName’ and ‘OrderDate’. The code looks like this:

CREATE TABLE Orders (
	OrderID INT AUTO_INCREMENT PRIMARY KEY,
	ProductID INT,
	CustomerName VARCHAR(50),
	OrderDate DATE
);

We’re also going to fill this table with sample values:

INSERT INTO Orders VALUES
(101, 247, 'Jackson', '2024-02-20'),
(102, 332, 'Meyer', '2024-03-03'),
(103, 247, 'Anderson', '2024-03-09'),
(104, 191, 'Rodman', '2024-03-17'),
(105, 499, 'Martinez', '2024-03-17');

Now, we’ll use INNER JOIN for MariaDB to filter for customers who appear in the customer list and have placed an order listed in the Orders table. The corresponding code looks like this:

SELECT CustomerList.CustomerID, CustomerList.LastName, Orders.OrderID, Orders.ProductID
FROM CustomerList
INNER JOIN Orders
ON CustomerList.LastName = Orders.CustomerName;

In this example, we focus on the last name in the customer list and the customer name in the orders. When these values match, they are included in the results. Since the customers Meyer and Rodman appear in both tables, the output would look like this:

LEFT OUTER JOIN

LEFT OUTER JOIN in MariaDB works according to a similar principle and also uses an almost identical syntax. In contrast to INNER JOIN, however, all data records from the first or left table (in our example ‘CustomerList’) are output and only the matching data records from the second or right table (‘orders’). If there is no equivalent in the second table, the value is specified as NULL. Using the example from above, this is what the code looks like:

SELECT CustomerList.LastName, Orders.ProductID
FROM CustomerList
LEFT OUTER JOIN Orders
ON CustomerList.LastName = Orders.CustomerName;

This gives us the following result:

RIGHT OUTER JOIN

In MariaDB, RIGHT OUTER JOIN operates in the opposite manner. Here, data from the second or right table is combined with matching values from the first or left table. If there is no match, the resulting value will be NULL. Below is the code:

SELECT CustomerList.LastName, Orders.ProductID
FROM CustomerList
RIGHT OUTER JOIN Orders
ON CustomerList.LastName = Orders.CustomerName;

This is what the output looks like: