-- Basic DISTINCT

SELECT DISTINCT department FROM employees;

-- COUNT DISTINCT

SELECT COUNT(DISTINCT customer_id) AS unique_customers FROM orders;

-- Multiple columns

SELECT DISTINCT department, job_title FROM employees;

-- DISTINCT with aggregates

SELECT

department,

COUNT(DISTINCT job_title) AS unique_roles

FROM employees

GROUP BY department;

-- ALL (explicit, rarely used)

SELECT ALL department FROM employees;

DISTINCT removes duplicate rows from result

ALL includes all rows (default behavior)

COUNT(DISTINCT column) counts unique values

DISTINCT applies to entire row when multiple columns

DISTINCT happens after WHERE, before ORDER BY

NULL values treated as one distinct value

Core references in this topic include DISTINCT, ANY / ALL, WHERE. Learn what each one does, when to use it, and the execution or engine rules that matter.

DISTINCT

Removes duplicate values from a projection or aggregate input set.

COUNT(DISTINCT customer_id)

ANY / ALL

Compares one value against every or at least one value from a subquery result.

salary > ALL (SELECT salary FROM interns)

WHERE

Filters rows before projection and sorting. It decides which rows continue through the query pipeline.

SELECT ... FROM table WHERE condition;

Most performance issues start with a weak WHERE clause or a missing supporting index.

EXISTS

Tests whether a correlated or non-correlated subquery returns at least one row.

WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id)

UNIQUE

Prevents duplicate values in a column or column combination.

email VARCHAR(255) UNIQUE

COUNT

Counts rows or non-NULL values depending on the argument.

COUNT(*)