# 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`: ```sql 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](https://www.ionos.com/digitalguide/hosting/technical-matters/mariadb-create-table/). This table includes the columns “Customer ID,” “LastName,” “FirstName,” “City,” and “CreationDate.” Here’s what the code looks like: ```sql 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`: ```sql INSERT INTO CustomerList VALUES (1, 'Smith', 'Martina', 'Los Angeles', '2022-07-19'), (2, 'Rodman', 'Daniel', 'Atlanta', '2023-03-03'), (3, 'Meyer', 'Peter', 'Chicago', '2023-07-09'), (4, 'Garcia', 'Laura', 'Phoenix', '2023-12-10'), (5, 'Davis', 'Lisa', 'Baltimore', '2024-01-17'); ``` We then create the “Orders” table. This contains the columns “OrderID”, “ProductID”, “CustomerName” and “OrderDate”. The code looks like this: ```sql 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: ```sql 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: ```sql 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:
Customer ID Customer Name Order ID Product ID
3 Meyer 102 332
2 Rodman 104 191
## `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: ```sql SELECT CustomerList.LastName, Orders.ProductID FROM CustomerList LEFT OUTER JOIN Orders ON CustomerList.LastName = Orders.CustomerName; ``` This gives us the following result:
Customer Name Product ID
Smith NULL
Rodman 191
Meyer 332
Garcia NULL
Davis NULL
## `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: ```sql SELECT CustomerList.LastName, Orders.ProductID FROM CustomerList RIGHT OUTER JOIN Orders ON CustomerList.LastName = Orders.CustomerName; ``` This is what the output looks like:
Customer Name Product ID
NULL 247
Meyer 332
NULL 247
Rodman 191
NULL 499
Tip In our Digital Guide you’ll find many useful tutorials and articles for MariaDB. For example, there’s a [comparison of MariaDB and MySQL](https://www.ionos.com/digitalguide/hosting/technical-matters/mariadb-vs-mysql/) a tutorial on [installing MariaDB](https://www.ionos.com/digitalguide/hosting/technical-matters/install-mysql-mariadb/) and instructions on [how to reset your MariaDB root password](https://www.ionos.com/digitalguide/hosting/technical-matters/reset-mysql-mariadb-root-password/). This is a markdown version of: [https://www.ionos.com/digitalguide/hosting/technical-matters/mariadb-join/](https://www.ionos.com/digitalguide/hosting/technical-matters/mariadb-join/) for AI/LLM consumption.