SQL

INNER JOIN in SQL

🔗 INNER JOIN is the most commonly used join in SQL. It returns only the rows that have matching values in both tables. If a row in one table does not have a matching row in the other table, it is excluded from the result.

📖 What is INNER JOIN?

An INNER JOIN combines rows from two or more related tables based on a specified condition. Only records that satisfy the join condition are included in the final result set.

Information

JOIN and INNER JOIN are equivalent in SQL. If you simply write JOIN, SQL treats it as an INNER JOIN.

🎯 Why Use INNER JOIN?

Use INNER JOIN whenever you need data that exists in both tables.

📌 Retrieve related information from multiple tables.
📌 Eliminate unmatched records automatically.
📌 Build reports using normalized database tables.
📌 Improve data consistency by avoiding duplicate storage.

📊 Sample Tables

Consider the following Students table:

StudentID	Name	Department
101	Alice	Computer Science
102	Bob	Mathematics
103	Charlie	Physics
104	David	Computer Science

Consider the following Courses table:

CourseID	StudentID	CourseName
1	101	Database Systems
2	101	Operating Systems
3	103	Physics Lab
4	104	Computer Networks
5	105	Artificial Intelligence

📝 Basic Syntax

INNER JOIN Syntax

SELECT table1.column_name,
       table2.column_name
FROM table1
INNER JOIN table2
ON table1.common_column = table2.common_column;

💡 Basic INNER JOIN Example

Retrieve each student's name along with their enrolled course.

Basic INNER JOIN

SELECT Students.Name,
       Courses.CourseName
FROM Students
INNER JOIN Courses
ON Students.StudentID = Courses.StudentID;

Result:

Name	CourseName
Alice	Database Systems
Alice	Operating Systems
Charlie	Physics Lab
David	Computer Networks

Important

Student Bob does not appear because there is no matching StudentID in the Courses table. Likewise, the course with StudentID = 105 does not appear because there is no matching student.

🏷️ Using Table Aliases

Table aliases make join queries shorter and easier to read.

INNER JOIN with Aliases

SELECT s.Name,
       c.CourseName
FROM Students AS s
INNER JOIN Courses AS c
ON s.StudentID = c.StudentID;

🔍 INNER JOIN with WHERE

Filter the joined results using the WHERE clause.

Filter Joined Data

SELECT s.Name,
       c.CourseName
FROM Students s
INNER JOIN Courses c
ON s.StudentID = c.StudentID
WHERE s.Department = 'Computer Science';

📊 INNER JOIN with ORDER BY

Sort the joined result set using the ORDER BY clause.

Sort Joined Results

SELECT s.Name,
       c.CourseName
FROM Students s
INNER JOIN Courses c
ON s.StudentID = c.StudentID
ORDER BY s.Name ASC;

🔗 INNER JOIN Multiple Tables

You can join more than two tables in a single query.

Joining Three Tables

SELECT s.Name,
       c.CourseName,
       d.DepartmentName
FROM Students s
INNER JOIN Courses c
ON s.StudentID = c.StudentID
INNER JOIN Departments d
ON s.DepartmentID = d.DepartmentID;

📊 How INNER JOIN Works

Students Table	Courses Table	Included?
Matching StudentID	Matching StudentID	✅ Yes
No Match	Matching Row	❌ No
Matching Row	No Match	❌ No

⚖️ INNER JOIN vs Other Joins

Join Type	Rows Returned
INNER JOIN	Only matching rows from both tables.
LEFT JOIN	All rows from the left table and matching rows from the right table.
RIGHT JOIN	All rows from the right table and matching rows from the left table.
FULL OUTER JOIN	All rows from both tables, regardless of matches.

⚠️ Common Mistakes

❌ Joining on unrelated columns.
❌ Forgetting the ON clause.
❌ Using SELECT * when only a few columns are needed.
❌ Not qualifying columns that exist in multiple tables.

Warning

When multiple tables contain columns with the same name, always qualify them using the table name or an alias, such as s.StudentID.

💼 Real-World Example

A university administrator wants a report showing every enrolled student's name, department, and course.

Student Enrollment Report

SELECT s.Name,
       s.Department,
       c.CourseName
FROM Students s
INNER JOIN Courses c
ON s.StudentID = c.StudentID
ORDER BY s.Name;

This query combines data from both tables and displays only students who have course enrollments.

⚠️ Best Practices

Best Practice

Use INNER JOIN when you need only matching records from related tables. Join using primary and foreign keys whenever possible, use table aliases to improve readability, retrieve only the required columns, and ensure the join condition accurately reflects the relationship between the tables.

🚀 Key Points to Remember

📌 INNER JOIN returns only matching rows.
📌 Unmatched rows from either table are excluded.
📌 JOIN is the same as INNER JOIN.
📌 Joins are usually based on primary and foreign keys.
📌 Table aliases improve query readability.
📌 Multiple tables can be joined in a single query.

>>"An INNER JOIN reveals only the data that successfully connects across related tables."

Summary

✅ The INNER JOIN is the foundation of relational database queries. It combines related records from multiple tables by returning only matching rows, making it an essential tool for reporting, data analysis, and real-world database applications.