SQL Joins

Introduction

In this chapter, we will learn about SQL Joins, which are used to combine rows from two or more tables based on a related column between them. Joins are fundamental for querying data from multiple tables in relational databases. This chapter will cover different types of joins, their syntax, explanations, and provide examples to help you understand how to use joins effectively.

Sample Tables and Data

Create Sample Tables

First, let's create two sample tables named employees and departments.

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50)
);

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    department_id INT,
    salary DECIMAL(10, 2),
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

Insert Sample Data

INSERT INTO departments (department_id, department_name)
VALUES 
(1, 'HR'),
(2, 'Finance'),
(3, 'IT');

INSERT INTO employees (first_name, last_name, email, department_id, salary)
VALUES 
('Ramesh', 'Kumar', 'ramesh.kumar@example.com', 1, 50000),
('Sita', 'Patel', 'sita.patel@example.com', 2, 60000),
('Arjun', 'Singh', 'arjun.singh@example.com', 1, 55000),
('Priya', 'Sharma', 'priya.sharma@example.com', 2, 62000),
('Ravi', 'Verma', 'ravi.verma@example.com', NULL, 50000);

Types of Joins

1. INNER JOIN

Definition: The INNER JOIN keyword selects records that have matching values in both tables. If there is no match between the columns, the row will not be included in the result set.

Syntax:

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

Example: To retrieve employees along 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;

Output:

first_name	last_name	department_name
Ramesh	Kumar	HR
Sita	Patel	Finance
Arjun	Singh	HR
Priya	Sharma	Finance

2. LEFT JOIN (LEFT OUTER JOIN)

Definition: The LEFT JOIN keyword returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL from the right side if there is no match.

Syntax:

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

Example: To 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;

Output:

first_name	last_name	department_name
Ramesh	Kumar	HR
Sita	Patel	Finance
Arjun	Singh	HR
Priya	Sharma	Finance
Ravi	Verma	NULL

3. RIGHT JOIN (RIGHT OUTER JOIN)

Definition: The RIGHT JOIN keyword returns all records from the right table (table2), and the matched records from the left table (table1). The result is NULL from the left side if there is no match.

Syntax:

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

Example: To retrieve all departments and their employees (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;

Output:

first_name	last_name	department_name
Ramesh	Kumar	HR
Sita	Patel	Finance
Arjun	Singh	HR
Priya	Sharma	Finance
NULL	NULL	IT

4. FULL JOIN (FULL OUTER JOIN)

Definition: The FULL JOIN keyword returns all records when there is a match in either left (table1) or right (table2) table. It returns NULL for non-matching rows from both tables.

Syntax:

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

Example: To retrieve all employees and departments, including those without matches in the other table (not supported by all SQL databases):

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

5. CROSS JOIN

Definition: The CROSS JOIN keyword returns the Cartesian product of the two tables, which means it returns all possible combinations of rows from the two tables.

Syntax:

SELECT columns
FROM table1
CROSS JOIN table2;

Example: To retrieve all combinations of employees and departments:

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

6. SELF JOIN

Definition: The SELF JOIN keyword is used to join a table to itself as if it were two tables, temporarily renaming at least one table in the SQL statement.

Syntax:

SELECT a.columns, b.columns
FROM table a, table b
WHERE a.common_column = b.common_column;

Example: To retrieve pairs of employees who have 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.id <> e2.id;

Conclusion

Joins are a fundamental concept in SQL for combining data from multiple tables. This chapter covered the different types of joins, their syntax, and examples to illustrate their use. Understanding how to use joins effectively will greatly enhance your ability to query and analyze data across multiple tables in a relational database.