The SQL JOIN clause is fundamental for retrieving data from multiple tables in a relational database. It works by combining rows based on a related column between them. Understanding the different types of joins is essential for any database professional, enabling you to query and analyze complex datasets effectively. A JOIN is a means to logically relate rows of one table to rows of another table. This is achieved by specifying a join condition, which is typically an equality comparison on one or more columns that exist in both tables (often the primary key of one table and the foreign key of the other).

Types of SQL JOINs

There are four primary types of JOINs in SQL, each serving a distinct purpose in how they combine data and handle unmatched rows.

2. Left Join (or Left Outer Join)

Returns all rows from the left table (TableA), and the matching rows from the right table (TableB).
If there is no match in the right table, NULL is returned for the right table’s columns.

Syntax:

SELECT columns
FROM TableA
LEFT JOIN TableB
ON TableA.key = TableB.key;

Examples

1. Customers and Orders
Retrieve all customers, including those who have never placed an order.

SELECT c.customer_id, c.customer_name, o.order_id, o.order_date
FROM customers c
LEFT JOIN orders o
    ON c.customer_id = o.customer_id;

2. Students and Enrollments
Retrieve all students, even if they are not enrolled in any course.

SELECT s.student_id, s.student_name, e.course_id
FROM students s
LEFT JOIN enrollments e
    ON s.student_id = e.student_id;

3. Employees and Departments
Retrieve all employees, including those not assigned to a department.

SELECT e.employee_id, e.employee_name, d.department_name
FROM employees e
LEFT JOIN departments d
    ON e.department_id = d.department_id;

4. Products and Sales
Retrieve all products, even products with no sales yet.

SELECT p.product_id, p.product_name, s.sale_id, s.sale_date
FROM products p
LEFT JOIN sales s
    ON p.product_id = s.product_id;

5. Blog Posts and Comments
Retrieve all blog posts, including posts with no comments.

SELECT p.post_id, p.title, c.comment_id, c.comment_text
FROM posts p
LEFT JOIN comments c
    ON p.post_id = c.post_id;

3. Right Join (or Right Outer Join)

Returns all rows from the right table (TableB), and the matching rows from the left table (TableA).
If there is no match in the left table, NULL is returned for the left table’s columns.

Syntax:

SELECT columns
FROM TableA
RIGHT JOIN TableB
ON TableA.key = TableB.key;

Examples

1. Orders and Customers
Retrieve all customers, including those who have never placed an order (customers are on the right).

SELECT o.order_id, o.order_date, c.customer_id, c.customer_name
FROM orders o
RIGHT JOIN customers c
    ON o.customer_id = c.customer_id;

2. Enrollments and Students
Retrieve all students, including those not enrolled in any course.

SELECT e.course_id, s.student_id, s.student_name
FROM enrollments e
RIGHT JOIN students s
    ON e.student_id = s.student_id;

3. Employees and Departments
Retrieve all departments, including departments with no employees.

SELECT e.employee_id, e.employee_name, d.department_name
FROM employees e
RIGHT JOIN departments d
    ON e.department_id = d.department_id;

4. Sales and Products
Retrieve all products, including products with no sales yet.

SELECT s.sale_id, s.sale_date, p.product_id, p.product_name
FROM sales s
RIGHT JOIN products p
    ON s.product_id = p.product_id;

5. Comments and Posts
Retrieve all blog posts, including posts with no comments.

SELECT c.comment_id, p.post_id, p.title
FROM comments c
RIGHT JOIN posts p
    ON c.post_id = p.post_id;

4. Full Join (or Full Outer Join)

Returns all rows when there is a match in either the left or the right table.
It effectively combines the results of a LEFT and a RIGHT join. Where there is no match, NULL values are used for the columns of the non-matching table.

Syntax:

SELECT columns
FROM TableA
FULL JOIN TableB
ON TableA.key = TableB.key;

Examples

1. Customers and Orders
Retrieve all customers and all orders, including customers with no orders and orders with no matching customer.

SELECT c.customer_id, c.customer_name,
       o.order_id, o.order_date
FROM customers c
FULL OUTER JOIN orders o
    ON c.customer_id = o.customer_id;

