DevLearningTools

LEARN · SQL

SQL SELECT DISTINCT

SELECT DISTINCT removes duplicate rows from a query's result. Learn how it works on one column, on multiple columns, and how it differs from COUNT(DISTINCT ...).

← SQL SELECTSQL WHERE →

A plain SELECT can return the same value more than once, every employee's department, for example, repeats once per employee in that department. DISTINCT removes those duplicates from the result.

Learning Objectives

  • Use SELECT DISTINCT to remove duplicate rows from a result.
  • Apply DISTINCT across more than one column at once.
  • Explain how DISTINCT differs from COUNT(DISTINCT ...).

All Rows

Including repeats

DISTINCT

Compares full rows

Unique Rows Only

Duplicates collapsed

Removing Duplicates

DISTINCT goes right after SELECT, before the column list. It applies to the entire row being selected, not to one column in isolation, which matters as soon as more than one column is involved.

MySQL / PostgreSQL / SQLite / SQL Server
SELECT DISTINCT department
FROM employees;

-- Without DISTINCT: Engineering, Engineering, Research
-- With DISTINCT:    Engineering, Research

DISTINCT on Multiple Columns

With two columns, DISTINCT removes only rows where both values together are an exact repeat. Two employees both in Engineering with status 'active' collapse to one row, but Engineering/active and Engineering/on_leave remain separate, since the combination differs.

ADVANTAGES
  • + Simple, one keyword to deduplicate an entire result
  • + Works across any number of selected columns at once
DISADVANTAGES
  • − Costs extra work on every query, the database must compare rows to find duplicates
  • − Can mask a deeper issue, like a join multiplying rows, instead of fixing the actual cause
MySQL / PostgreSQL / SQLite / SQL Server
SELECT DISTINCT department, status
FROM employees;

Try It Yourself

Using the employees table, write a query that lists every distinct department, in alphabetical order.

Answer
SELECT DISTINCT department
FROM employees
ORDER BY department ASC;

Frequently Asked Questions

Does DISTINCT ignore NULL values?

No, NULL is treated as a value for deduplication purposes, every database in this course collapses multiple NULLs in the same column down to a single NULL row, the same as it would for any repeated value.

Is DISTINCT slower than a plain SELECT?

Generally yes, the database has to compare rows to find and remove duplicates, which is extra work a plain SELECT doesn't do. On a large result set this cost is worth being aware of.

Common Beginner Mistakes

Assuming SELECT DISTINCT col1, col2 removes duplicates of col1 alone

It doesn't, DISTINCT applies to the whole selected row. Two rows only collapse into one if every selected column matches exactly between them.

Using DISTINCT as a substitute for understanding why duplicates exist in the first place

DISTINCT treats the symptom, not the cause. If a query is producing unexpected duplicates, it's usually worth understanding why (often a join multiplying rows, covered later in this course) rather than just hiding it with DISTINCT.

Best Practices

  • Reach for DISTINCT when duplicates are an expected, normal part of the data, not as a blanket fix for an unexpected duplicate.
  • Remember DISTINCT considers the full selected row, be deliberate about exactly which columns you include when using it.
  • Be aware of its performance cost on large tables before using it casually.

Interview Questions

Write a query returning every distinct department and status combination in the employees table.

SELECT DISTINCT department, status FROM employees; — DISTINCT here considers both columns together, so unique combinations are kept, not just unique values of one column.

How does COUNT(DISTINCT department) differ from SELECT DISTINCT department?

SELECT DISTINCT department returns each unique department as a row. COUNT(DISTINCT department) returns a single number, how many unique departments there are, aggregate functions and COUNT specifically are covered in depth later in this course.

Summary

This lesson covered removing duplicate rows with SELECT DISTINCT, how it considers the full selected row rather than one column alone, and its performance tradeoff on large result sets.

What's Next?

The next lesson covers WHERE, filtering a query down to only the rows you care about.

</>

Ready to Practice SQL?

Write and run real SQL queries online, right in your browser, no install or signup required. Pick a playground based on which database you want to practice on.

SQLite / PostgreSQL

Fast, no server, no ads.

Open DB Fiddle ↗

MySQL / SQL Server

Switch databases with one dropdown.

Open SQL Fiddle ↗

Both open a third-party SQL playground in a new tab. External site terms and privacy policy apply.

← SQL SELECTSQL WHERE →