MySQL INNER JOIN

An INNER JOIN combines rows from two tables when the specified join condition matches.

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

Only students having a matching course are returned.

INNER JOIN is useful when related information is stored in separate tables.

• Students and courses
• Customers and orders
• Employees and departments
• Products and categories
• Students and payments
• Users and profiles

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

Example:

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

The ON clause specifies how the two tables are related.

ON students.course_id = courses.id

Here:

• students.course_id is the foreign key.
• courses.id is the related key in the courses table.
• Rows are matched when these values are equal.

students table:

courses table:

The course_id column connects the two tables.

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

Result:

Only matching records are returned.

In MySQL, JOIN without another join type means INNER JOIN.

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

This produces the same type of result as:

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

Aliases make JOIN queries shorter and easier to read.

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

Here:

• s represents students.
• c represents courses.

You can select columns from both tables.

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

This displays student and course information together.

WHERE can be used to filter the joined result.

SELECT
    s.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 only students enrolled in Python.

ORDER BY can sort the results of an INNER JOIN.

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

This sorts the joined results by student name.

LIMIT can restrict the number of rows returned by a JOIN query.

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

This returns a maximum of 10 matching rows.

You can join more than two tables.

SELECT
    s.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.student_id = p.student_id;

This can display student, course, and payment information together.

INNER JOIN can be combined with aggregate functions.

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

This counts students for each course that has matching student records.

You can calculate totals from joined tables.

SELECT
    s.name,
    SUM(p.amount) AS total_paid
FROM students AS s
INNER JOIN payments AS p
ON s.student_id = p.student_id
GROUP BY s.student_id, s.name;

This calculates the total payment for each student who has matching payment records.

GROUP BY can summarize information from joined tables.

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

This creates a course-wise summary.

HAVING can filter grouped JOIN results.

SELECT
    c.course_name,
    COUNT(s.student_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
HAVING COUNT(s.student_id) > 5;

This returns courses having more than five matching students.

INNER JOIN is commonly based on a primary key and a related foreign key.

CREATE TABLE courses (
    id INT PRIMARY KEY,
    course_name VARCHAR(100)
);

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100),
    course_id INT,
    FOREIGN KEY (course_id)
    REFERENCES courses(id)
);

The course_id column connects students to courses.

INNER JOIN returns only matching records.

Suppose a student has:

course_id = 999

but there is no course with:

id = 999

That student's row will not appear in the INNER JOIN result.

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

For example:

students.course_id
courses.id

They can still be joined using:

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

The ON clause can contain more than one condition.

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

This joins students only to active courses.

AND can also be used in the WHERE clause after the JOIN.

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

This returns active students enrolled in Python.

A common practical example is joining students and payments.

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

Only students having matching payment records are returned.

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

This creates a simple student-course report.

You can calculate values using columns from joined tables.

SELECT
    s.name,
    c.course_name,
    c.fee,
    s.paid_fee,
    c.fee - s.paid_fee AS due_amount
FROM students s
INNER JOIN courses c
ON s.course_id = c.id;

This calculates the remaining amount for each matching student-course record.

Multiple tables can be joined and grouped for reports.

SELECT
    c.course_name,
    COUNT(DISTINCT s.student_id) AS total_students,
    SUM(p.amount) AS total_paid
FROM courses c
INNER JOIN students s
ON c.id = s.course_id
INNER JOIN payments p
ON s.student_id = p.student_id
GROUP BY c.id, c.course_name;

This creates a course-wise payment report for students having matching payment records.

When a student has multiple matching payment records, the JOIN can return multiple rows for that student.

SELECT
    s.name,
    p.amount
FROM students s
INNER JOIN payments p
ON s.student_id = p.student_id;

If one student has three payments, that student can appear in three result rows.

Use GROUP BY with aggregate functions when you need one summarized row per student.

A simplified logical processing order for a JOIN query is:

FROM
JOIN
ON
WHERE
GROUP BY
HAVING
SELECT
ORDER BY
LIMIT

Example:

SELECT
    c.course_name,
    COUNT(s.student_id) AS total_students
FROM courses c
INNER JOIN students s
ON c.id = s.course_id
WHERE s.status = 'Active'
GROUP BY c.id, c.course_name
HAVING COUNT(s.student_id) > 2
ORDER BY total_students DESC;

The next lessons will explain LEFT JOIN and RIGHT JOIN in detail.

CREATE TABLE courses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    course_name VARCHAR(100),
    fee DECIMAL(10,2)
);

CREATE TABLE students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    course_id INT,
    city VARCHAR(100),
    status VARCHAR(20),
    paid_fee DECIMAL(10,2)
);

CREATE TABLE payments (
    payment_id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT,
    amount DECIMAL(10,2),
    payment_date DATE
);

INSERT INTO courses
(course_name, fee)
VALUES
('Python', 15000),
('Java', 18000),
('PHP', 12000);

INSERT INTO students
(name, course_id, city, status, paid_fee)
VALUES
('Amit Kumar', 1, 'Patna', 'Active', 10000),
('Priya Singh', 2, 'Gaya', 'Active', 18000),
('Rahul Sharma', 1, 'Patna', 'Active', 7000),
('Neha Kumari', 3, 'Patna', 'Inactive', 5000);

INSERT INTO payments
(student_id, amount, payment_date)
VALUES
(1, 5000, '2026-01-10'),
(1, 5000, '2026-02-10'),
(2, 18000, '2026-03-15'),
(3, 7000, '2026-04-20');

-- Student and course details
SELECT
    s.student_id,
    s.name,
    c.course_name,
    c.fee
FROM students s
INNER JOIN courses c
ON s.course_id = c.id;

-- Student payment details
SELECT
    s.name,
    c.course_name,
    p.amount,
    p.payment_date
FROM students s
INNER JOIN courses c
ON s.course_id = c.id
INNER JOIN payments p
ON s.student_id = p.student_id;

-- Course-wise student count
SELECT
    c.course_name,
    COUNT(s.student_id) AS total_students
FROM courses c
INNER JOIN students s
ON c.id = s.course_id
GROUP BY c.id, c.course_name;

-- Course-wise payment total
SELECT
    c.course_name,
    SUM(p.amount) AS total_paid
FROM courses c
INNER JOIN students s
ON c.id = s.course_id
INNER JOIN payments p
ON s.student_id = p.student_id
GROUP BY c.id, c.course_name;

This example demonstrates INNER JOIN with two tables, three tables, aggregate functions, and GROUP BY.