Reading and filtering data 40 min

SQL DISTINCT

Remove repeated projected rows without changing stored data, and project only the columns whose duplicates should collapse.

DISTINCT collapses duplicate result rows

Unique valuesSELECT DISTINCT category returns each category once.

Whole rowTwo result rows collapse only when every selected value matches.

Not a join fixUnexpected duplicates usually mean the query asked the wrong question.

Display orderORDER BY still belongs on the unique list when the sequence should be a contract.

SOURCEcourses10 stored rows

Categories repeat because many courses share Data, Web, or Backend.

PROJECTSELECT DISTINCT categoryunique values

Repeated category values collapse in the result, not in the table.

ORDERORDER BY category ASCBackend, Data, Web

The short list still needs an explicit display order.

SELECT DISTINCT returns unique projected values

The courses table has ten rows but only three category values: Backend, Data, and Web. SELECT DISTINCT category returns each category once. It does not delete duplicate source rows, and it does not mean that categories are unique in the table. The final ORDER BY category gives the short list a predictable display order.

SQL BROWSER RUNNER

List unique category values

Turn repeated course categories into a three-row result.

CREATE TABLE courses (
  course_id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  category TEXT NOT NULL,
  status TEXT NOT NULL,
  duration_minutes INTEGER NOT NULL,
  price_cents INTEGER NOT NULL,
  rating REAL
);

INSERT INTO courses
  (course_id, title, category, status, duration_minutes, price_cents, rating)
VALUES
  (101, 'SQL Foundations', 'Data', 'published', 90, 0, 4.8),
  (102, 'Data Modeling', 'Data', 'published', 120, 2550, 4.6),
  (103, 'CSS Layouts', 'Web', 'published', 75, 0, 4.2),
  (104, 'JavaScript DOM', 'Web', 'draft', 105, 2000, NULL),
  (105, 'Python APIs', 'Backend', 'published', 150, 3900, 4.9),
  (106, 'Query Tuning', 'Data', 'draft', 135, 4500, NULL),
  (107, 'Accessible UI', 'Web', 'published', 60, 1200, NULL),
  (108, 'Transactions', 'Backend', 'published', 110, 3000, 4.4),
  (109, 'HTML Semantics', 'Web', 'published', 75, 0, 4.2),
  (110, 'Intro to SQL', 'Data', 'published', 90, 0, 4.8);

SELECT DISTINCT category
FROM courses
ORDER BY category ASC;

CREATE TABLE courses (
  course_id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  category TEXT NOT NULL,
  status TEXT NOT NULL,
  duration_minutes INTEGER NOT NULL,
  price_cents INTEGER NOT NULL,
  rating REAL
);

INSERT INTO courses
  (course_id, title, category, status, duration_minutes, price_cents, rating)
VALUES
  (101, 'SQL Foundations', 'Data', 'published', 90, 0, 4.8),
  (102, 'Data Modeling', 'Data', 'published', 120, 2550, 4.6),
  (103, 'CSS Layouts', 'Web', 'published', 75, 0, 4.2),
  (104, 'JavaScript DOM', 'Web', 'draft', 105, 2000, NULL),
  (105, 'Python APIs', 'Backend', 'published', 150, 3900, 4.9),
  (106, 'Query Tuning', 'Data', 'draft', 135, 4500, NULL),
  (107, 'Accessible UI', 'Web', 'published', 60, 1200, NULL),
  (108, 'Transactions', 'Backend', 'published', 110, 3000, 4.4),
  (109, 'HTML Semantics', 'Web', 'published', 75, 0, 4.2),
  (110, 'Intro to SQL', 'Data', 'published', 90, 0, 4.8);

SELECT DISTINCT category
FROM courses
ORDER BY category ASC;

QUERY OUTPUT

Edit the query, predict the rows it will return, then run it.

DISTINCT considers the whole selected row

SELECT DISTINCT category, status returns unique pairs, not one row per category. Data and Web each have both published and draft courses, while Backend has only published courses. The result therefore has five rows. When the query projects multiple columns, two result rows collapse only when every selected value matches. Adding course_id would make every row unique and defeat this use of DISTINCT.

SQL BROWSER RUNNER

Compare category-status pairs

See how one more projected column changes what counts as a duplicate.

CREATE TABLE courses (
  course_id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  category TEXT NOT NULL,
  status TEXT NOT NULL,
  duration_minutes INTEGER NOT NULL,
  price_cents INTEGER NOT NULL,
  rating REAL
);

INSERT INTO courses
  (course_id, title, category, status, duration_minutes, price_cents, rating)
