Joins

In DBMS Joins are used to use the relation between the tables to create combined result. 1. (Inner) Join 2. Left (Outer) Join 3. Right (Outer) Join 4. Full (Outer) Join 5. Cross Join 6. Self Join

1. (Inner) Join

• Returns only rows which are present in both tables for the join

SELECT employees.id, employees.name, departments.department_name
FROM employees 
INNER JOIN departments 
ON employees.department_id = departments.id;

2. Left (Outer) Join

• Returns all rows of the main table, join them with the join table, and if data not present in join table for the main table, it’s value returned as NULL
• Returns all left and common rows, and no right, means departments with no employees wont be returned, but employees with no department will be there

SELECT employees.id, employees.name, departments.department_name
FROM employees
LEFT JOIN departments
ON employees.department_id = departments.id;

3. Right (Outer) Join

• Returns all rows of the main table, join them with the join table, and if data not present in the main table for the join table, its value is returned as NULL
• Returns all right and common rows, and not left, means employees with no departments wont be returned, but departments with no employees will be there.

SELECT employees.id, employees.name, departments.department_name
FROM employees
RIGHT JOIN departments
ON empoyees.department_id = departments.id;

4. Full (Outer) Join

• All rows are returned, left, right and common. The left and right which are not connected, will have other columns as NULL

SELECT employees.id, employess.name, departments.department_name
FROM employees
FULL OUTER JOIN departments
ON employees.department_id = departments.id;

5. Cross Join

• Create rows with cartesian product, that is, it will join each row in left with each row in right, so for 4 in left, and 5 in right will return 20 rows

SELECT employees.id, employees.name, departments.department_name
FROM employees
CROSS JOIN departments;

6. Self Join

• Self join is any type of regular join, but the join table is itself

SELECT e1.id AS EmployeeID, e1.name AS EmployeeName, e2.id AS ManagerID, e2.name AS ManagerName FROM employees e1 INNER JOIN employees e2 ON e1.manager_id = e2.id;