MariaDB Join with Examples

In this MariaDB tutorial, we will learn about the “MariaDB Join” where we will extract the information by combining the multiple tables and cover the following topics.

• MariaDB Join
• MariaDB Join 3 tables
• MariaDB Join update
• MariaDB Join vs inner join
• MariaDB Join delete
• MariaDB Join vs left join
• MariaDB Join string
• MariaDB Join subquery
• MariaDB join_buffer_size
• MariaDB join_buffer_limit
• MariaDB join buffer
• MariaDB Join limit
• MariaDB left Join order by
• MariaDB Join group by
• MariaDB Join order by
• MariaDB Join distinct
• MariaDB left join count
• MariaDB full join
• MariaDB Join foreign key
• MariaDB Join on concat
• MariaDB bnl join
• MariaDB join across database
• MariaDB update join group by

MariaDB Join

In MariaDB, JOIN is used to merge the rows from more than one table based on common columns in tables. In other words, The data is extracted from more than one table into a single table using the JOIN clause.

• The JOIN clause can be used when there are two or more than two tables with common columns.

There are four types of JOIN in MariaDB:

• INNER JOIN: It is a simple JOIN that retrieves all the rows from more than one table where the JOIN condition is True.
• LEFT JOIN: It is a LEFT OUTER JOIN that retrieves all the rows from the left table based on whatever is specified in the ON condition and returns those rows from another table where the JOIN condition is True.
• RIGHT JOIN: It is a RIGHT OUTER JOIN that retrieves all the rows from the right table based on whatever is specified in the ON condition and returns those rows from another table where the JOIN condition is True.
• CROSS JOIN: It returns the result set where each row in a table is joined with each row in another table.

The syntax is given below for all kinds of JOIN.

SELECT column_list FROM table_1
JOIN table_2 ON table_1.col = table_2.col

Where,

• column_name: It is can be the list of columns from more than one table like table_1 and table_2.
• JOIN: It is a clause to join the tables and can be any of the JOIN like LEFT, RIGHT and CROSS JOIN.
• ON table_1.col = table_2.col : It is condition on which tables are joined when it is met. Where table_q.col and table_2.col is common column of the table_1 and table_2 respectively.

Let’s take an example by creating two different tables.

CREATE TABLE Employee(id INT,emp_name VARCHAR(30));

• CREATE TABLE: It is the command to create a new table here we are creating the table as Employee.
• id INT: It is the column of the table Employee of type integer that will contain the id of each employee.
• emp_name VARCHAR(30): It is the column of the table Employee of a type character that will contain the name of each employee.

Insert the following records in that table.

INSERT INTO Employee(id,emp_name)VALUES(1,'Finley Morris'),(2,'Luke Nicholson'),(3,'Joel Bradley'),(4,'Charlie Pearce');

After inserting the four records in a table, view the table Employee.

Create the second table as Employee_Dept using the below code.

CREATE TABLE Employee_Dept(dept_id INT,emp_department VARCHAR(40));

Where,

• CREATE TABLE: It is the command to create a new table here we are creating the table Employee_Dept.
• dept_id INT: It is the column of table Employee_Dept of type integer to store the id of each employee department.
• emp_department VARCHAR(40): It is the column of table Employee_Dept of a type character to store the department of each employee.

Insert the following record in the second table.

INSERT INTO Employee_Dept(dept_id,emp_department)VALUES(1,'Kitchen Staff'),(2,'Managerial Staff'),(3,'Floor Staff'),
(4,'Bar Tenders'),(5,'Delivery Staff');

After inserting the five records, the records are the department of the restaurant and view the table Employee_Dept.

We have created the two tables Employee and Employee_Dept, the output of the table is given above or what kind of data contains in a table.

Now, perform the simple join operation using the below code.

SELECT emp_name,emp_department FROM Employee AS emp
JOIN Employee_Dept AS edpt ON emp.id = edpt.dept_id;

In the above code, we are selecting the columns emp_name and emp_department from both tables. Then joining the second table Employee_Dept as edpt on the columns emp.id and edgpt.dept_id using the equal operator (=).