VALUES
  (101, 'SQL Foundations', 'Data', 'published', 90, 0, 4.8),
  (102, 'Data Modeling', 'Data', 'published', 120, 2550, 4.6),
  (103, 'CSS Layouts', 'Web', 'published', 75, 0, 4.2),
  (104, 'JavaScript DOM', 'Web', 'draft', 105, 2000, NULL),
  (105, 'Python APIs', 'Backend', 'published', 150, 3900, 4.9),
  (106, 'Query Tuning', 'Data', 'draft', 135, 4500, NULL),
  (107, 'Accessible UI', 'Web', 'published', 60, 1200, NULL),
  (108, 'Transactions', 'Backend', 'published', 110, 3000, 4.4),
  (109, 'HTML Semantics', 'Web', 'published', 75, 0, 4.2),
  (110, 'Intro to SQL', 'Data', 'published', 90, 0, 4.8);

SELECT DISTINCT category, status
FROM courses
ORDER BY category ASC, status ASC;

CREATE TABLE courses (
  course_id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  category TEXT NOT NULL,
  status TEXT NOT NULL,
  duration_minutes INTEGER NOT NULL,
  price_cents INTEGER NOT NULL,
  rating REAL
);

INSERT INTO courses
  (course_id, title, category, status, duration_minutes, price_cents, rating)
VALUES
  (101, 'SQL Foundations', 'Data', 'published', 90, 0, 4.8),
  (102, 'Data Modeling', 'Data', 'published', 120, 2550, 4.6),
  (103, 'CSS Layouts', 'Web', 'published', 75, 0, 4.2),
  (104, 'JavaScript DOM', 'Web', 'draft', 105, 2000, NULL),
  (105, 'Python APIs', 'Backend', 'published', 150, 3900, 4.9),
  (106, 'Query Tuning', 'Data', 'draft', 135, 4500, NULL),
  (107, 'Accessible UI', 'Web', 'published', 60, 1200, NULL),
  (108, 'Transactions', 'Backend', 'published', 110, 3000, 4.4),
  (109, 'HTML Semantics', 'Web', 'published', 75, 0, 4.2),
  (110, 'Intro to SQL', 'Data', 'published', 90, 0, 4.8);

SELECT DISTINCT category, status
FROM courses
ORDER BY category ASC, status ASC;

QUERY OUTPUT

Edit the query, predict the rows it will return, then run it.

DISTINCT does not repair a wrong join

Use DISTINCT when the question truly asks for unique projected values: the set of categories, the set of published statuses, or another list whose duplicates are not useful. If an unexpected join duplicates whole entities, inspect the join and its relationship keys before hiding the extra rows with DISTINCT. Collapsing those rows can hide a grain mistake and make counts look smaller than the duplicated work actually was.

Common DISTINCT mistakes

SELECT DISTINCT course_id, category

Deduplication at the wrong grain

The unique ID keeps each row distinct. Project only the values whose duplicates should collapse.

DISTINCT as a join patch

Hidden duplication

If whole courses repeat, the join or grouping is wrong. Distinct values will not restore the intended grain.

No ORDER BY

An unordered unique list

Uniqueness and display order are separate contracts. Sort the short list when a reviewer needs a sequence.

Thinking the table changed

Result-only collapse

DISTINCT does not add a unique constraint and does not delete stored rows.

Independent lab: list published categories

List each published category once in alphabetical order. The starter query should return Backend, Data, and Web. Remove DISTINCT and count how many published rows share those three labels, then restore it. Adding status to the select list should not grow this published-only result, because every remaining row is published.

SQL BROWSER RUNNER

List distinct published categories

Collapse published category values and keep the short list alphabetical.

CREATE TABLE courses (
  course_id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  category TEXT NOT NULL,
  status TEXT NOT NULL,
  duration_minutes INTEGER NOT NULL,
  price_cents INTEGER NOT NULL,
  rating REAL
);

INSERT INTO courses
  (course_id, title, category, status, duration_minutes, price_cents, rating)
VALUES
  (101, 'SQL Foundations', 'Data', 'published', 90, 0, 4.8),
  (102, 'Data Modeling', 'Data', 'published', 120, 2550, 4.6),
  (103, 'CSS Layouts', 'Web', 'published', 75, 0, 4.2),
  (104, 'JavaScript DOM', 'Web', 'draft', 105, 2000, NULL),
  (105, 'Python APIs', 'Backend', 'published', 150, 3900, 4.9),
  (106, 'Query Tuning', 'Data', 'draft', 135, 4500, NULL),
  (107, 'Accessible UI', 'Web', 'published', 60, 1200, NULL),
  (108, 'Transactions', 'Backend', 'published', 110, 3000, 4.4),
  (109, 'HTML Semantics', 'Web', 'published', 75, 0, 4.2),
  (110, 'Intro to SQL', 'Data', 'published', 90, 0, 4.8);

