JSTAcademy
0 XP
Dashboard
Crash Courses
Joins & Relationships
11 min
Basic+150 XP
Crash Courses · Basic

Joins & Relationships

Query data across related tables with INNER, LEFT, and other JOIN types.
11 min read+150 XP on completionCert: PostgreSQL Crash Course
Tap any word in the text below to start reading from there.

Joins & Relationships

Joins combine data from related tables. Understanding INNER vs LEFT JOIN is essential for any non-trivial query.

Why Joins Exist

Data is split across tables to eliminate redundancy. But to display meaningful information — "Jordan completed PostgreSQL Fundamentals on Monday" — you need to pull Jordan's name from users, the course title from courses, and the completion date from enrollments. Joins combine these tables into a single result set.

INNER JOIN (only matching rows)

-- Users who have completed courses
SELECT
  u.email,
  e.course_id,
  e.xp_earned,
  e.completed_at
FROM enrollments e
INNER JOIN users u ON u.id = e.user_id
WHERE e.course_id LIKE 'cc-%'
ORDER BY e.completed_at DESC;

INNER JOIN excludes rows that have no match. If a user has no enrollments, they do not appear in this result.

LEFT JOIN (all left + matching right)

-- ALL users, with their enrollment counts (even users with zero completions)
SELECT
  u.email,
  COUNT(e.id) AS completed_courses,
  COALESCE(SUM(e.xp_earned), 0) AS total_xp
FROM users u
LEFT JOIN enrollments e ON e.user_id = u.id
GROUP BY u.id, u.email
ORDER BY total_xp DESC;

LEFT JOIN keeps every row from the left table (users). Users with no enrollments appear with NULL for the enrollment columns — COALESCE converts NULL to 0.

Three-Table Join

-- Full enrollment details: course title, user name, completion date
SELECT
  c.title,
  c.xp AS course_xp,
  u.display_name,
  e.completed_at,
  e.quiz_score
FROM enrollments e
INNER JOIN courses c ON c.id = e.course_id
INNER JOIN users   u ON u.id = e.user_id
WHERE u.email = 'jordan@example.com'
ORDER BY e.completed_at DESC;

Each JOIN adds another table. Chain as many as needed. Use table aliases (e, c, u) to keep the query readable.

Self-Join

-- Find users who completed the same course as a given user
SELECT DISTINCT u2.email
FROM enrollments e1
INNER JOIN enrollments e2 ON e2.course_id = e1.course_id
                          AND e2.user_id != e1.user_id
INNER JOIN users u2 ON u2.id = e2.user_id
WHERE e1.user_id = 'uuid-here';

A self-join joins a table to itself using aliases. Here e1 is the given user's enrollments; e2 finds other users in the same courses.

0%