MySQL Joining Tables

A JOIN clause is used to combine rows from two or more tables, based on a related column between them.

Supported Types of Joins in MySQL

Returns records that have matching values in both tables
Returns all records from the left table, and the matched records from the right table
Returns all records from the right table, and the matched records from the left table
Returns all records from both tables

Inner JOIN

SELECT columns
FROM table1
INNER JOIN table2
ON table1.column = table2.column;
Create two tables “officers” and “students”
Select 2 columns in table “Officers” and 1 column in table “Students”

Left Outer JOIN

SELECT columns
FROM table1
LEFT [OUTER] JOIN table2
ON table1.column = table2.column;
Return all rows in the “Officers” table and matched rows in the “Students” table with NULL value in some rows

Right Outer JOIN

SELECT columns
FROM table1
RIGHT [OUTER] JOIN table2
ON table1.column = table2.column;
Return all rows in the “Students” table and matched rows in the “Officers” table

Cross JOIN

SELECT columns
FROM table_1
CROSS JOIN table_2;

Unlike the INNER JOIN, LEFT JOIN, and RIGHT JOIN, the cross join clause does not have a join condition.