DISTINCT

---

title: “DISTINCT” part: 1 topic_number: 18 slug: “distinct” difficulty: “Beginner” prerequisites: “select-from-where” —

DISTINCT

What Is It?

DISTINCT eliminates duplicate rows from your query results. If two rows have identical values in all selected columns, only one is kept. It’s like saying “show me unique values only.”

Real-world analogy: A list of unique cities where your customers live, not every customer address (which would have duplicates).

---

Syntax Breakdown

SELECT DISTINCT column1, column2, ...
FROM table
WHERE conditions;

Key points:

• DISTINCT goes right after SELECT
• Applies to the entire row of selected columns
• Returns each unique combination only once

---

Basic Examples

Unique Department IDs

Find which departments have employees (no duplicates):

SELECT DISTINCT department_id
FROM employees;

Without DISTINCT:

department_id
1
1
2
2
2
3
3
… (20 rows)

With DISTINCT:

department_id
1
2
3
4
5
6
7
8
9
10
NULL

Much cleaner! Only 11 unique values instead of 20 rows.

Unique Locations

Find all unique office locations:

SELECT DISTINCT location
FROM departments;

Expected output:

location
San Francisco
New York
Austin
Chicago
Boston
Remote
Seattle
NULL

8 unique locations (including NULL).

---

Going Deeper

DISTINCT on Multiple Columns

DISTINCT applies to the entire row, not individual columns:

SELECT DISTINCT department_id, manager_id
FROM employees;

What this returns: Unique COMBINATIONS of (department_id, manager_id).

Expected output (partial):

department_id	manager_id
1	1
1	10
2	1
2	3
3	1
3	5

Each row is unique — maybe multiple employees have dept=1 and manager=1, but that combination appears only once.

Pause and Predict: What does SELECT DISTINCT first_name, last_name return if two employees have the same full name?

Answer

Only one row with that name. If you have two "John Smith" employees (different employee_ids), DISTINCT will show only one "John Smith" row. **This is dangerous!** You'd lose information. In our data, all names are unique, so this isn't a problem. But in real databases with common names, DISTINCT on name can hide multiple people.

DISTINCT vs GROUP BY

These are similar but not identical:

Using DISTINCT:

SELECT DISTINCT department_id
FROM employees;

Using GROUP BY:

SELECT department_id
FROM employees
GROUP BY department_id;

Both return the same unique department_ids!

When to use each:

• DISTINCT: Simple deduplication, no aggregation
• GROUP BY: When you also need aggregates (COUNT, SUM, etc.)

Example needing GROUP BY:

SELECT department_id, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id;

You can’t do this with DISTINCT — you need GROUP BY for aggregates.

DISTINCT with ORDER BY

SELECT DISTINCT location
FROM departments
ORDER BY location;

Expected output:

location
Austin
Boston
Chicago
New York
Remote
San Francisco
Seattle
NULL

NULLs sort first in ASC order (MySQL default).

---

Watch Out — Common Mistakes

Mistake #1: DISTINCT on One Column When Selecting Multiple

--  WRONG — Can't use DISTINCT on just one column
SELECT first_name, DISTINCT last_name
FROM employees;

Error: You have an error in your SQL syntax

Why it fails: DISTINCT applies to the entire SELECT list, not individual columns.

-- • CORRECT — DISTINCT applies to both columns
SELECT DISTINCT first_name, last_name
FROM employees;

Or use GROUP BY if you want distinct last names with some first name:

SELECT last_name, MIN(first_name) AS first_name
FROM employees
GROUP BY last_name;

Mistake #2: DISTINCT Doesn’t Aggregate

--  WRONG — This doesn't give you a count of unique departments
SELECT DISTINCT COUNT(department_id)
FROM employees;

What this does: Counts all department_ids, then applies DISTINCT to that single count value (which does nothing useful).

Expected: 20 (or 18 if you use COUNT(department_id) which excludes NULLs)