2. Students and Enrollments
Retrieve all students and all enrollments, including students not enrolled in any course and enrollments without a valid student.

SELECT s.student_id, s.student_name,
       e.course_id
FROM students s
FULL OUTER JOIN enrollments e
    ON s.student_id = e.student_id;

3. Employees and Departments
Retrieve all employees and all departments, including employees without departments and departments without employees.

SELECT e.employee_id, e.employee_name,
       d.department_id, d.department_name
FROM employees e
FULL OUTER JOIN departments d
    ON e.department_id = d.department_id;

4. Products and Sales
Retrieve all products and all sales, including products with no sales and sales referencing missing products.

SELECT p.product_id, p.product_name,
       s.sale_id, s.sale_date
FROM products p
FULL OUTER JOIN sales s
    ON p.product_id = s.product_id;

5. Blog Posts and Authors
Retrieve all blog posts and all authors, including posts without authors and authors without posts.

SELECT p.post_id, p.title,
       a.author_id, a.author_name
FROM posts p
FULL OUTER JOIN authors a
    ON p.author_id = a.author_id;

Note: Some databases (e.g., MySQL) don’t directly support FULL OUTER JOIN. It can be simulated using:

SELECT ...
FROM A
LEFT JOIN B ON ...
UNION
SELECT ...
FROM A
RIGHT JOIN B ON ...;

5. NON-EQUI JOIN

A Non-Equi Join is a join where the relationship between tables is based on a non-equal condition such as <, >, <=, >=, BETWEEN, or !=. It does not use = in the join condition.

Syntax

SELECT columns
FROM TableA a
JOIN TableB b
    ON a.column < > <= >= BETWEEN b.column;

Examples

1. Employees and Salary Grades Match employees to salary grades based on salary ranges.

SELECT e.employee_id, e.employee_name, e.salary, 
       g.grade_name
FROM employees e
JOIN salary_grades g
    ON e.salary BETWEEN g.min_salary AND g.max_salary;

2. Students and Score Levels Match students to a grade level based on their score.

SELECT s.student_id, s.student_name, s.score, l.level_name
FROM students s
JOIN score_levels l
    ON s.score >= l.min_score 
   AND s.score <  l.max_score;

3. Products and Price Categories Classify products into price categories.

SELECT p.product_id, p.product_name, p.price, c.category_name
FROM products p
JOIN price_categories c
    ON p.price BETWEEN c.lower_limit AND c.upper_limit;

4. Sales and Discounts Match a sale to a valid discount range.

SELECT s.sale_id, s.amount, d.discount_percent
FROM sales s
JOIN discounts d
    ON s.amount > d.min_purchase;

5. Orders and Delivery Slots Match orders to delivery slots based on time ranges.

SELECT o.order_id, o.order_time, d.slot_name
FROM orders o
JOIN delivery_slots d
    ON o.order_time BETWEEN d.start_time AND d.end_time;

6. Self Join

A Self Join is when a table joins with itself.
It is useful for comparing rows within the same table, such as hierarchical relationships (employees → managers), or finding duplicates.

Syntax

SELECT a.column, b.column
FROM TableName a
JOIN TableName b
    ON a.key = b.related_key;

Examples

1. Employees and Their Managers Match each employee with their manager (both stored in the same table).

SELECT e.employee_name AS employee,
       m.employee_name AS manager
FROM employees e
JOIN employees m
    ON e.manager_id = m.employee_id;

2. Students Scoring Higher Than Others Find pairs of students where one scored higher.

SELECT s1.student_name, s2.student_name
FROM students s1
JOIN students s2
    ON s1.score > s2.score;

3. Products With Same Price Find pairs of products that have the same price.

SELECT p1.product_name, p2.product_name, p1.price
FROM products p1
JOIN products p2
    ON p1.price = p2.price
   AND p1.product_id <> p2.product_id;

4. Employees at Same Department Find employees who work in the same department.

SELECT e1.employee_name, e2.employee_name, e1.department_id
FROM employees e1
JOIN employees e2
    ON e1.department_id = e2.department_id
   AND e1.employee_id <> e2.employee_id;

