INNER JOIN

Sample schema

CREATE TABLE departments (
  code    varchar(10) PRIMARY KEY,
  name_en text NOT NULL,
  name_bn text,
  active  boolean DEFAULT true
);

CREATE TABLE enrollments (
  id         integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  student_id integer NOT NULL REFERENCES students(id) ON DELETE CASCADE,
  course_id  integer NOT NULL REFERENCES courses(id),
  grade      char(2)
);

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. Table aliases keep long queries readable and are required when two tables share a column name.

USING and NATURAL JOIN

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

USING merges the joined column so it appears once. NATURAL JOIN joins on every same-named column automatically — avoid it, because adding a column later can silently change the join condition.

Multiple joins

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;

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;

LATERAL joins

A LATERAL subquery can reference columns from tables to its left. This is how you write "top N per group" as a join, and it has no equivalent in older MySQL:

SELECT d.name_en, top.name_en, top.cgpa
FROM departments d
CROSS JOIN LATERAL (
  SELECT name_en, cgpa
  FROM students s
  WHERE s.department = d.code
  ORDER BY cgpa DESC
  LIMIT 3
) AS top;

Use LEFT JOIN LATERAL (…) ON true when you want to keep departments that have no students.

Join algorithms

Postgres chooses between three strategies, and EXPLAIN tells you which one it picked:

Plan node	Good for
Nested Loop	Small outer side with an index on the inner side
Hash Join	Large unsorted sets joined on equality
Merge Join	Both inputs already sorted on the join key

Performance rules

1. Index every column used in ON. Postgres does not automatically index foreign key columns — only the referenced primary key gets one.
2. Keep the joined columns the same type; a text to varchar join is fine, but int to bigint can prevent index use.
3. Filter early in WHERE so fewer rows reach the join.
4. Run ANALYZE after bulk changes; the planner picks bad joins when its statistics are stale.