INNER JOIN

A join combines rows from two tables based on a related column. INNER JOIN returns only the rows that match in both tables.

Sample schema

CREATE TABLE departments (
  code    VARCHAR(10) PRIMARY KEY,
  name_en VARCHAR(100) NOT NULL,
  name_bn VARCHAR(100)
);

CREATE TABLE enrollments (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  student_id INT NOT NULL,
  course_id  INT NOT NULL,
  grade      CHAR(2),
  FOREIGN KEY (student_id) REFERENCES students(id)
);

Basic INNER JOIN

SELECT s.name_en, d.name_en AS department
FROM students s
INNER JOIN departments d ON s.department = d.code;

INNER is optional — JOIN alone means INNER JOIN. Table aliases (s, d) keep long queries readable and are required when both tables share a column name.

USING

When the joining columns have the same name, USING is shorter and de-duplicates the column in the output:

SELECT student_id, name_en, grade
FROM students JOIN enrollments USING (student_id);

Joining three or more tables

SELECT
  s.name_en    AS student,
  c.title      AS course,
  e.grade,
  d.name_en    AS department
FROM enrollments e
JOIN students    s ON e.student_id = s.id
JOIN courses     c ON e.course_id  = c.id
JOIN departments d ON s.department = d.code
WHERE e.grade IS NOT NULL
ORDER BY s.name_en;

Each JOIN narrows the result: a student with no enrollment never appears.

Joins with aggregation

SELECT d.name_en AS department,
       COUNT(s.id) AS students,
       ROUND(AVG(s.cgpa), 2) AS avg_cgpa
FROM departments d
JOIN students s ON s.department = d.code
GROUP BY d.code, d.name_en
ORDER BY students DESC;

Non-equi joins

The ON condition does not have to be equality:

SELECT s.name_en, g.letter
FROM students s
JOIN grade_bands g ON s.cgpa BETWEEN g.min_cgpa AND g.max_cgpa;

Performance rules

1. Index every column used in ON — this is the single biggest join optimisation.
2. Foreign key columns should have the same type and collation as the referenced column, or MySQL cannot use the index.
3. Filter with WHERE to shrink the row sets before they are joined.
4. Read the plan with EXPLAIN and look for type: ALL, which means a full table scan.