SQL Academy · Lesson

SELECT DISTINCT Basics

Return only unique rows.

SELECT DISTINCT Basics is a free SQL Academy lesson on CoddyKit — lesson 1 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Why Duplicates Happen

Tables often contain repeated values. A customers table might list the same city across thousands of rows. When you only care about the set of distinct values, plain SELECT returns every row including repeats.

SELECT DISTINCT collapses identical rows into one. In this lesson you will learn the basics of returning only unique results.

SELECT city FROM customers;
-- Returns one row per customer, with many repeated cities

The DISTINCT Keyword

DISTINCT goes right after SELECT. It removes duplicate rows from the result set, keeping just one copy of each unique combination of the selected columns.

SELECT DISTINCT city
FROM customers;
-- One row per unique city

DISTINCT Applies to the Whole Row

A common misconception is that DISTINCT applies to a single column. In fact it applies to the entire selected row. Every column you list participates in deciding whether two rows are duplicates.

Below, rows are deduplicated by the value of city only, because that is the only column selected.

SELECT DISTINCT country
FROM customers;
-- Unique countries across all customers

Sample Data

Imagine a small orders table. Notice the repeated status and country values. We will use this shape throughout the lesson.

id=1, status='paid', country='US'
id=2, status='paid', country='US'
id=3, status='shipped', country='DE'
id=4, status='paid', country='DE'

CREATE TABLE orders (
  id         int PRIMARY KEY,
  status     text,
  country    text,
  amount     numeric(10,2)
);

Distinct Single Column

Selecting DISTINCT status from the sample data returns the unique status values: paid and shipped. The four rows collapse to two.

SELECT DISTINCT status
FROM orders;
-- paid
-- shipped

NULL Is Treated as One Value

Even though NULL is normally not equal to anything (not even itself), DISTINCT treats all NULLs as a single group. If a column has many NULL rows, you get exactly one NULL in the distinct result.

SELECT DISTINCT shipped_at
FROM orders;
-- All NULL shipped_at rows collapse into a single NULL row

DISTINCT with ORDER BY

You usually want the unique values sorted. DISTINCT runs first to build the unique set, then ORDER BY sorts it. Any column in ORDER BY must also appear in the SELECT list when using DISTINCT.

SELECT DISTINCT country
FROM orders
ORDER BY country;
-- DE
-- US

DISTINCT vs GROUP BY

SELECT DISTINCT col and SELECT col GROUP BY col produce the same unique rows. Use DISTINCT when you just want unique values; reach for GROUP BY when you also need aggregates like COUNT per group.

-- These two return the same set of countries:
SELECT DISTINCT country FROM orders;

SELECT country FROM orders
GROUP BY country;

DISTINCT Has a Cost

Removing duplicates is not free. The database must sort or hash all the candidate rows to find unique ones. On large tables this adds CPU and memory work, so only ask for DISTINCT when you actually need uniqueness.

-- Avoid blind DISTINCT on every query.
-- If the column is already unique (e.g. a PRIMARY KEY),
-- DISTINCT does nothing useful but still costs work:
SELECT DISTINCT id FROM orders;  -- id is already unique

DISTINCT in Expressions

You can apply DISTINCT to a computed expression, not just a raw column. The database evaluates the expression for every row, then deduplicates the results.

SELECT DISTINCT upper(country) AS country_code
FROM orders
ORDER BY country_code;
-- DE
-- US

Building a Lookup List

A practical use of DISTINCT is populating a dropdown or filter list in an application: fetch the unique statuses to show the user every possible option that currently exists in the data.

SELECT DISTINCT status
FROM orders
ORDER BY status;
-- paid
-- shipped

Quick Check

Where does the DISTINCT keyword belong, and what does it deduplicate?

Recap

You learned the basics of SELECT DISTINCT:

It removes duplicate rows from the result set.
It applies to the whole selected row, not one column.
All NULLs collapse into a single value.
ORDER BY runs after deduplication; its columns must be selected.
It costs extra work, so use it only when needed.

Next: extending DISTINCT across multiple columns.

SELECT DISTINCT status
FROM orders
ORDER BY status;

Frequently asked questions

Is the “SELECT DISTINCT Basics” lesson free?

Yes — the full text of “SELECT DISTINCT Basics” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “SELECT DISTINCT Basics”?

Return only unique rows. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 1 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “SELECT DISTINCT Basics” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to SQL Academy