INNER JOIN in SQL Explained with Examples

Table of Contents

1. Introduction
2. What is an INNER JOIN?
3. Syntax of INNER JOIN
4. How INNER JOIN Works
5. INNER JOIN with Real Tables
6. INNER JOIN with Aliases
7. Filtering with INNER JOIN
8. INNER JOIN on Multiple Conditions
9. INNER JOIN with Aggregate Functions
10. INNER JOIN with GROUP BY
11. INNER JOIN Between Three or More Tables
12. INNER JOIN vs LEFT JOIN
13. Common Mistakes with INNER JOIN
14. Performance Tips
15. Best Practices
16. Real-World Examples
17. Sample Data Setup
18. Nested INNER JOINs
19. Using INNER JOIN in Subqueries
20. Summary and What’s Next

1. Introduction

The INNER JOIN is the most commonly used JOIN in SQL. It returns rows when there is a match in both joined tables. If you’re working with normalized databases, INNER JOIN is your best friend.

2. What is an INNER JOIN?

An INNER JOIN returns only the rows where matching values exist in both tables based on a specified condition.

3. Syntax of INNER JOIN

sqlCopyEditSELECT table1.column, table2.column
FROM table1
INNER JOIN table2
ON table1.common_column = table2.common_column;

4. How INNER JOIN Works

It performs a row-by-row comparison, returning only those pairs where the condition matches.

Think of it as the intersection of two datasets.

5. INNER JOIN with Real Tables

Suppose we have two tables:

customers

orders

sqlCopyEditSELECT c.name, o.total
FROM customers c
INNER JOIN orders o
ON c.id = o.customer_id;

Result:

Notice: Aditya and customer_id 4 (nonexistent in customers) are excluded.

6. INNER JOIN with Aliases

Aliases help shorten queries:

sqlCopyEditSELECT c.name, o.total
FROM customers AS c
INNER JOIN orders AS o
ON c.id = o.customer_id;

This is functionally the same and widely used in production SQL.

7. Filtering with INNER JOIN

Add WHERE for more specific results:

sqlCopyEditSELECT c.name, o.total
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
WHERE o.total > 500;

Returns customers with orders above 500.

8. INNER JOIN on Multiple Conditions

sqlCopyEditSELECT *
FROM a
INNER JOIN b
ON a.id = b.a_id AND a.status = b.status;

Used when join requires more than one field to match.

9. INNER JOIN with Aggregate Functions

sqlCopyEditSELECT c.name, COUNT(o.id) AS order_count
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
GROUP BY c.name;

Returns total number of orders per customer.

10. INNER JOIN with GROUP BY

You can combine JOINs with GROUP BY to get grouped metrics:

sqlCopyEditSELECT d.name AS department, AVG(e.salary) AS avg_salary
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id
GROUP BY d.name;

11. INNER JOIN Between Three or More Tables

sqlCopyEditSELECT e.name, d.name AS dept, l.city
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id
INNER JOIN locations l ON d.location_id = l.id;

This joins 3 tables: employees → departments → locations.

12. INNER JOIN vs LEFT JOIN

13. Common Mistakes with INNER JOIN

• Missing ON clause — leads to cross join
• Forgetting to qualify column names — ambiguity
• Expecting unmatched rows to appear — use LEFT JOIN instead
• Matching on incorrect columns — wrong results

14. Performance Tips

• Always index columns used in ON clause
• Avoid joining unnecessary columns
• Filter early using WHERE
• Use proper data types to avoid implicit type casting

15. Best Practices

• Use meaningful aliases
• Qualify columns: table.column
• Use INNER JOIN over WHERE-based joins (modern syntax)
• Avoid SELECT * in joins
• Comment complex logic if needed

16. Real-World Examples

Example 1: Customers and Orders

sqlCopyEditSELECT c.name, o.order_date
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id;

Example 2: Students and Courses

sqlCopyEditSELECT s.name, c.title
FROM students s
INNER JOIN enrollments e ON s.id = e.student_id
INNER JOIN courses c ON e.course_id = c.id;

17. Sample Data Setup

sqlCopyEditCREATE TABLE customers (
  id INT PRIMARY KEY,
  name VARCHAR(100)
);

CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT,
  total DECIMAL(10, 2)
);

18. Nested INNER JOINs

sqlCopyEditSELECT name, total
FROM (
  SELECT c.name, o.total
  FROM customers c
  INNER JOIN orders o ON c.id = o.customer_id
) AS summary
WHERE total > 500;

Useful for isolating complex subqueries.

19. Using INNER JOIN in Subqueries

sqlCopyEditSELECT *
FROM orders
WHERE customer_id IN (
  SELECT id FROM customers WHERE city = 'Delhi'
);

Can be rewritten as a more efficient INNER JOIN.

20. Summary and What’s Next

INNER JOIN is the backbone of combining tables in SQL. It ensures that only related records are retrieved — clean, fast, and accurate. When working with normalized databases, this is the most frequently used join.