The result of the above code is given below.

From the output, we can see that the JOIN clause shows the common rows in both tables based on the specified condition.

Take another example using the RIGHT JOIN.

SELECT emp_name,emp_department FROM employee AS emp
RIGHT JOIN Employee_Dept AS edpt ON emp.id = edpt.dept_id;

Here, we are doing the same thing as we did in the above code instead of using the simple JOIN, we are using here the RIGHT JOIN clause. After running the above code we will see the difference.

As we can see in the output, the RIGHT JOIN shows all the rows from the right table (Employee_Dept) and matched rows from the left table (Employee).

Let’s do one more example using the CROSS JOIN.

SELECT emp_name,emp_department FROM employee AS emp
CROSS JOIN Employee_Dept AS edpt ON emp.id = edpt.dept_id;

Here, we are doing the same thing as we did in the above code instead of using the simple RIGHT JOIN, we are using here CROSS JOIN clause. After running the above code we will see the difference.

The output of the code is given below.

Read: MariaDB Select Into + Examples

MariaDB Join 3 tables

In MariaDB, the JOIN clause can combine the rows from more than two or three tables. So here in this sub-section, we will join three tables.

• Remember while combining or joining multiple tables each table must have a common column.

The syntax to join more than two tables is given below.

SELECT column_list FROM table_1
JOIN table_2 ON table_1.column_name = table_2.column_name
JOIN table_3 ON table_2.column_name = table_3.column_name
..........

Here in the above syntax, the multiple JOIN clause is used to combine three or more tables.

Creat the third table as an Address that will contain the address of the employees in the table Employee.

CREATE TABLE Address(address_id INT, address VARCHAR(50));

Where,

• CREATE TABLE: It is the command to create a new table as here we are creating the table as Address.
• address_id INT: It is the column of type integer of table Address that will store the id of each employee address.
• address VARCHAR(50): It is the column of type character of table Address that will store the address of each employee.

Insert the following records.

INSERT INTO Address(address_id,address)VALUES(1,'United State'),(2,'United Kindom'),(3,'New York'),(4,'Canada'),(5,'New Zealand');

View the table using the below code.

SEELCT * FROM Address;

Let’s join the three tables that we have created in the above sub-section using the below code.

SELECT emp_name,emp_department,address FROM employee AS emp
JOIN Employee_Dept AS edpt ON emp.id = edpt.dept_id
JOIN Address AS adrs ON edpt.dept_id = adrs.address_id ;

The above output shows that each row of three tables is combined using the multiple JOIN clause. Note each table has common columns like id, dept_id, and address_id, all these columns have the same kind of data.

Read: MariaDB regexp + Examples

MariaDB join vs inner join

In MariaDB, the JOIN and INNER JOIN are the same in functionality but the only difference in their names. Both return the rows from multiple tables where columns match between both or multiple tables.

Let’s understand with an example to see the difference, Here we will use the same table that we have created in the above sub-sections.

SELECT emp_name,emp_department FROM Employee AS emp
JOIN Employee_Dept AS edpt ON emp.id = edpt.dept_id;

Here in the above code, we have used the simple JOIN clause to retrieve the rows from the tables Employee and Employee_Dept.

The output of the code is given below.

Now run the same code using the INNER JOIN clause using the below code.

SELECT emp_name,emp_department FROM employee AS emp
INNER JOIN Employee_Dept AS edpt ON emp.id = edpt.dept_id;

Here in the code, instead of a simple JOIN clause, we have used the INNER JOIN clause.

The output of the code is given below.

If we look at the output of both the query, we have achieved the same result, because there is no difference between the JOIN and INNER JOIN clauses in the case of functionality. So we can use the INNER JOIN clause without the keyword INNER.

Read: MariaDB Case Statement

MariaDB join subquery

The SUBQUERY (Query within the query is called subquery) is used in some scenarios rather than JOINS in MariaDB. When we need the duplicate values but not false duplicate values.

Suppose we have a table named Employee_Country which has three rows as (‘United State’, ‘United State’, ‘Canada’) and another table as Person_Country which has two rows as (‘United State’, ‘Canada’, ‘Canada’). Now want to show the rows of table Employee_Country that is also in the table Person_Country then here subquery gives the right result instead of JOIN.