-- List each published category once, A to Z.
SELECT DISTINCT category
FROM courses
WHERE status = 'published'
ORDER BY category ASC;

CREATE TABLE courses (
  course_id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  category TEXT NOT NULL,
  status TEXT NOT NULL,
  duration_minutes INTEGER NOT NULL,
  price_cents INTEGER NOT NULL,
  rating REAL
);

INSERT INTO courses
  (course_id, title, category, status, duration_minutes, price_cents, rating)
VALUES
  (101, 'SQL Foundations', 'Data', 'published', 90, 0, 4.8),
  (102, 'Data Modeling', 'Data', 'published', 120, 2550, 4.6),
  (103, 'CSS Layouts', 'Web', 'published', 75, 0, 4.2),
  (104, 'JavaScript DOM', 'Web', 'draft', 105, 2000, NULL),
  (105, 'Python APIs', 'Backend', 'published', 150, 3900, 4.9),
  (106, 'Query Tuning', 'Data', 'draft', 135, 4500, NULL),
  (107, 'Accessible UI', 'Web', 'published', 60, 1200, NULL),
  (108, 'Transactions', 'Backend', 'published', 110, 3000, 4.4),
  (109, 'HTML Semantics', 'Web', 'published', 75, 0, 4.2),
  (110, 'Intro to SQL', 'Data', 'published', 90, 0, 4.8);

-- List each published category once, A to Z.
SELECT DISTINCT category
FROM courses
WHERE status = 'published'
ORDER BY category ASC;

QUERY OUTPUT

Edit the query, predict the rows it will return, then run it.

Lesson review

DISTINCT collapses equal projected rows, so its meaning depends on the selected columns. Unique category values are not the same as unique category-status pairs. The clause does not change stored data and does not repair a query that duplicated entities for the wrong reason. Order the unique list when the display sequence should be a contract.

I can distinguish unique category values from unique category-status pairs.
I know DISTINCT applies to the whole projected row.
I will not use DISTINCT to hide a duplicated join.
I can order a unique list so the display is reviewable.

Reading and filtering data 40 min

SQL DISTINCT

Remove repeated projected rows without changing stored data, and project only the columns whose duplicates should collapse.

DISTINCT collapses duplicate result rows

Unique valuesSELECT DISTINCT category returns each category once.

Whole rowTwo result rows collapse only when every selected value matches.

Not a join fixUnexpected duplicates usually mean the query asked the wrong question.

Display orderORDER BY still belongs on the unique list when the sequence should be a contract.

SOURCEcourses10 stored rows

Categories repeat because many courses share Data, Web, or Backend.

PROJECTSELECT DISTINCT categoryunique values

Repeated category values collapse in the result, not in the table.

ORDERORDER BY category ASCBackend, Data, Web

The short list still needs an explicit display order.

SELECT DISTINCT returns unique projected values

SQL BROWSER RUNNER

List unique category values

Turn repeated course categories into a three-row result.

CREATE TABLE courses (
  course_id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  category TEXT NOT NULL,
  status TEXT NOT NULL,
  duration_minutes INTEGER NOT NULL,
  price_cents INTEGER NOT NULL,
  rating REAL
);

INSERT INTO courses
  (course_id, title, category, status, duration_minutes, price_cents, rating)
VALUES
  (101, 'SQL Foundations', 'Data', 'published', 90, 0, 4.8),
  (102, 'Data Modeling', 'Data', 'published', 120, 2550, 4.6),
  (103, 'CSS Layouts', 'Web', 'published', 75, 0, 4.2),
  (104, 'JavaScript DOM', 'Web', 'draft', 105, 2000, NULL),
  (105, 'Python APIs', 'Backend', 'published', 150, 3900, 4.9),
  (106, 'Query Tuning', 'Data', 'draft', 135, 4500, NULL),
  (107, 'Accessible UI', 'Web', 'published', 60, 1200, NULL),
  (108, 'Transactions', 'Backend', 'published', 110, 3000, 4.4),
  (109, 'HTML Semantics', 'Web', 'published', 75, 0, 4.2),
  (110, 'Intro to SQL', 'Data', 'published', 90, 0, 4.8);

SELECT DISTINCT category
FROM courses
ORDER BY category ASC;

