In MySQL, joining tables allows you to combine rows from two or more tables based on a related column between them. There are different types of joins:
1. INNER JOIN: Returns only the rows that have matching values in both tables.
2. LEFT JOIN (or LEFT OUTER JOIN): Returns all rows from the left table, and the matched rows from the right table. If there is no match, the result is NULL on the side of the right table.
3. RIGHT JOIN (or RIGHT OUTER JOIN): Returns all rows from the right table, and the matched rows from the left table. If there is no match, the result is NULL on the side of the left table.
4. FULL JOIN (or FULL OUTER JOIN): Returns rows when there is a match in one of the tables. This join can potentially return very large result sets.
5. CROSS JOIN: Returns the Cartesian product of the two tables. This means that it returns all possible combinations of rows from the two tables.
Example Tables
Consider two tables: employees and departments.
employees
| emp_id | emp_name | dept_id |
|---|
| 1 |
John Doe |
101 |
| 2 |
Jane Smith |
102 |
| 3 |
Sam Brown |
103 |
| 4 |
Lisa White |
101 |
departments
| dept_id | dept_name |
|---|
| 101 |
Human Resources |
| 102 |
IT |
| 104 |
Finance |
INNER JOIN Example
To get the list of employees along with their department names, you can use an INNER JOIN:
SELECT employees.emp_id, employees.emp_name, departments.dept_name
FROM employees
INNER JOIN departments ON employees.dept_id = departments.dept_id;
Result:
| emp_id | emp_name | dept_name |
|---|
| 1 |
John Doe |
Human Resources |
| 2 |
Jane Smith |
IT |
| 4 |
Lisa White |
Human Resources |
LEFT JOIN Example
To get all employees and their departments (including those without a department), use a LEFT JOIN:
SELECT employees.emp_id, employees.emp_name, departments.dept_name
FROM employees
LEFT JOIN departments ON employees.dept_id = departments.dept_id;
Result:
| emp_id | emp_name | dept_name |
|---|
| 1 |
John Doe |
Human Resources |
| 2 |
Jane Smith |
IT |
| 3 |
Sam Brown |
NULL |
| 4 |
Lisa White |
Human Resources |
RIGHT JOIN Example
To get all departments and their employees (including departments with no employees), use a RIGHT JOIN:
SELECT employees.emp_id, employees.emp_name, departments.dept_name
FROM employees
RIGHT JOIN departments ON employees.dept_id = departments.dept_id;
Result:
| emp_id | emp_name | dept_name |
|---|
| 1 |
John Doe |
Human Resources |
| 4 |
Lisa White |
Human Resources |
| 2 |
Jane Smith |
IT |
| NULL |
NULL |
Finance |
FULL JOIN Example
MySQL does not support FULL OUTER JOIN directly. However, you can achieve this using a UNION of LEFT JOIN and RIGHT JOIN results:
SELECT employees.emp_id, employees.emp_name, departments.dept_name
FROM employees
LEFT JOIN departments ON employees.dept_id = departments.dept_id
UNION
SELECT employees.emp_id, employees.emp_name, departments.dept_name
FROM employees
RIGHT JOIN departments ON employees.dept_id = departments.dept_id;
Result:
| emp_id | emp_name | dept_name |
|---|
| 1 |
John Doe |
Human Resources |
| 2 |
Jane Smith |
IT |
| 3 |
Sam Brown |
NULL |
| 4 |
Lisa White |
Human Resources |
| NULL |
NULL |
Finance |
CROSS JOIN Example
To get all possible combinations of employees and departments:
SELECT employees.emp_name, departments.dept_name
FROM employees
CROSS JOIN departments;
Result:
| emp_name | dept_name |
|---|
| John Doe |
Human Resources |
| John Doe |
IT |
| John Doe |
Finance |
| Jane Smith |
Human Resources |
| Jane Smith |
IT |
| Jane Smith |
Finance |
| Sam Brown |
Human Resources |
| Sam Brown |
IT |
| Sam Brown |
Finance |
| Lisa White |
Human Resources |
| Lisa White |
IT |
| Lisa White |
Finance |
These examples should help you understand how to perform various types of joins in MySQL.