SQL INNER JOIN - Combining Rows from Multiple Tables

Introduction

Think of a school report that combines student names with their exam results — those two pieces of information live in separate tables. To bring them together in one result, we use INNER JOIN. It’s one of the most common and powerful SQL operations.

What is INNER JOIN?

INNER JOIN returns only the rows where there is a match in both joined tables. If a student has no exam record, or an exam record has no corresponding student — that row won’t appear.

Syntax of INNER JOIN

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

1. Sample Tables – Students and Results

Table: students

CREATE TABLE students (
  roll_no INT PRIMARY KEY,
  name VARCHAR(50),
  class VARCHAR(10)
);

INSERT INTO students VALUES
(1, 'Aarav Sharma', '10A'),
(2, 'Diya Iyer', '9B'),
(3, 'Sneha Patil', '10A'),
(4, 'Mehul Agarwal', '8C');

Table: results

CREATE TABLE results (
  roll_no INT,
  subject VARCHAR(30),
  marks INT
);

INSERT INTO results VALUES
(1, 'Maths', 85),
(2, 'Maths', 92),
(3, 'Maths', 78),
(5, 'Maths', 88); -- roll_no 5 doesn't exist in students

2. Basic INNER JOIN Example

Let’s join students with results on roll_no:

SELECT s.name, s.class, r.subject, r.marks
FROM students AS s
INNER JOIN results AS r
ON s.roll_no = r.roll_no;

name           | class | subject | marks
----------------+--------+---------+-------
Aarav Sharma   | 10A   | Maths   | 85
Diya Iyer      | 9B    | Maths   | 92
Sneha Patil    | 10A   | Maths   | 78

Note: The row with roll_no = 5 in results is ignored because there’s no matching student.

3. Use Case – Full Student Report

Let’s format a full report with aliases:

SELECT s.name AS student_name, s.class,
        r.subject, r.marks
FROM students s
INNER JOIN results r ON s.roll_no = r.roll_no;

4. Filtering Joined Results

Show only Class 10A students with their marks:

SELECT s.name, r.marks
FROM students s
INNER JOIN results r ON s.roll_no = r.roll_no
WHERE s.class = '10A';

name           | marks
----------------+-------
Aarav Sharma   | 85
Sneha Patil    | 78

5. INNER JOIN with Multiple Subjects

INSERT INTO results VALUES
(1, 'Science', 90),
(2, 'Science', 88),
(3, 'Science', 85);

SELECT s.name, r.subject, r.marks
FROM students s
INNER JOIN results r ON s.roll_no = r.roll_no
ORDER BY s.name, r.subject;

name           | subject | marks
----------------+---------+-------
Aarav Sharma   | Maths   | 85
Aarav Sharma   | Science | 90
Diya Iyer      | Maths   | 92
Diya Iyer      | Science | 88
Sneha Patil    | Maths   | 78
Sneha Patil    | Science | 85

Best Practices

• Always use ON clause with clear matching columns to avoid cartesian products.
• Use table aliases for cleaner and shorter queries, especially with long table names.
• Validate data before joining — unmatched records will not appear in INNER JOIN results.

Summary

The INNER JOIN clause is your gateway to combining data across related tables. Whether you're generating student performance reports, matching users with logins, or linking sales with products — INNER JOIN brings it all together when keys match.

What’s Next?

Next, we’ll explore LEFT JOIN — a way to include unmatched records from the left table for more inclusive reports.