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.

INNER JOIN (only matching rows)

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

LEFT JOIN (all left + matching right)

-- All users, with their progress (NULL if none)
SELECT
  u.email,
  COUNT(p.id) AS completed_courses,
  COALESCE(SUM(p.xp_earned), 0) AS total_xp
FROM auth.users u
LEFT JOIN progress p ON p.user_id = u.id
GROUP BY u.id, u.email
ORDER BY total_xp DESC;

Three-Table Join

-- Course details with user progress and profile
SELECT
  c.title,
  c.xp AS course_xp,
  pr.display_name,
  p.completed_at,
  p.quiz_score
FROM progress p
INNER JOIN courses c ON c.id = p.course_id
INNER JOIN profiles pr ON pr.id = p.user_id
WHERE p.user_id = 'uuid-here'
ORDER BY p.completed_at DESC;

Self-Join

-- Find users who completed the same course as a given user
SELECT DISTINCT u2.email
FROM progress p1
INNER JOIN progress p2 ON p2.course_id = p1.course_id AND p2.user_id != p1.user_id
INNER JOIN auth.users u2 ON u2.id = p2.user_id
WHERE p1.user_id = 'uuid-here';
0%