Let’s understand with an example by creating the tables Employee_Country and Person_Country.

CREATE TABLE Employee_Country(emp_country VARCHAR(30));

Where,

• CREATE TABLE: It statement to create a new table as here we are creating the table as Employee_Country.
• emp_country VARCHAR(30): It is a column of a type of characters that will store the name of the country.

Insert the following records in Employee_Country like United State, United State and Canada.

INSERT INTO Employee_Country(emp_country)VALUES('United State'),('United State'),('Canada');

View the create table.

SELECT * FROM Employee_Country;

Create another table as Person_Country using the below code.

CREATE TABLE Person_Country(person_country VARCHAR(30));

Here we create the table as Person_Country where

• person_country VARCHAR(30): It is the column of type character to store the name of countries.

Insert the following records in the Person_Country table as United State, Canada and Canada.

INSERT INTO Person_Country(person_country)VALUES('United State'),('Canada'),('Canada');

View the table using the below query.

SELECT * FROM Person_Country;

Now, perform the JOIN operation using subqueries.

SELECT Employee_Country.emp_country
FROM   Employee_Country 
WHERE  Employee_Country.emp_country IN 
  (SELECT  Person_Country.person_country
    FROM   Person_Country);

Here in the code, We are listing all records of the table Employee_Country that also exists in another table Person_Country. As we can see in the code that there is one more query that exists within parenthesis.

Read: MariaDB on Duplicate Key Update

MariaDB join Delete

MariaDB allows us to use the JOIN clause with the DELETE statement to delete the record from multiple tables.

The syntax to delete the record using JOIN is given below.

DELETE table_name_1, table_name_2
FROM table_name_1
JOIN table_name_2 ON table_name_1.column_name = table_name_2.column_1
WHERE condition

Let’s consider this using an example by creating two different tables.

Create a table as Country_Id by typing the below code.

CREATE TABLE Country_Id (
    id INT PRIMARY KEY
);

CREATE TABLE Country(country_name VARCHAR(50),ref_id INT);

Where,

• id INT PRIMARY KEY: It is the column of type integer of table Counrty_Id to store the id of each country.
• country_name: It is the column of type character of table Country that is going to store the name of each country.
• ref_id INT: It is the column of type integer of table Country to store the reference id of each country.

Insert the following record in both the tables.

INSERT INTO Country_Id(id)VALUES(1),(2),(3),(4);

INSERT INTO Country(country_name,ref_id)VALUES('United Kindom',1),('United State',2),('Canada',3),('Brazil',4);

View the Country_Id table.

View the Country table.

Now delete the record using the JOIN clause with DELETE statement using the below code.

DELETE Country_Id,Country FROM Country_Id
    INNER JOIN
    Country ON Country.ref_id = Country_Id.id 
WHERE
    Country_Id.id = 2;

Let’s check the deleted record from both tables.

Show the Country_Id and Country table data using the below query.

SELECT * FROM Country_Id;
   
SELECT * FROM Country;

The output in the above picture shows that record number 2 is deleted from both tables.

Read: MariaDB Window functions

MariaDB join vs left join

The difference between JOIN and LEFT JOIN is the way records are retrieved from the multiple tables.

• The JOIN clause returns all rows that have command match in the multiple tables whereas LEFT JOIN returns all rows in the left table and the match rows from another table based on a given condition.

The syntax is given below

SELECT Column_list LeftTable_Name
JOIN RightTable_Name ON LeftTable_Name.column = RighTable_Name.column

Let’s understand with an example, here we will use the two tables that we have created in the above sub-section is Employee and Emplyee_Dept.

First, perform the simple JOIN operation using the below code.

SELECT * FROM employee_dept
JOIN employee ON employee_dept.dept_id = employee.id ;

The output of the code returns the matched record from both tables based on the specified conditions as employee_dept.dept_id = employee.id given in the below output.

Now, perform the LEFT JOIN clause using the below code.

