In SQL, 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".
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.
SELECT employees.*, departments.department_name FROM employees LEFT JOIN departments ON employees.department_id = departments.department_id;
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.
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.
SELECT employees.*, departments.department_name FROM employees RIGHT JOIN departments ON employees.department_id = departments.department_id;
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".
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.
SELECT employees.*, departments.department_name FROM employees FULL JOIN departments ON employees.department_id = departments.department_id;
Note: This query returns all employees and their departments, including those employees with no assigned department and departments with no assigned employees.