What you probably wanted: Count of unique departments:

-- • CORRECT — Count distinct departments
SELECT COUNT(DISTINCT department_id) AS unique_departments
FROM employees;

Expected output:

unique_departments
10

(10 unique non-NULL department_ids)

Mistake #3: Performance with DISTINCT on Large Tables

-- CAN BE SLOW on large tables
SELECT DISTINCT *
FROM huge_table;

Why it’s slow: MySQL must compare every row to every other row to eliminate duplicates. On a table with millions of rows, this can take time.

Better alternatives:

• If possible, ensure uniqueness at INSERT time (don’t insert duplicates)
• Use GROUP BY with specific columns
• Add indexes on columns you’re checking for uniqueness

In our small company_db, this isn’t an issue. But on production databases with millions of rows, use DISTINCT carefully.

---

Edge Case Spotlight

COUNT(DISTINCT column) — Powerful Combination

Count unique values in one query:

SELECT 
    COUNT(*) AS total_employees,
    COUNT(DISTINCT department_id) AS unique_departments,
    COUNT(DISTINCT location) AS unique_locations
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;

Expected output:

total_employees	unique_departments	unique_locations
20	10	7

Powerful! Multiple distinct counts in one query.

Note: COUNT(DISTINCT ...) ignores NULL. If you want to count NULL as a distinct value, you need a more complex query.

---

Try This

Exercise 1 (Guided)

Find all unique manager IDs (people who manage others). Show only the manager_id column.

Hint

SELECT DISTINCT manager_id FROM employees. Remember that NULL will appear (employees with no manager).

Exercise 2 (Independent)

Find all unique combinations of location and department_name from the departments table. Sort by location.

Exercise 3 (Challenge)

Count how many unique roles exist in the employee_projects table. (Hint: Use COUNT(DISTINCT …))

---

Answer Key

Exercise 1 Answer

```sql SELECT DISTINCT manager_id FROM employees ORDER BY manager_id; ``` **Expected output:** | manager_id | |------------| | NULL | | 1 | | 3 | | 5 | | 7 | | 10 | 6 unique values (including NULL for employees with no manager, like Alice). **To exclude NULL:** ```sql SELECT DISTINCT manager_id FROM employees WHERE manager_id IS NOT NULL ORDER BY manager_id; ```

Exercise 2 Answer

```sql SELECT DISTINCT department_name, location FROM departments ORDER BY location, department_name; ``` **Expected output:** | department_name | location | |-----------------|----------| | Sales | Austin | | Finance | Boston | | Human Resources | Chicago | | Legal | New York | | Marketing | New York | | Customer Support | Remote | | Engineering | San Francisco | | Product | San Francisco | | Data Science | Seattle | | Operations | NULL | All 10 departments with their unique location combinations (each department appears once).

Exercise 3 Answer

```sql SELECT COUNT(DISTINCT role) AS unique_roles FROM employee_projects; ``` **Expected output:** | unique_roles | |--------------| | 30 | (Or however many unique roles exist in the data — could be 20-35 based on our assignments) **To see the actual roles:** ```sql SELECT DISTINCT role FROM employee_projects ORDER BY role; ``` This shows roles like "Lead Developer", "Frontend Developer", "Campaign Manager", etc.

---

Quick Recap

• DISTINCT eliminates duplicate rows from results
• Applies to the entire SELECT list (all columns together)
• Cannot apply DISTINCT to just one column when selecting multiple
• DISTINCT vs GROUP BY: use DISTINCT for simple deduplication, GROUP BY when you need aggregates
• COUNT(DISTINCT column) counts unique values
• Can impact performance on large tables

---

Up Next

Time for a Challenge! → Mini Challenge 6

You’ve mastered LEFT JOIN, RIGHT JOIN, and DISTINCT! Practice these join techniques with a challenge.

You can now eliminate duplicates. Next, you’ll master the tricky world of NULL values — what they mean and how to handle them properly!