Joins In MySQL

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)

Simplified structure of Employees and Departments tables:

 Employees Table 

employee_id first_name last_name department_id
emp123 Alice Collin 1
emp234 Edward Collin 3
emp345 Charles Collin 2

 Departments Table 

department_id department_name
1 Marketing
2 Finance

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

INNER JOIN In MySQL:

The INNER JOIN keyword selects records that have matching values in both tables.

Example:

SELECT employees.employee_id, employees.first_name, departments.department_name, departments.department_id
FROM employees
INNER JOIN departments ON employees.department_id = departments.department_id;

The above query returns columns such as `employee_id` and `first_name` from the "employees" table and the "department_name" and "department_id" from the "departments" table only when there is a match of the "department_id" in department and employee table.

LEFT JOIN (or LEFT OUTER JOIN):

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.

Example:

SELECT employees.employee_id, employees.first_name, departments.department_name, departments.department_id
FROM employees
LEFT JOIN departments ON employees.department_id = departments.department_id;

The above query returns all "employees" and their corresponding departments. If an "employee" does not belong to any "department", the "department_name" will be NULL.

RIGHT JOIN (or RIGHT OUTER JOIN):

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.

Example:

SELECT employees.employee_id, employees.first_name, departments.department_name, departments.department_id
FROM employees
RIGHT JOIN departments ON employees.department_id = departments.department_id;

The above query returns all "departments" and the "employees" belonging to those "departments". If a "department" has no employees, the employee-related columns will be "NULL".

FULL JOIN (or FULL OUTER JOIN):

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.

Example:

SELECT employees.employee_id, employees.first_name, departments.department_name, departments.department_id
FROM employees
FULL JOIN departments ON employees.department_id = departments.department_id;

The above query returns columns such as `employee_id` and `first_name` from the "employees" table including those "employees" with no assigned "department" and "departments" with no assigned "employees".

<-- Limit

Any All -->