CREATE TABLE courses (
  course_id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  category TEXT NOT NULL,
  status TEXT NOT NULL,
  duration_minutes INTEGER NOT NULL,
  price_cents INTEGER NOT NULL,
  rating REAL
);

INSERT INTO courses
  (course_id, title, category, status, duration_minutes, price_cents, rating)
VALUES
  (101, 'SQL Foundations', 'Data', 'published', 90, 0, 4.8),
  (102, 'Data Modeling', 'Data', 'published', 120, 2550, 4.6),
  (103, 'CSS Layouts', 'Web', 'published', 75, 0, 4.2),
  (104, 'JavaScript DOM', 'Web', 'draft', 105, 2000, NULL),
  (105, 'Python APIs', 'Backend', 'published', 150, 3900, 4.9),
  (106, 'Query Tuning', 'Data', 'draft', 135, 4500, NULL),
  (107, 'Accessible UI', 'Web', 'published', 60, 1200, NULL),
  (108, 'Transactions', 'Backend', 'published', 110, 3000, 4.4),
  (109, 'HTML Semantics', 'Web', 'published', 75, 0, 4.2),
  (110, 'Intro to SQL', 'Data', 'published', 90, 0, 4.8);

SELECT DISTINCT category
FROM courses
ORDER BY category ASC;

QUERY OUTPUT

Edit the query, predict the rows it will return, then run it.

DISTINCT considers the whole selected row

SQL BROWSER RUNNER

Compare category-status pairs

See how one more projected column changes what counts as a duplicate.

CREATE TABLE courses (
  course_id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  category TEXT NOT NULL,
  status TEXT NOT NULL,
  duration_minutes INTEGER NOT NULL,
  price_cents INTEGER NOT NULL,
  rating REAL
);

INSERT INTO courses
  (course_id, title, category, status, duration_minutes, price_cents, rating)
VALUES
  (101, 'SQL Foundations', 'Data', 'published', 90, 0, 4.8),
  (102, 'Data Modeling', 'Data', 'published', 120, 2550, 4.6),
  (103, 'CSS Layouts', 'Web', 'published', 75, 0, 4.2),
  (104, 'JavaScript DOM', 'Web', 'draft', 105, 2000, NULL),
  (105, 'Python APIs', 'Backend', 'published', 150, 3900, 4.9),
  (106, 'Query Tuning', 'Data', 'draft', 135, 4500, NULL),
  (107, 'Accessible UI', 'Web', 'published', 60, 1200, NULL),
  (108, 'Transactions', 'Backend', 'published', 110, 3000, 4.4),
  (109, 'HTML Semantics', 'Web', 'published', 75, 0, 4.2),
  (110, 'Intro to SQL', 'Data', 'published', 90, 0, 4.8);

SELECT DISTINCT category, status
FROM courses
ORDER BY category ASC, status ASC;

CREATE TABLE courses (
  course_id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  category TEXT NOT NULL,
  status TEXT NOT NULL,
  duration_minutes INTEGER NOT NULL,
  price_cents INTEGER NOT NULL,
  rating REAL
);

INSERT INTO courses
  (course_id, title, category, status, duration_minutes, price_cents, rating)
VALUES
  (101, 'SQL Foundations', 'Data', 'published', 90, 0, 4.8),
  (102, 'Data Modeling', 'Data', 'published', 120, 2550, 4.6),
  (103, 'CSS Layouts', 'Web', 'published', 75, 0, 4.2),
  (104, 'JavaScript DOM', 'Web', 'draft', 105, 2000, NULL),
  (105, 'Python APIs', 'Backend', 'published', 150, 3900, 4.9),
  (106, 'Query Tuning', 'Data', 'draft', 135, 4500, NULL),
  (107, 'Accessible UI', 'Web', 'published', 60, 1200, NULL),
  (108, 'Transactions', 'Backend', 'published', 110, 3000, 4.4),
  (109, 'HTML Semantics', 'Web', 'published', 75, 0, 4.2),
  (110, 'Intro to SQL', 'Data', 'published', 90, 0, 4.8);

SELECT DISTINCT category, status
FROM courses
ORDER BY category ASC, status ASC;

QUERY OUTPUT

Edit the query, predict the rows it will return, then run it.

DISTINCT does not repair a wrong join

Common DISTINCT mistakes

SELECT DISTINCT course_id, category

Deduplication at the wrong grain

The unique ID keeps each row distinct. Project only the values whose duplicates should collapse.

DISTINCT as a join patch

Hidden duplication

