SQL INNER JOIN

An INNER JOIN combines records from two tables when a matching value exists in both tables.

SELECT *
FROM students
INNER JOIN courses
ON students.course_id = courses.id;

Only students having a matching course are returned.

The basic syntax is:

SELECT columns
FROM table1
INNER JOIN table2
ON table1.column = table2.column;

The ON clause specifies how the tables are related.

Suppose we have students and courses tables.

SELECT
    students.student_name,
    courses.course_name
FROM students
INNER JOIN courses
ON students.course_id = courses.id;

This displays each student's name along with the matching course name.

The ON clause defines the relationship between the tables.

SELECT *
FROM students
INNER JOIN courses
ON students.course_id = courses.id;

Here, students.course_id is matched with courses.id.

It is usually better to select only the columns that are required.

SELECT
    students.student_name,
    students.mobile,
    courses.course_name
FROM students
INNER JOIN courses
ON students.course_id = courses.id;

Table aliases make INNER JOIN queries shorter and easier to read.

SELECT
    s.student_name,
    c.course_name
FROM students AS s
INNER JOIN courses AS c
ON s.course_id = c.id;

Here, s represents students and c represents courses.

An INNER JOIN can be combined with a WHERE condition.

SELECT
    s.student_name,
    c.course_name
FROM students AS s
INNER JOIN courses AS c
ON s.course_id = c.id
WHERE c.course_name = 'Python';

This returns students enrolled in the Python course.

We can sort the joined result using ORDER BY.

SELECT
    s.student_name,
    c.course_name
FROM students AS s
INNER JOIN courses AS c
ON s.course_id = c.id
ORDER BY s.student_name;

Multiple conditions can be used in the ON clause.

SELECT
    s.student_name,
    c.course_name
FROM students AS s
INNER JOIN courses AS c
ON s.course_id = c.id
AND c.status = 'Active';

Only students connected to active courses are returned.

INNER JOIN can combine more than two tables.

SELECT
    s.student_name,
    c.course_name,
    p.amount
FROM students AS s
INNER JOIN courses AS c
ON s.course_id = c.id
INNER JOIN payments AS p
ON s.id = p.student_id;

This combines students, courses, and payments.

Multiple related tables can be joined in the same query.

SELECT
    s.student_name,
    c.course_name,
    p.amount,
    a.attendance_date
FROM students AS s
INNER JOIN courses AS c
ON s.course_id = c.id
INNER JOIN payments AS p
ON s.id = p.student_id
INNER JOIN attendance AS a
ON s.id = a.student_id;

INNER JOIN can be combined with aggregate functions such as COUNT() and SUM().

SELECT
    c.course_name,
    COUNT(s.id) AS total_students
FROM courses AS c
INNER JOIN students AS s
ON c.id = s.course_id
GROUP BY c.id, c.course_name;

This shows the number of students in each course that has matching students.

GROUP BY can be used to create reports from joined tables.

SELECT
    c.course_name,
    COUNT(s.id) AS total_students
FROM students AS s
INNER JOIN courses AS c
ON s.course_id = c.id
GROUP BY c.course_name;

HAVING can filter grouped results after an INNER JOIN.

SELECT
    c.course_name,
    COUNT(s.id) AS total_students
FROM students AS s
INNER JOIN courses AS c
ON s.course_id = c.id
GROUP BY c.course_name
HAVING total_students > 5;

This returns courses having more than five matching students.

INNER JOIN is useful for displaying student payment information.

SELECT
    s.student_name,
    p.amount,
    p.payment_date
FROM students AS s
INNER JOIN payments AS p
ON s.id = p.student_id;

Only students having matching payment records are displayed.

We can combine student, course, and payment information.

SELECT
    s.student_name,
    c.course_name,
    p.amount,
    p.payment_date
FROM students AS s
INNER JOIN courses AS c
ON s.course_id = c.id
INNER JOIN payments AS p
ON s.id = p.student_id;

