MariaDB Join with Examples - DatabaseFAQs.com

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;

Let’s increase the scholarship of the student by a certain amount using the JOIN operation.

UPDATE Student_Scholarship
INNER JOIN Marks ON Student_Scholarship.student_id = Marks.id
SET scholarship = scholarship + scholarship * (score /50);

Here in the code, we have used the UPDATE statement to update the scholarship of the student. Then used the INNER JOIN with table MARKS on common columns as Student_Scholarship.student_id = Marks.id.

At last set the new value of the column scholarship using the SET statement as SET scholarship = scholarship + scholarship * (score /50);

View the updated data by typing the below code.

SELECT * FROM Student_Scholarship;

Looking at the above output, we have updated the scholarship of each student by a certain amount using the JOIN clause.

Read: MariaDB DateTime Tutorial + Examples

MariaDB join_buffer_size

The MariaDB has many system variables that can change according to our need, the join_buffer_size is one of the system variables which is used to speed up the JOIN process.

The size of the join_buffer_size is increased for the optimization of the FULL JOINS when indexing is not possible.
The minimum size is specified in bytes for join_buffer_size in a queries.
The join_buffer_size value can be set for session and global purpose.
The data type of join_buffer_size is numeric.
The default value of the join_buffer_size is different for dfferent version of MariaDB as MariaDB version which is greater than (10.1.7) has 256kb (262144) and 128kb (131072) for the version less than (10.1.6).
The join_buffer_size is dynamic in nature that means its value can be changed at runtime.

The syntax to change the join_buffer_size in the command line is given below.

./mysqld_safe --join-buffer-size=#

Where,

mysqld_safe: It is the script for starting the mysqld in a Linux environment or distributions.
–join-buffer-size=#: It is the system variables that can be changed according to our need, and the symbol # represents the values assigned to this system variable.

To check the value of the system variable in MariaDB use the below query.

SHOW VARIABLES LIKE 'join_buffer_size';

After running the above query, the output shows the current value of the join_buffer_size which is the default value 262144 (256kb).

Read: MariaDB DATEDIFF Function

MariaDB join_buffer_space_limit

The join_buffer_space_limit is another system variable in MariaDB that controls the total memory used by the join buffers.

The join_buffer_space_limit is introduced in the MariaDB(5.3).
It is dynamic in nature which means its value can be set at runtime.
The maximum size of query buffer is specified in bytes.
The default value supported by join_buffer_space_limit is 2097152.
Scope of join_buffer_space_limit is session and global.
The data type of the join_buffer_space_limit is numeric.

The syntax to set the join_buffer_space_limit using the command line is given below.

./mysqld_safe --join-buffer-space-limit=#

Where,

mysqld_safe: It is the script for starting the mysqld in a Linux environment or distributions.
–join-buffer-space-limit=#: It is the system variables that can be changed according to our need, and the symbol # represents the values assigned to this system variable.

To check the current value of the join_buffer_space_limit use the below query.

SHOW variables LIKE 'join_buffer_space_limit';

After running the above query, the output shows the current value of the join_buffer_space_limit which is the default value 2097152.

Read: MariaDB Create Sequence + Examples

MariaDB join buffer

Whenever FULL JOIN is performed between two tables at that time join buffer is used. The buffer is allocated to each query and this buffer is freed after the query has finished its work.

Only the used columns are stored in join buffer.
When the type of join is index or ALL then the buffer is used.
The system varaible join_buffer_size is used to specify the size of join buffer.

MariaDB join limit

In MariaDB, we can limit the result set generated by the JOIN clause using the LIMIT keyword. The LIMIT keyword limits the number of records in the result set.

Here we will use the same table Student_Address and Student_Data that we have used in the above sub-section.

Let’s perform the simple JOIN operation and apply the limit to limit the result using the below code.

SELECT * FROM Student_Data 
JOIN Student_Address ON Student_Data.stu_address = Student_Address.stu_address
LIMIT 4;

As from the above code, we are showing each record of both the tables using the JOIN clause based on the common columns Student_Data.stu_address = Student_Address.stu_address. Then limit the result set by LIMIT 4 which means it will show only four records as a result.

The output shows only the four records as we can see, this is due to the keyword LIMIT, instead of 4, if we will specify 6 then it will show 6 records as a result.

Read: MariaDB AUTO_INCREMENT + Examples

MariaDB left join order by

In MariaDB, the ORDER BY clause can be used after the LEFT JOIN to show the result in some orderly way.

The syntax is given below.

SELECT column_list FROM table_name_1
LEFT JOIN table_name_2 ON table_name_1.column = table_name_2.column
ORDER BY column_name

