Basics of selection I · Topic 4 Free
Removing duplicates: DISTINCT
Data model — the tables you will query
How the tables join
| Relationship | Cardinality | Join condition |
|---|---|---|
| students.cohort_id → cohorts.id | many students → one cohort | JOIN cohorts ON students.cohort_id = cohorts.id |
| enrollments.student_id → students.id | many enrollments → one student | JOIN students ON enrollments.student_id = students.id |
| enrollments.course_id → courses.id | many enrollments → one course | JOIN courses ON enrollments.course_id = courses.id |
| job_offers.student_id → students.id | many job offers → one student | JOIN students ON job_offers.student_id = students.id |
In short: each student belongs to one cohort. Students enroll in courses through enrollments, and their job offers land in job_offers.
Run SELECT country FROM students; and you get 30 rows — one per student, with Canada repeated many times. Often you want each value once.
DISTINCT
SELECT DISTINCT country
FROM students;DISTINCT removes duplicate rows from the result. Each country now appears exactly once.
DISTINCT over several columns
DISTINCT looks at the whole row, not the first column:
SELECT DISTINCT category, difficulty
FROM courses;This returns each unique pair. ('SQL', 'beginner') and ('SQL', 'intermediate') are different rows, so both stay.
Watch out for NULL
DISTINCT treats all NULLs as one value. If three students have no city, SELECT DISTINCT city FROM students; shows NULL once.
When not to use it
DISTINCT is sometimes used to hide a mistake — a JOIN that accidentally multiplied rows. If you find yourself adding DISTINCT to “fix” a query, first ask why the duplicates appeared. Counting unique values (COUNT(DISTINCT ...)) is coming up in the GROUP BY topic.
Postgres also has DISTINCT ON (...) — a powerful extension that keeps the first row per group. Remember it exists; we will meet it again with sorting.
Practice
A real Postgres database runs in your browser. Solve the tasks, or just experiment.