Date conditions can be applied to joined data.

SELECT
    s.student_name,
    p.amount,
    p.payment_date
FROM students AS s
INNER JOIN payments AS p
ON s.id = p.student_id
WHERE p.payment_date >= '2026-01-01';

This returns matching payments from January 1, 2026 onward.

Calculations can be performed on columns from joined tables.

SELECT
    s.student_name,
    c.course_name,
    c.fee - COALESCE(s.paid_fee, 0) AS pending_fee
FROM students AS s
INNER JOIN courses AS c
ON s.course_id = c.id;

The query calculates the pending fee using data from both tables.

CASE can be used with an INNER JOIN to create meaningful categories.

SELECT
    s.student_name,
    c.course_name,
    CASE
        WHEN s.paid_fee >= c.fee THEN 'Paid'
        ELSE 'Pending'
    END AS fee_status
FROM students AS s
INNER JOIN courses AS c
ON s.course_id = c.id;

INNER JOIN is commonly used with tables connected through foreign keys.

SELECT
    s.student_name,
    c.course_name
FROM students AS s
INNER JOIN courses AS c
ON s.course_id = c.id;

Here, students.course_id can reference the primary key courses.id.

The related columns do not have to have the same name.

SELECT
    s.student_name,
    c.course_name
FROM students AS s
INNER JOIN courses AS c
ON s.course_code = c.code;

The important requirement is that the values used for matching are related.

String conditions can be applied after joining tables.

SELECT
    s.student_name,
    c.course_name
FROM students AS s
INNER JOIN courses AS c
ON s.course_id = c.id
WHERE c.course_name LIKE 'Python%';

The IN operator can filter joined records.

SELECT
    s.student_name,
    c.course_name
FROM students AS s
INNER JOIN courses AS c
ON s.course_id = c.id
WHERE c.course_name IN ('Python', 'Java', 'PHP');

BETWEEN can filter numeric values from joined tables.

SELECT
    s.student_name,
    p.amount
FROM students AS s
INNER JOIN payments AS p
ON s.id = p.student_id
WHERE p.amount BETWEEN 1000 AND 5000;

DISTINCT can be used to remove duplicate combinations from joined results.

SELECT DISTINCT
    c.course_name
FROM students AS s
INNER JOIN courses AS c
ON s.course_id = c.id;

This displays each course only once.

INNER JOIN: Returns only matching records from both tables.

LEFT JOIN: Returns all records from the left table and matching records from the right table.

-- INNER JOIN
SELECT *
FROM students s
INNER JOIN courses c
ON s.course_id = c.id;

A common mistake is using the wrong columns in the ON condition.

-- Correct
SELECT
    s.student_name,
    c.course_name
FROM students s
INNER JOIN courses c
ON s.course_id = c.id;

Always verify that the columns used for joining actually represent the relationship between the tables.

If a row in one table does not have a matching row in the other table, INNER JOIN does not return that row.

SELECT
    s.student_name,
    c.course_name
FROM students s
INNER JOIN courses c
ON s.course_id = c.id;

Students whose course_id has no matching course ID will not appear in the result.

A practical student-course report can be created using INNER JOIN.

SELECT
    s.student_id,
    s.student_name,
    s.mobile,
    c.course_name,
    c.fee
FROM students AS s
INNER JOIN courses AS c
ON s.course_id = c.id
ORDER BY s.student_name;

This displays student information along with their matching course information.

The following example combines students, courses, and payments to create a useful report.

SELECT
    s.student_id,
    s.student_name,
    c.course_name,
    c.fee AS course_fee,
    p.amount AS paid_amount,
    p.payment_date
FROM students AS s
INNER JOIN courses AS c
ON s.course_id = c.id
INNER JOIN payments AS p
ON s.id = p.student_id
WHERE p.payment_date >= '2026-01-01'
ORDER BY p.payment_date DESC;

This query returns students who have matching courses and payment records from January 1, 2026 onward.