SQL INNER JOIN — Combining Rows from Multiple Tables

Student names are stored separately from subject results. The teacher needs to connect each result with the correct student.

An INNER JOIN produces a row for each pair of source rows that satisfies the join condition. Source rows with no matching partner do not appear.

Read the join condition as a relationship between two row sources. With ON s.roll_no = r.roll_no, each matching subject result pairs with its student. A student with three results can therefore appear three times; the join does not promise one output row per student.

Related concepts: LEFT JOIN and Foreign Keys.

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.

SQL · portable concepts; preserved examples identify their engine

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

SQL · portable concepts; preserved examples identify their engine

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

SQL · portable concepts; preserved examples identify their engine

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.

SQL · portable concepts; preserved examples identify their engine

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;

SQL · portable concepts; preserved examples identify their engine

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

SQL · portable concepts; preserved examples identify their engine

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

• 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.

A join can multiply rows when several records match on either side. Match on the intended key rather than a similar-looking name, and choose DISTINCT or grouping only when those operations match the required report.

Join the practice students to their Maths results. Explain why Diya is absent.

This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.

SQL · create only these practice tables in a disposable database:

CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');
CREATE TABLE practice_results (roll_no INTEGER, subject VARCHAR(30), marks INTEGER);
INSERT INTO practice_results VALUES (1,'Maths',85),(3,'Maths',85),(4,'Maths',92);

This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.

SQL · create only these practice tables in a disposable database:

CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');
CREATE TABLE practice_results (roll_no INTEGER, subject VARCHAR(30), marks INTEGER);
INSERT INTO practice_results VALUES (1,'Maths',85),(3,'Maths',85),(4,'Maths',92);

SQL · this practice variation runs with SQLite 3; the preserved examples retain their stated dialect

SELECT s.name, r.marks FROM practice_students AS s INNER JOIN practice_results AS r ON s.roll_no = r.roll_no WHERE r.subject = 'Maths' ORDER BY s.roll_no;

The results fixture has Maths rows for roll numbers 1 and 3. Diya has no matching result, so an inner join does not produce a row for her.

An INNER JOIN produces a row for each pair of source rows that satisfies the join condition. Source rows with no matching partner do not appear.

The results fixture has Maths rows for roll numbers 1 and 3. Diya has no matching result, so an inner join does not produce a row for her.

Continue your understanding with LEFT JOIN and Foreign Keys.

Source for the engine-specific behavior: MySQL 8.4 reference documentation.