Here we will use the same tables Employee_Dept and Employee that we have created in the above sub-section.

Run the below query to view all the records of both tables and order them by the column emp_department of table Employee_Dept.

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

In last order the result according to the column emp_darpartment of table Emplyee_Dept in alphabetical order as specified in the above code. By default ORDER BY clause orders the string from A-Z and number in ascending order from low to high.

The output of the above code is given below.

If you don’t understand how the above code works, then refer to the sub-section “MariaDB join vs left join” of this tutorial.

Read: MariaDB Primary Key With Examples

MariaDB Join group by

The GROUP BY clause puts similar rows that have the same values into groups in MariaDB, so the GROUP BY clause will be used with the JOIN clause.

Here we will create the two new tables as Customer and Books.

Create the table as Customer using the below code.

CREATE TABLE Customer(id INT AUTO_INCREMENT PRIMARY KEY , cust_name VARCHAR(50), item_orderd_price FLOAT);

Where,

CREATE TABLE: It is the statement to create a new table, here we are creating the table as Customer.

id INT AUTO_INCREMENT PRIMARY KEY: It is the column of type integer to store the unique identity of each customer. It will be incremented automatically because the column is defined as AUTO_INCREMENT and PRIMARY KEY.
cust_name VARCHAR(50): This is the column of type characters to store the name of each customer.
item_ordered_price FLOAT: It is the column of type float to store the price of an item ordered by the customer.

Insert the following record into a table Customer using the below query.

INSERT INTO Customer(cust_name,item_orderd_price)VALUES('Jason', 299),('Aurelio',500),('Duane',150),('Beverly J. Stolz',600),
('Betty W. Keller',400),('Margaret W',499),('Audrey L. Fisher',800);

View the table using the below statement.

SELECT * FROM CUSTOMER;

Create the second table as Books using the below query.

CREATE TABLE Books(book_name VARCHAR(100),book_lang VARCHAR(50), book_price FLOAT);

Where,

CREATE TABLE: Here we are creating the table as Books using the statement CREATE TABLE.
book_name VARCHAR(100): It is the column of type characters to store the name of the books.
book_lang VARCHAR(50): It is the column of type characters to store the written language of the books.
book_price FLOAT: It is the column of type float to store the price of books.

Insert the following records into a table Books using the below query.

INSERT INTO Books(book_name,book_lang,book_price)VALUES('The Little Prince','French',500),('The Adventures of Pinocchio','Italian',299),
('Dao De Jing','Chinese',150),('Steps to Christ','English',800),('El Ingenioso Hidalgo Don Quijote de la Mancha','English',400),
('Andersen''s Fairy Tales','Danish',499),('The Adventures of Asterix','French',600),('Book of Mormon','English',900),
('The Upright Revolution: Or Why Humans Walk Upright','English',1200),('The Adventures of Tintin','French',750),
('The Imitation of Christ','Latin',550.45),('Harry Potter','English',950);

View the table using the below statement.

SELECT * FROM Books;

Let’s perform the JOIN operation using the two tables without the GROUP BY clause.

SELECT * FROM Customer
JOIN Books ON Customer.item_orderd_price = Books.book_price;

Here in the above code, we are selecting every column (*) from both tables Customer and Books using the SELECT statement.

Then joining the tables using JOIN Books on common columns using the ON Customer.item_orderd_price = Books.book_price.

Look at the above output, the result is without the use of the GROUP BY clause.

Now we will use the same query with the GROUP BY clause.

SELECT * FROM Customer
JOIN Books ON Customer.item_orderd_price = Books.book_price
GROUP BY book_lang;

We have used the same code that is used just above this code, here is the code we have specified the GROUP BY clause to group the common rows that have the same values. So here we are grouping according to the language of the book using the GROUP BY book_lang.

The output of the code is given below.

Again look at the output of this code, how the records are grouped.

Read: MariaDB Delete Row + Examples

MariaDB Join order by

Here in this sub-section, we will use the ORDER BY clause with a simple JOIN clause, we already know how to use the ORDER BY clause with LEFT JOIN in the above sub-section “MariaDB left join order by“.

We will use the same query that we have used in the above-section “MariaDB join group by”, in that code we will replace the GROUP BY with the ORDER BY clause.

Run the below query with the ORDER BY clause.

SELECT * FROM Customer
JOIN Books ON Customer.item_orderd_price = Books.book_price
ORDER BY book_lang;

We have used the same code that is used in the above sub-section, here is the code we have specified the ORDER BY clause to order the records based on specific columns. So here we are ordering the records according to the language of the book using the ORDER BY book_lang.

