INNER JOIN

➔ Returns only those rows where there are matches in both tables based on the joining column name.

Syntax

SELECT table1.column_name, table2.column_name
FROM table1
[INNER] JOIN table2
ON table1.common_column = table2.common_column;

Example: Find employees who are assigned to a department.

SELECT e.first_name, d.department_name
FROM hr.employees e
INNER JOIN hr.departments d 
ON e.department_id = d.department_id;

LEFT JOIN

➔ Returns all rows from the left-hand table and matching rows from the right-hand table. If no match is found, no data will come from the table on the right.

Syntax

SELECT columns
FROM table1
LEFT [OUTER] JOIN table2
ON table1.column_name = table2.column_name;

Example: Find all employees, including employees not assigned to any department (Returns only those rows whose value for the joining column 'department_id' is common to both tables and unique to the table on the left).

SELECT e.first_name, d.department_name
FROM hr.employees e
LEFT JOIN hr.departments d 
ON e.department_id = d.department_id;

RIGHT JOIN

➔ Returns all rows from the right side table and the matching rows from the left side table. If no match is found, no data will be returned from the table on the left.

Syntax

SELECT columns
FROM table1
RIGHT [OUTER] JOIN table2
ON table1.column_name = table2.column_name;

Example: List and return all departments and their employees, including departments with no employees.(Returns only those rows whose value for the joining column 'department_id' is common to both tables and has unique value to the left hand table.)

SELECT e.first_name, d.department_name
FROM hr.employees e
RIGHT JOIN hr.departments d  
ON e.department_id = d.department_id;
                            

FULL JOIN

➔ Returns all rows with matching data and mismatched data in both tables.

Syntax

SELECT columns
FROM table1
FULL [OUTER] JOIN table2 
ON table1.column_name = table2.column_name;

Example: List all employees and all departments, regardless of employees without departments and departments without employees. ( Returns only those rows whose joining column 'department_id' has the same value in both tables and is unique in both tables.)

SELECT e.first_name, d.department_name
FROM hr.employees e
FULL JOIN hr.departments d 
ON e.department_id = d.department_id;

SELF JOIN

➔ A join where a table is joined to itself, typically used for hierarchical data.

Syntax

SELECT t1.column_name, t2.column_name
FROM table_name t1
[INNER | LEFT | RIGHT] JOIN table_name t2
ON t1.common_column = t2.common_column;

Example: To find the name of each employee and their respective manager from an employees table.

SELECT e.first_name ||' '||e.last_name AS Employee, m.first_name AS Manager
FROM employees e
LEFT JOIN employees m 
ON e.manager_id = m.employee_id;

NATURAL JOIN

➔ Automatically joins two tables based on columns with the same name and data type in both tables.

Syntax

SELECT column_list 
FROM table1 
NATURAL JOIN table2;

Example: Both the EMPLOYEES table and the DEPARTMENTS table have a DEPARTMENT_ID column, so when using a NATURAL JOIN, Oracle will automatically use that column to link the two tables.

--Return distinct department_id in employees table.
SELECT distinct department_id 
FROM hr.employees;

--Return distinct department_id in departments table.
SELECT distinct department_id 
FROM hr.departments;

--Return common data in both tables
SELECT last_name, department_id, department_name 
FROM hr.employees 
NATURAL JOIN hr.departments;

NATURAL JOIN Characteristics

➔ In a natural join, the ON or USING clause cannot be used to specify the join condition; Oracle automatically detects the common columns.

➔ Column qualifiers cannot be used for common-join-columns with a table name or alias in the SELECT list or WHERE clause.

➔ By default, a NATURAL JOIN acts as an INNER JOIN and returns only matching rows.

CROSS JOIN

➔ Returns the Cartesian product of two tables (by pairing each row of the first table with each row of the second table).

➔ Useful for creating all possible combinations.

Example: Generate all possible combinations of employees and departments table rows.

SELECT e.first_name, e.department_id, d.department_name
FROM hr.employees e
CROSS JOIN hr.departments d;

Oracle Inner Excluding Join (Anti-Join)

➔ In Oracle, an inner excluding join, commonly known as an anti-join, returns rows from one table that do not have any matching records in another table.

➔ The most common way to write an anti-join is to create a LEFT JOIN with a specific NULL check.

Example: Find employees who are not assigned to any department.

This query finds all employees from the left table and filters for those who did not find a match in the right table.

--Oracle Inner Join
/* SELECT e.first_name, e.last_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;  */

--Oracle Inner Excluding Join
SELECT e.first_name, e.department_id, dpt.department_name
FROM hr.employees e
LEFT JOIN hr.departments dpt 
ON e.department_id = dpt.department_id
WHERE dpt.department_id IS NOT NULL;

Oracle Left Excluding Join

➔ A left excluding join (also known as a left anti-join) in Oracle returns only those records from the left table that do not have a match in the right table.

Example:

--Oracle Left Join
/* SELECT d.department_name, e.first_name
FROM employees e
RIGHT JOIN departments d 
ON e.department_id = d.department_id; */

--Oracle Left Excluding Join
SELECT d.department_name, e.first_name
FROM employees e
RIGHT JOIN departments d 
ON e.department_id = d.department_id
WHERE d.department_id IS NULL;

Oracle Right Excluding Join

➔ A right excluding join (also known as a right anti-join) in Oracle returns only those records from the right table that do not have a match in the left table.

Example:

--Oracle Right Join
/* SELECT d.department_name, e.first_name
FROM employees e
RIGHT JOIN departments d 
ON e.department_id = d.department_id; */

--Oracle Right Excluding Join
SELECT d.department_name, e.first_name
FROM employees e
RIGHT JOIN departments d 
ON e.department_id = d.department_id
WHERE e.department_id IS NULL; 

Oracle Full Excluding Join

➔ An Oracle full excluding join (or full join with exclusion) returns only the unmatched rows from both tables, and excludes the rows that meet the join condition.

Example:

-- Oracle Full Join
/* SELECT e.employee_name, d.department_name
FROM employees e
FULL OUTER JOIN departments d 
ON e.department_id = d.department_id; */

-- Oracle Full Excluding Join
SELECT e.first_name, dpt.department_name
FROM employees e
FULL OUTER JOIN departments dpt 
ON e.department_id = dpt.department_id
WHERE e.department_id IS NULL OR dpt.department_id IS NULL;