codeIn [Spark]

Introduction to SQL Joins #

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 in a single query, which is crucial for relational database operations.

Types of Joins #

1. INNER JOIN
2. LEFT JOIN (LEFT OUTER JOIN)
3. RIGHT JOIN (RIGHT OUTER JOIN)
4. FULL JOIN (FULL OUTER JOIN)
5. CROSS JOIN
6. SELF JOIN

Sample Database #

For the examples in this document, we’ll use the following sample tables:

Table: employees #

employee_id	first_name	last_name	department_id
1	John	Doe	101
2	Jane	Smith	102
3	Bob	Johnson	103
4	Alice	Brown	101

Table: departments #

department_id	department_name
101	Sales
102	Marketing
104	IT

1. INNER JOIN #

An INNER JOIN returns only the rows with matching values in both tables.

Syntax #

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

Example #

Retrieve all employees with their department names.

SELECT employees.first_name, employees.last_name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.department_id;

Result #

first_name	last_name	department_name
John	Doe	Sales
Jane	Smith	Marketing
Alice	Brown	Sales

2. LEFT JOIN (LEFT OUTER JOIN) #

A LEFT JOIN returns all rows from the left table and the matched rows from the right table. If no match is found, NULLs are returned for columns from the right table.

Syntax #

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

Example #

Retrieve all employees and their department names, including those without a department.

SELECT employees.first_name, employees.last_name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.department_id;

Result #

first_name	last_name	department_name
John	Doe	Sales
Jane	Smith	Marketing
Bob	Johnson	NULL
Alice	Brown	Sales

3. RIGHT JOIN (RIGHT OUTER JOIN) #

A RIGHT JOIN returns all rows from the right table and the matched rows from the left table. If no match is found, NULLs are returned for columns from the left table.

Syntax #

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

Example #

Retrieve all departments and the employees in those departments, including departments with no employees.

SELECT employees.first_name, employees.last_name, departments.department_name
FROM employees
RIGHT JOIN departments ON employees.department_id = departments.department_id;

Result #

first_name	last_name	department_name
John	Doe	Sales
Jane	Smith	Marketing
NULL	NULL	IT
Alice	Brown	Sales

4. FULL JOIN (FULL OUTER JOIN) #

A FULL JOIN returns all rows from both tables. If there is no match, NULLs are returned for columns where there is no match in either table.

Syntax #

SELECT columns
FROM table1
FULL JOIN table2 ON table1.column = table2.column;

Example #

Retrieve all employees and departments, showing all possible matches.

SELECT employees.first_name, employees.last_name, departments.department_name
FROM employees
FULL JOIN departments ON employees.department_id = departments.department_id;

Result #

first_name	last_name	department_name
John	Doe	Sales
Jane	Smith	Marketing
Bob	Johnson	NULL
Alice	Brown	Sales
NULL	NULL	IT

5. CROSS JOIN #

A CROSS JOIN returns the Cartesian product of two tables, meaning it returns all possible combinations of rows from the tables.

Syntax #

SELECT columns
FROM table1
CROSS JOIN table2;

Example #

Retrieve all combinations of employees and departments.

SELECT employees.first_name, employees.last_name, departments.department_name
FROM employees
CROSS JOIN departments;

Result #

first_name	last_name	department_name
John	Doe	Sales
John	Doe	Marketing
John	Doe	IT
Jane	Smith	Sales
Jane	Smith	Marketing
Jane	Smith	IT
Bob	Johnson	Sales
Bob	Johnson	Marketing
Bob	Johnson	IT
Alice	Brown	Sales
Alice	Brown	Marketing
Alice	Brown	IT

6. SELF JOIN #

A SELF JOIN is a regular join but the table is joined with itself. This is useful for hierarchical data or comparing rows within the same table.

Syntax #

SELECT a.columns, b.columns
FROM table a
JOIN table b ON a.column = b.column;

Example #

Find pairs of employees who work in the same department.

SELECT e1.first_name AS employee1, e2.first_name AS employee2, e1.department_id
FROM employees e1
JOIN employees e2 ON e1.department_id = e2.department_id
WHERE e1.employee_id < e2.employee_id;

Result #

employee1	employee2	department_id
John	Alice	101

---

Conclusion #

Understanding SQL joins is fundamental for working with relational databases. Each join type serves different purposes, and mastering them allows you to handle complex queries involving multiple tables effectively. Practice these examples to gain confidence in using joins in various scenarios.