From the output, we can see in the column book_lang that is ordered alphabetically as the language start with the letter ‘C‘, then ‘D‘, and so on.

Read: MariaDB Full Outer Join – How to implement

MariaDB Join distinct

In MariaDB, the DISTINCT keyword is used before the column names to remove the duplicate rows from the results.

The syntax to use the DISTINCT with JOIN is given below.

SELECT DISTINCT column_list FROM table_1
JOIN table_2 ON table_1.column = table_2.column;

Where

DISTINCT: It is the statement that removes the records having the duplicate values.
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.column = table_2.column : It is condition on which tables are joined when it is met. Where table_1.column and table_2.column is common column of the table_1 and table_2 respectively.

Let’s take an example with the two tables Customer and Books that we have created in the above sub-section.

SELECT DISTINCT book_lang FROM Customer
JOIN Books ON Customer.item_orderd_price = Books.book_price;

Here in the above code, we are selecting only the distinct language of the book available in table Books using the SELECT DISTINCT book_lang.

From the above output, the table Books contains the book written in five languages.

MariaDB left join count

The COUNT function counts the number of rows in tables in MariaDB, here we will use the COUNT function with the LEFT JOIN clause.

Here we will use the same two tables Books and Customer that we have created in the above sub-section.

Let’s run the below query to find the number of unique customers, books, and the language in which all the books were written.

SELECT COUNT(DISTINCT book_name) AS number_of_books, COUNT(distinct book_lang) AS number_of_book_language,
COUNT(DISTINCT cust_name) AS number_of_customers FROM Books
LEFT JOIN Customer ON Customer.item_orderd_price = Books.book_price;

Here in the above code, we are using the function COUNT to count the number of items in a particular column as specified in the code COUNT(DISTINCT book_name) AS number_of_books. This code contains the keyword distinct so that duplicate records should be counted only one time.

Then used the keyword AS to specify the alternate name to the same column using the AS number_of_book_language that column is specified within the COUNT function.

After running the above query, the output shows that the number of books is 12, the number of book_language is 6 and the number of customers is 7.

Read: MariaDB Cast with Examples

MariaDB full join

In MariaDB, The alternate name of FULL JOIN is FULL OUTER JOIN but MariaDB doesn’t support the FULL JOIN or FULL OUTER JOIN.

MariaDB join foreign key

The set of columns or columns in one table that references the set of columns or columns in another table is called FOREIGN KEY.

The JOIN clause always use the PRIMARY KEY and FOREIGN KEY column of multiple table to join the records.

To understand this foreign key concept with the JOIN clause, let’s create the two tables using the below steps.

Create a table as Countries that will contain the name of countries using the below code.

CREATE TABLE Countries(id INT AUTO_INCREMENT PRIMARY KEY, country_name VARCHAR(50));

Where,

CREATE TABLE : It is statement to create a new table as here we are creating the table Countries.
id INT AUTO_INCREMENT PRIMARY KEY : It is the column of type integer to store the unique identity of each countries that will be generated automatically using the AUTO_INCREMENT. This column is also defined as PRIMARY KEY column.
country_name VARCHAR(50) : It is the column of type characters to store the name of the each countries.

Insert the following records in table Countries.

INSERT INTO Countries(country_name)VALUES('United State'),('Canada'),('United Kindom'),('New Zealand');

Create the second table Person with FOREIGN KEY using the below code.

CREATE TABLE Person(person_name VARCHAR(50),person_id INT, FOREIGN KEY (person_id) REFERENCES countries(id));

Where,

CREATE TABLE: It is a statement to create a new table as here we are creating the table Person.
person_name VARCHAR(50): It is a column of type characters to store the name of each person to 50 characters.
person_id INT: It is a column of type integer to store the id of each person.
FOREIGN KEY (person_id) REFERENCES countries(id)): This line defines person_id as FOREIGN KEY that denotes a column id in another table using the REFERENCES countries(id).

Insert the following records in table Person.

INSERT INTO Person(person_name,person_id)VALUES('Paula R. Bingham',1),('Shelia J. Williams',2),('Maria J. Nelson',3),('Elaine F. Berrian',4);

Let’s perform the JOIN clause with these two tables using the below code.

SELECT country_name,person_name FROM Countries
JOIN Person ON Countries.id = Person.person_id;

Here in the above code, we are using the FOREIGN KEY column that is person_id with another column id to combine the records from both tables Countries and Person.

The output of the above code is given below.

Read: MariaDB ISNULL + Examples

MariaDB join on concat

The CONCAT() function in MariaDB combines the multiple strings into a single string, here we will use this function with the JOIN clause to combine the string in multiple tables into a single string.