5. Customers Referenced by Other Customers Identify customers who referred other customers.

SELECT c1.customer_name AS referred_by,
       c2.customer_name AS new_customer
FROM customers c1
JOIN customers c2
    ON c1.customer_id = c2.referred_by;

7. Cross Join

A Cross Join returns the Cartesian product of two tables.
Every row in Table A is combined with every row in Table B.
If A has 5 rows and B has 10 rows → result is 50 rows.

Syntax

SELECT columns
FROM TableA
CROSS JOIN TableB;

Examples

1. Generate All Product–Category Combinations Useful for testing or generating lists.

SELECT p.product_name, c.category_name
FROM products p
CROSS JOIN categories c;

2. Students Paired With All Courses Show all possible student-course combinations.

SELECT s.student_name, c.course_name
FROM students s
CROSS JOIN courses c;

3. Employees With All Shifts Assign all employees to all possible shifts.

SELECT e.employee_name, sh.shift_name
FROM employees e
CROSS JOIN shifts sh;

4. All Dates × All Rooms Generate a schedule for all rooms on all dates.

SELECT d.date_value, r.room_name
FROM dates d
CROSS JOIN rooms r;

5. Promotions Applied to All Products Useful for marketing simulations.

SELECT p.product_name, promo.promo_name
FROM products p
CROSS JOIN promotions promo;

Features and Differences with Other Joins

JOIN Type	Result Set Focus	Missing Match Handling	Visual Representation (Venn)
INNER	Only rows with matches in both tables.	Excluded	Intersection
LEFT	All rows from Left table + matched rows from Right.	`NULL` for Right table columns	Left circle + Intersection
RIGHT	All rows from Right table + matched rows from Left.	`NULL` for Left table columns	Right circle + Intersection
FULL	All rows from both tables (matched or not).	`NULL` for non-matching columns	Union (both circles combined)

Non-Equi Joins and Self Joins

Non-Equi Join: A join where the condition is not based on equality (=), but uses other comparison operators like $>, <, \geq, \leq$, or $!=$.
- Example: Joining an Employees table to a Salary_Grade table to find the salary grade for each employee where the employee’s salary is between the grade’s $min_salary$ and $max_salary$.
Self Join: A join where a table is joined to itself. This requires aliasing the table to treat it as two separate logical entities.
- Example: Finding pairs of employees who work in the same department or listing an employee next to their manager (assuming the manager’s ID is stored as a foreign key in the employee table).

Real-World Example: An E-commerce Database

Imagine an e-commerce database with two simple tables:

Customers: customer_id (PK), name
Orders: order_id (PK), customer_id (FK), order_date

1. INNER JOIN Example (Matched Records Only)

Goal: Get a list of all orders that have a corresponding customer.

SELECT c.name, o.order_id, o.order_date
FROM Customers c
INNER JOIN Orders o
  ON c.customer_id = o.customer_id;

2. LEFT JOIN Example (Including Customers without Orders)

Goal: Get a list of ALL customers and any orders they have placed.

SELECT c.name, o.order_id
FROM Customers c
LEFT JOIN Orders o
  ON c.customer_id = o.customer_id;

Result: Customers who haven’t placed an order will show their name but have NULL for order_id.

3. Filtering for Unmatched Records (Inverse Joins)

A common use case is finding records that exist in one table but not the other. This is often done by using a LEFT JOIN and filtering for NULL values in the right table.

Goal: Find customers who have never placed an order.

SELECT c.name
FROM Customers c
LEFT JOIN Orders o
  ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL; -- Selects only the rows where the match failed

SQL Joins

(Connecting Relational Data)

Types of SQL JOINs

2. Left Join (or Left Outer Join)

Examples

3. Right Join (or Right Outer Join)

Examples

4. Full Join (or Full Outer Join)

Examples

5. NON-EQUI JOIN

Examples

6. Self Join

Examples

7. Cross Join

Examples

Features and Differences with Other Joins

Non-Equi Joins and Self Joins

Real-World Example: An E-commerce Database

1. INNER JOIN Example (Matched Records Only)

2. LEFT JOIN Example (Including Customers without Orders)

3. Filtering for Unmatched Records (Inverse Joins)