MySQL Joins
What is Joins?
Joins in SQL used to combine rows from two or more tables based on a related column between them.
There are several types of joins, including INNER JOIN, LEFT JOIN (or LEFT OUTER JOIN), RIGHT JOIN (or RIGHT OUTER JOIN), and FULL JOIN (or FULL OUTER JOIN).
INNER JOIN:
The INNER JOIN keyword selects records that have matching values in both tables.
Syntax:
SELECT columns
FROM table1
INNER JOIN table2 ON table1.column_name = table2.column_name;
Example:
Assuming we have two tables, employees and departments, with a common column department_id:
SELECT employees.employee_id, employees.first_name, employees.last_name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.department_id;
This query retrieves the employee_id, first_name, last_name, and department_name for employees and their corresponding departments.
LEFT JOIN (or LEFT OUTER JOIN):
The LEFT JOIN keyword returns all records from the left table (table1), and the matched records from the right table (table2).
The result is NULL from the right side if there is no match.
Syntax:
SELECT columns
FROM table1
LEFT JOIN table2 ON table1.column_name = table2.column_name;
Example:
SELECT employees.employee_id, employees.first_name, employees.last_name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.department_id;
This query retrieves all employees, including those who do not belong to any department.
The department_name will be NULL for employees without a matching department.
RIGHT JOIN (or RIGHT OUTER JOIN):
The RIGHT JOIN keyword returns all records from the right table (table2), and the matched records from the left table (table1).
The result is NULL from the left side when there is no match.
Syntax:
SELECT columns
FROM table1
RIGHT JOIN table2 ON table1.column_name = table2.column_name;
Example:
SELECT employees.employee_id, employees.first_name, employees.last_name, departments.department_name
FROM employees
RIGHT JOIN departments ON employees.department_id = departments.department_id;
This query retrieves all departments, including those without any employees.
The employee_id, first_name, and last_name will be NULL for departments without matching employees.
FULL JOIN (or FULL OUTER JOIN):
The FULL JOIN keyword returns all records when there is a match in either left (table1) or right (table2) table records.
Syntax:
SELECT columns
FROM table1
FULL JOIN table2 ON table1.column_name = table2.column_name;
Example:
SELECT employees.employee_id, employees.first_name, employees.last_name, departments.department_name
FROM employees
FULL JOIN departments ON employees.department_id = departments.department_id;
This query retrieves all employees and all departments.
If there is no match, NULL values will be present in the columns from the table without a match.
Tips:
- Use joins to combine related information from different tables.
- Ensure that the columns used for joining have compatible data types.
- Be mindful of NULL values when using outer joins.