We are going to use the same tables Countries and Persons that we have created in the above sub-section.

Let’s run the code to combine the columns person_name and country_name of tables Person and Countries respectively.

SELECT CONCAT(person_name,' belong to ',country_name) FROM Countries
JOIN Person ON Countries.id = Person.person_id;

Here in the above code, look in the function CONCAT with SELECT statement we have provided the three strings as the first string is the column,person_name, second is the string ' belong to ' and third is the column country_name.

The output of the above code is given below.

From the output, we can see that the CONCAT function combines these three strings into one string.

MariaDB bnl join

The full form of BNL is Block Nested Loop, it is the algorithm to perform join on tables and it is better than the algorithm NLJ (Nested Loop Join).

The working of the BNL is to perform the join operation on table_1 and table_2, first it reads the record from table_1 into a join buffer one by one until join buffer gets full.
Then this buffer is passed to the next inner loop, then each record of table_2 is read and compared against the buffer that contains the records of table_1. For reading the records of table_2 full index or full table scan and range index is done.
While comparing records from table_2 to the records in buffer that contains the records of table_1, the matched records are sent to the result staream related to that join.

This is all about the BNL algorithm.

Mariadb join across databases

In MariaDB, the JOIN can be used to combine the records of multiple tables of multiple databases.

The syntax is given below.

SELECT columns_list FROM database_1.table_name_1 AS table1
JOIN database_2.table_name_2 AS table2 ON table1.column = table2.column

Where,

columns_list: It is column names from multiple tables that we want to view after performing the JOIN operation.
database_1.table_name_1 AS table1: It is a way to specify the name of the first table that we want to use for JOIN operation, where database_1 is the name of the first database whose table we want to use. Then use the dot (.) with the name of the table (table_name_1) that we want to access.
database_2.table_name_2 AS table2: It is a way to specify the name of the second table that we want to use for JOIN operation, where database_2 is the name of the second database whose table we want to use. Then use the dot (.) with the name of the table (table_name_2) that we want to access.

Let’s create two different databases with tables.

Create database as Country using the below code.

CREATE DATABASE Country;

Select the database.

USE Country;

Create the same table Countries that we have created in the above sub-section in the database Country using the below code.

CREATE TABLE Countries(id INT AUTO_INCREMENT PRIMARY KEY, country_name VARCHAR(50));

Insert the same records.

INSERT INTO Countries(country_name)VALUES('United State'),('Canada'),('United Kindom'),('New Zealand');

View the created database and table given in the below output.

Create the second database as Person_Data.

CREATE DATABASE Person_Data;

Select the database.

USE Person_Data;

Create the same table Person that we have created in the above sub-section in the database Person_Data

CREATE TABLE Person(person_name VARCHAR(50),person_id INT);

Insert the same records.

INSERT INTO Person(person_name,person_id)VALUES('Paula R. Bingham',1),('Shelia J. Williams',2),('Maria J. Nelson',3),('Elaine F. Berrian',4);

View the created database and table given in the below output.

Now perform the JOIN operation across the database using the below code.

SELECT country_name,person_name FROM Country.Countries AS table_1
JOIN Person_data.Person AS table_2 ON table_1.id = table_2.person_id;

The output of the above code is given below.

Read: MariaDB Order By Clause

MariaDB update join group by

In MariaDB, we already know that the UPDATE and GROUP BY statements can be used with the JOIN clause from the above subsection.

Here we will use the same tables Countries and Person that we have created in the above sub-section by adding more records to these tables.

Insert the following records in the table Countries.

INSERT INTO Countries(country_name)VALUES('Canada'),('United Kindom'),('United States'),('Canada'),('New Zealand'),('United States');

Insert these records in a table Person.

INSERT INTO Person(person_name,person_id)VALUES('Bertha D. Drum',5),('Andrea D. Mixon',6),('Wade C. Buckler',7),('Kimberly M. Deese',8),
('Zulma C. Weatherford',9),('David G. Griffin',10);

Let’s perform the update with JOIN and GROUP BY clause.

UPDATE countries
JOIN (SELECT person_id,person_name FROM person GROUP BY person_name) AS p
ON p.person_id = countries.id
SET country_name = 'Brazil'
WHERE p.person_id = 3;

View the updated data using the below code.

SELECT person_id,person_name, country_name
FROM countries
JOIN person ON countries.id = person.person_id

Look in the above output, the person ‘Maria J. Nelson’ has updated the country name as ‘Brazil’.

You may also like to read the following MariaDB tutorials.

So, in this tutorial, we have learned about the “MariaDB join” and covered 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_space_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

Bijay

I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.

Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.