Joins

In SQL, joins are used to combine rows from two or more tables based on a related column between them. Joins allow you to retrieve data from multiple tables as though the data were stored in a single table.

Types of Joins

1. INNER JOIN
2. LEFT JOIN (LEFT OUTER JOIN)
3. RIGHT JOIN (RIGHT OUTER JOIN)
4. FULL OUTER JOIN

1. INNER JOIN

An INNER JOIN returns records that have matching values in both tables. If there is no match, no data is returned from either table for that row.

Syntax:

SELECT columns
FROM table1
INNER JOIN table2 ON table1.common_column = table2.common_column;

Example:

SELECT Employees.FirstName, Employees.LastName, Departments.DepartmentName
FROM Employees
INNER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

This query returns employees and their corresponding department names. Only employees who are assigned to a department will be displayed.

2. LEFT JOIN (LEFT OUTER JOIN)

A LEFT JOIN returns all records from the left table (table1), and the matched records from the right table (table2). If there is no match, the result will contain NULL for columns from the right table.

Syntax:

SELECT columns
FROM table1
LEFT JOIN table2 ON table1.common_column = table2.common_column;

Example:

SELECT Employees.FirstName, Employees.LastName, Departments.DepartmentName
FROM Employees
LEFT JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

This query retrieves all employees and their department names. Employees without a department will have NULL values in the DepartmentName column.

3. RIGHT JOIN (RIGHT OUTER JOIN)

A RIGHT JOIN is the opposite of a LEFT JOIN. It returns all records from the right table (table2), and the matched records from the left table (table1). If there is no match, the result will contain NULL for columns from the left table.

Syntax:

SELECT columns
FROM table1
RIGHT JOIN table2 ON table1.common_column = table2.common_column;

Example:

SELECT Employees.FirstName, Employees.LastName, Departments.DepartmentName
FROM Employees
RIGHT JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

SELECT columns
FROM table1
FULL OUTER JOIN table2 ON table1.common_column = table2.common_column;

SELECT Employees.FirstName, Employees.LastName, Departments.DepartmentName
FROM Employees
FULL OUTER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

Departments Table:

Using different types of joins will result in various outcomes:

1. INNER JOIN: Shows only John and Jane, as they are the only ones assigned to departments.
2. LEFT JOIN: Shows all employees, including Mike, but Mike will have NULL for DepartmentName.
3. RIGHT JOIN: Shows all departments, including IT (which has no employees), with NULL for employee details where there’s no match.
4. FULL OUTER JOIN: Shows all employees and departments, including those with no matches (e.g., Mike and the IT department).
5. CROSS JOIN: Every employee is combined with every department, resulting in all possible combinations of employees and departments.
6. SELF JOIN: Could be used to compare employees within the same table (e.g., pairing employees with their managers).