SELECT * FROM employee_dept
LEFT JOIN employee ON employee_dept.dept_id = employee.id;

Here in the above code, We have used the asterisk symbol(*) with SELECT statement that represents every column of tables. Then perform the LEFT JOIN with table Employee on the common columns (employee_dept.dept_id = employee.id) of both tables.

The output of the above code returns all records of the left table (employee_dept) and matches records from the right table, as we can in the below output.

Read: MariaDB Row_Number tutorial

MariaDB join string

In MariaDB, the JOIN clause can be used with only string or character type values which means the JOIN operation can also be performed with string data.

First, create two different tables for the example using the below instructions.

Create a table as Student_Address to store the address of the student using the below code.

CREATE TABLE Student_Address(stu_address VARCHAR(50));

Where,

• CREATE TABLE: This statement is used to create a new table as here creating the table Student_Address.
• stu_address VARCHAR(50): It is the column of type characters to store the address of the student.

Insert the following record in a table.

INSERT INTO Student_Address(stu_address)VALUES('United State'),('New York'),('United Kindom'),('Australia'),('Canada'),('New Zeland');

View the table by typing the below query.

SELECT * FROM Student_Address;

Create another table as Student_Data to store the address and name of the student using the below code.

CREATE TABLE Student_Data(stu_address VARCHAR(50), student_name VARCHAR(40));

Where,

• CREATE TABLE: This statement is used to create a new table as here creating the table Student_Address.
• stu_address VARCHAR(50): It is the column of type characters to store the address of the student.
• student_name VARCHAR(40): It is the column of type characters to store the name of each student.

Insert the following records in a table using the below query.

INSERT INTO Student_Data(stu_address,student_name)VALUES
 ('United State','James'),('New York','Robert'),('United Kindom','Jhon'),('Australia','Michael'),
 ('Canada','William'),('New Zeland','David'),('Brazil','José');

View the table by typing the below query.

SELECT * FROM Student_Data;

Let’s perform the join with string column stu_address using the below code.

SELECT * FROM Student_Data 
RIGHT JOIN Student_Address ON Student_Data.stu_address = Student_Address.stu_address;

Here in the above code, We have used the asterisk symbol(*) with SELECT statement that represents every column of tables. Then perform the RIGHT JOIN with table Student_Data on the common columns (Student_Data.stu_address = Student_Address.stu_address) of both tables.

From the output, we can see that the JOIN is used on the column of type string or character.

Read: MariaDB Date Function with Examples

MariaDB join update

In MariaDB, the JOIN clause can also be used with the UPDATE statement to the data record of multiple tables.

First, create two different tables as Marks and Student_Scholarship.

Create the table as Marks that contain the score of each student using the below code.

CREATE TABLE Marks(id INT, score FLOAT);

Where,

• CREATE TABLE: It is the statement to create a new table as here we are creating the table as Marks.
• id INT: It is the column of type integer to store the id related to each score.
• score FLOAT: It is the column of type float or decimal to store the score of each student.

Insert the following records in the Marks table.

INSERT INTO Marks(id,score)VALUES(1,40),(2,60),(3,65.7),(4,80),(5,90);

View the table by typing the below query.

SELECT * FROM Marks;

Create the second table as Student_Scholarship that will contain the name, id, and scholarship of each student.

CREATE TABLE Student_Scholarship(student_name VARCHAR(50),student_id INT,scholarship FLOAT);

Where,

• CREATE TABLE: It is the command to create a new table as here creating the table as Student_Scholarship.
• student_name VARCHAR(50): It is the column of type character to store the name of each student.
• student_id INT: It is the column of type integer to store the unique identity of the each student.
• scholarship FLOAT: It is the column of type decimal or float to store the scholarship of the each student.

Insert the following records in the table Student_Scholarship.

INSERT INTO Student_Scholarship(student_name,student_id,scholarship)VALUES('Ben Howard',1,45000),('Blake Baxter',2,50000),
('Jamie Henderson',3,60000),('Joe Bradley',4,65000),('Leo Simpson',5,80000);

View the table by typing the below code.

SELECT * FROM Student_Scholarship;