JOIN is used to retrieve data from two or more tables. By default, JOIN is also called INNER JOIN. It is used with SELECT statement.
There are mainly two types of joins in MariaDB:
INNER JOIN:
MariaDB INNER JOIN is the most common type of join which returns all rows from multiple tables where the join condition is satisfied.
Syntax:
SELECT columns
FROM table1
INNER JOIN table2
ON table1.column = table2.column;
MariaDB Join 1
Example
We have two tables "Students" and "Employee2".
Student table
MariaDB Join 2
Employee2 Table
MariaDB Join 3
Execute the following commands:
SELECT Students.student_id, Students.student_name, Employee2.salary
FROM Students
INNER JOIN Employee2
ON Students.student_id = Employee2.emp_id;
Output
MariaDB Join 4
OUTER JOIN:
Again OUTER JOIN is divided into two types:
LEFT JOIN:
MariaDB LEFT OUTER JOIN is used to return all rows from the left-hand table specified in the ON condition and only those rows from the other table where the joined condition is satisfied.
LEFT OUTER JOIN is also called LEFT JOIN.
Syntax:
SELECT columns
FROM table1
LEFT [OUTER] JOIN table2
ON table1.column = table2.column;
MariaDB Left outer join 1
Example
SELECT Students.student_id, Students.student_name,
Students.student_address,
Employee2.salary, Employee2.emp_address
FROM Students
LEFT JOIN Employee2
ON Students.student_id = Employee2.emp_id;
Output
MariaDB Left outer join 2
RIGHT JOIN:
MariaDB RIGHT OUTER JOIN is used to return 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 satisfied with the conditions.
MariaDB RIGHT OUTER JOIN is also called RIGHT JOIN.
Syntax:
SELECT columns
FROM table1
RIGHT [OUTER] JOIN table2
ON table1.column = table2.column;
MariaDB Right outer join 1
Example
SELECT Students.student_id, Students.student_name,
Students.student_address, Employee2.salary,
Employee2.emp_address
FROM Students
RIGHT JOIN Employee2
ON Students.student_id = Employee2.emp_id;
MariaDB Right outer join 2
