In MySQL, Joins are used to combine rows from two or more tables based on a related column between them.
There are several types of joins:
INNER JOIN
LEFT JOIN (or LEFT OUTER JOIN)
RIGHT JOIN (or RIGHT OUTER JOIN)
FULL JOIN (or FULL OUTER JOIN)
Employees Table
employee_id | first_name | last_name | department_id |
emp123 | Alice | Collin | 1 |
emp234 | Edward | Collin | 3 |
emp345 | Charles | Collin | 2 |
department_id | department_name |
1 | Marketing |
2 | Finance |
The INNER JOIN keyword selects records that have matching values in both tables.
SELECT employees.*, departments.department_name FROM employees INNER JOIN departments ON employees.department_id = departments.department_id;
Note: This query returns all columns from the "employees" table and the "department_name" from the "departments" table where there is a match on the "department_id".
SELECT employees.*, departments.department_name FROM employees LEFT JOIN departments ON employees.department_id = departments.department_id;
The LEFT JOIN keyword returns all records from the left table "employees" and the matched records from the right table "departments". The result is "NULL" from the right side if there is no match.
Note: This query returns all "employees" and their corresponding "departments". If an "employee" does not belong to any "department", the "department_name" will be "NULL".
SELECT employees.*, departments.department_name FROM employees RIGHT JOIN departments ON employees.department_id = departments.department_id;
The RIGHT JOIN keyword returns all records from the right table "departments" and the matched records from the left table "employees". The result is "NULL" from the left side if there is no match.
Note: This query returns all departments and the employees belonging to those departments. If a department has no employees, the employee-related columns will be NULL.
SELECT employees.*, departments.department_name FROM employees FULL JOIN departments ON employees.department_id = departments.department_id;
The FULL JOIN keyword returns all records when there is a match in either the left "employees" or the right "departments" table records. If there is no match, "NULL" values are returned for columns from the table without a match.
Note: This query returns all "employees" and their "departments", including those "employees" with no assigned "department" and "departments" with no assigned "employees".