Table of Contents
- Introduction
- What is an INNER JOIN?
- Syntax of INNER JOIN
- How INNER JOIN Works
- INNER JOIN with Real Tables
- INNER JOIN with Aliases
- Filtering with INNER JOIN
- INNER JOIN on Multiple Conditions
- INNER JOIN with Aggregate Functions
- INNER JOIN with GROUP BY
- INNER JOIN Between Three or More Tables
- INNER JOIN vs LEFT JOIN
- Common Mistakes with INNER JOIN
- Performance Tips
- Best Practices
- Real-World Examples
- Sample Data Setup
- Nested INNER JOINs
- Using INNER JOIN in Subqueries
- 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
| id | name |
|---|---|
| 1 | Ravi |
| 2 | Sneha |
| 3 | Aditya |
orders
| id | customer_id | total |
|---|---|---|
| 1 | 1 | 500 |
| 2 | 2 | 1000 |
| 3 | 4 | 300 |
sqlCopyEditSELECT c.name, o.total
FROM customers c
INNER JOIN orders o
ON c.id = o.customer_id;
Result:
| name | total |
|---|---|
| Ravi | 500 |
| Sneha | 1000 |
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
| Feature | INNER JOIN | LEFT JOIN |
|---|---|---|
| Match only | Returns only matches | Returns all from left table |
| NULL values | Skipped if no match | Shown as NULL if no match |
| Performance | Slightly faster | Slightly heavier |
13. Common Mistakes with INNER JOIN
- Missing
ONclause — leads to cross join - Forgetting to qualify column names — ambiguity
- Expecting unmatched rows to appear — use
LEFT JOINinstead - Matching on incorrect columns — wrong results
14. Performance Tips
- Always index columns used in
ONclause - 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 JOINoverWHERE-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.