If whole courses repeat, the join or grouping is wrong. Distinct values will not restore the intended grain.

No ORDER BY

An unordered unique list

Uniqueness and display order are separate contracts. Sort the short list when a reviewer needs a sequence.

Thinking the table changed

Result-only collapse

DISTINCT does not add a unique constraint and does not delete stored rows.

Independent lab: list published categories

SQL BROWSER RUNNER

List distinct published categories

Collapse published category values and keep the short list alphabetical.

CREATE TABLE courses (
  course_id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  category TEXT NOT NULL,
  status TEXT NOT NULL,
  duration_minutes INTEGER NOT NULL,
  price_cents INTEGER NOT NULL,
  rating REAL
);

INSERT INTO courses
  (course_id, title, category, status, duration_minutes, price_cents, rating)
VALUES
  (101, 'SQL Foundations', 'Data', 'published', 90, 0, 4.8),
  (102, 'Data Modeling', 'Data', 'published', 120, 2550, 4.6),
  (103, 'CSS Layouts', 'Web', 'published', 75, 0, 4.2),
  (104, 'JavaScript DOM', 'Web', 'draft', 105, 2000, NULL),
  (105, 'Python APIs', 'Backend', 'published', 150, 3900, 4.9),
  (106, 'Query Tuning', 'Data', 'draft', 135, 4500, NULL),
  (107, 'Accessible UI', 'Web', 'published', 60, 1200, NULL),
  (108, 'Transactions', 'Backend', 'published', 110, 3000, 4.4),
  (109, 'HTML Semantics', 'Web', 'published', 75, 0, 4.2),
  (110, 'Intro to SQL', 'Data', 'published', 90, 0, 4.8);

-- List each published category once, A to Z.
SELECT DISTINCT category
FROM courses
WHERE status = 'published'
ORDER BY category ASC;

CREATE TABLE courses (
  course_id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  category TEXT NOT NULL,
  status TEXT NOT NULL,
  duration_minutes INTEGER NOT NULL,
  price_cents INTEGER NOT NULL,
  rating REAL
);

INSERT INTO courses
  (course_id, title, category, status, duration_minutes, price_cents, rating)
VALUES
  (101, 'SQL Foundations', 'Data', 'published', 90, 0, 4.8),
  (102, 'Data Modeling', 'Data', 'published', 120, 2550, 4.6),
  (103, 'CSS Layouts', 'Web', 'published', 75, 0, 4.2),
  (104, 'JavaScript DOM', 'Web', 'draft', 105, 2000, NULL),
  (105, 'Python APIs', 'Backend', 'published', 150, 3900, 4.9),
  (106, 'Query Tuning', 'Data', 'draft', 135, 4500, NULL),
  (107, 'Accessible UI', 'Web', 'published', 60, 1200, NULL),
  (108, 'Transactions', 'Backend', 'published', 110, 3000, 4.4),
  (109, 'HTML Semantics', 'Web', 'published', 75, 0, 4.2),
  (110, 'Intro to SQL', 'Data', 'published', 90, 0, 4.8);

-- List each published category once, A to Z.
SELECT DISTINCT category
FROM courses
WHERE status = 'published'
ORDER BY category ASC;

QUERY OUTPUT

Edit the query, predict the rows it will return, then run it.

Lesson review

I can distinguish unique category values from unique category-status pairs.
I know DISTINCT applies to the whole projected row.
I will not use DISTINCT to hide a duplicated join.
I can order a unique list so the display is reviewable.

Relational foundations

Reading and filtering data

Reports, aggregation, and analytics

Combining related data

Designing reliable schemas

Writing and protecting data

Performance and administration

Security and production workflow

SQL DISTINCT

DISTINCT collapses duplicate result rows

SELECT DISTINCT returns unique projected values

DISTINCT considers the whole selected row

DISTINCT does not repair a wrong join

Common DISTINCT mistakes

Deduplication at the wrong grain

Hidden duplication

An unordered unique list

Result-only collapse

Independent lab: list published categories

Lesson review

Your privacy choices

Relational foundations

Reading and filtering data

Reports, aggregation, and analytics

Combining related data

Designing reliable schemas

Writing and protecting data

Performance and administration

Security and production workflow

SQL DISTINCT

DISTINCT collapses duplicate result rows

SELECT DISTINCT returns unique projected values

DISTINCT considers the whole selected row

DISTINCT does not repair a wrong join

Common DISTINCT mistakes

Deduplication at the wrong grain

Hidden duplication

An unordered unique list

Result-only collapse

Independent lab: list published categories

Lesson review