Types of Joins
Inner Join
Description:
An INNER JOIN returns only the rows where there is a match between the tables being joined. Rows from both tables that do not meet the join condition are excluded from the result set.
Characteristics:
- Inclusive: Displays only rows with matching values in both tables.
- Efficient: Generally performs well with large tables if the join columns are indexed.
Syntax:
SELECT columns FROM table1 INNER JOIN table2 ON table1.column = table2.column;
Example:
Suppose we have the following tables:
- Employees (employee_id, name, department_id)
- Departments (department_id, department_name)
To list employees along with their department names:
SELECT Employees.name AS Employee, Departments.department_name FROM Employees INNER JOIN Departments ON Employees.department_id = Departments.department_id;
This query returns only employees who have a corresponding department.
Left Outer Join (LEFT JOIN)
Description:
A LEFT OUTER JOIN returns all rows from the left table (table1) and the matching rows from the right table (table2). If there is no match, the result set will contain NULL for columns from the right table.
Characteristics:
- Includes all rows from the left table: Even if there is no match in the right table.
- NULL values: Columns from the right table will have NULL where there are no matches.
Syntax:
SELECT columns FROM table1 LEFT OUTER JOIN table2 ON table1.column = table2.column;
Example:
To get a list of all employees, including those who do not belong to any department:
SELECT Employees.name AS Employee, Departments.department_name FROM Employees LEFT OUTER JOIN Departments ON Employees.department_id = Departments.department_id;
This query returns all employees with their department names, including those who do not have an assigned department (department fields will be NULL).
Right Outer Join (RIGHT JOIN)
Description:
A RIGHT OUTER JOIN returns all rows from the right table (table2) and the matching rows from the left table (table1). If there is no match, the result set will contain NULL for columns from the left table.
Characteristics:
- Includes all rows from the right table: Even if there is no match in the left table.
- NULL values: Columns from the left table will have NULL where there are no matches.
Syntax:
SELECT columns FROM table1 RIGHT OUTER JOIN table2 ON table1.column = table2.column;
Example:
To get a list of all departments, including those that do not have any employees:
SELECT Employees.name AS Employee, Departments.department_name FROM Employees RIGHT OUTER JOIN Departments ON Employees.department_id = Departments.department_id;
This query returns all departments with their employees, including departments that do not have any employees (employee fields will be NULL).
Full Outer Join
Description:
A FULL OUTER JOIN returns all rows when there is a match in either table. Rows without matches in one of the tables will have NULLs for columns of the table without matches.
Characteristics:
- Includes all rows from both tables: Regardless of whether there is a match.
- NULL values: Represents missing matches from either table.
Syntax:
SELECT columns FROM table1 FULL OUTER JOIN table2 ON table1.column = table2.column;
Example:
To get a complete list of employees and departments, even if some employees do not have a department or some departments do not have employees:
SELECT Employees.name AS Employee, Departments.department_name FROM Employees FULL OUTER JOIN Departments ON Employees.department_id = Departments.department_id;
This query returns all rows from both tables with available data, filling in missing values with NULL.
Self Join
Description:
A SELF JOIN is a join where a table is joined with itself. It is useful for comparing rows within the same table, such as in hierarchical relationships or self-referential data.
Characteristics:
- Internal Comparison: Allows comparing rows within the same table.
- Common Use Cases: Hierarchical data (e.g., managers and subordinates) or detecting duplicates.
Syntax:
SELECT a.column AS Alias1, b.column AS Alias2 FROM table a INNER JOIN table b ON a.column = b.column;
Example:
To get a list of employees and their supervisors in the same Employees table:
SELECT e1.name AS Employee, e2.name AS Supervisor FROM Employees e1 LEFT JOIN Employees e2 ON e1.supervisor_id = e2.employee_id;
In this example, e1 represents employees and e2 represents their potential supervisors.
Comparison Between Join Types
- INNER JOIN: Combines rows that have matching values in both tables. Used when you need data that exists in both tables.
- LEFT OUTER JOIN: Includes all rows from the left table, with NULLs for missing matches in the right table. Useful when you want to see all data from the left table, even if some matches are missing.
- RIGHT OUTER JOIN: Includes all rows from the right table, with NULLs for missing matches in the left table. Useful for seeing all data from the right table, even if some matches are missing.
- FULL OUTER JOIN: Includes all rows from both tables, with NULLs where there are no matches in either table. Useful for getting a complete view of both tables.
- SELF JOIN: Joins a table with itself, useful for hierarchical or self-referential data.