SQL · Theory

DISTINCT

Theory

41/439

DISTINCT

SELECT DISTINCT city FROM students; each city once. DISTINCT applies to the whole selected row, not one column in a mix unless that is the only column.

SELECT DISTINCT name, city can still repeat a name if cities differ. If you meant unique names, SELECT DISTINCT name.

Trap: DISTINCT to hide a bad JOIN that multiplied rows. Fix the JOIN. DISTINCT is slower on big tables than a correct query.

On the example next to this theory — DISTINCT — gROUP BY aggregates; HAVING filters groups; ORDER BY sorts the result.

Exam tip

SELECT DISTINCT city FROM students;

DISTINCT — sample query

CREATE TABLE sales (id INTEGER PRIMARY KEY, region TEXT, amount INTEGER);
INSERT INTO sales VALUES (1, 'N', 100), (2, 'N', 80), (3, 'S', 120);
SELECT region, SUM(amount) AS total
FROM sales
GROUP BY region
HAVING total >= 150
ORDER BY total DESC;

DISTINCT — gROUP BY aggregates; HAVING filters groups; ORDER BY sorts the result.

Short notes

DefDISTINCT removes duplicate result rows.
RuleIt is the whole SELECT list, not a magic single column.
TrapUsing DISTINCT to cover a cartesian JOIN.

CREATE TABLE sales (id INTEGER PRIMARY KEY, region TEXT, amount INTEGER); INSERT INTO sales VALUES (1, 'N', 100), (2, 'N', 80), (3, 'S', 120); SELECT region, SUM(amount) AS total FROM sales GROUP BY region HAVING total >= 150 ORDER BY total DESC;

DISTINCT

DISTINCT — sample query

Short notes

Questions

DISTINCT

DISTINCT — sample query

Short notes

Questions