SELECT DISTINCT

Key moments

Introduction and Analogy — The SELECT DISTINCT statement is introduced using the analogy of sorting unique crayon colors.

Use Case Example — The need for DISTINCT is demonstrated using the customers table and the country column to list unique origins.

Basic Syntax and Query — The standard syntax SELECT DISTINCT column FROM table is shown using the country column example.

Result Comparison — The difference between a standard SELECT and one using DISTINCT is explained, highlighting the filtering of duplicates.

Counting Unique Values — The COUNT(DISTINCT column) function is introduced as the method for calculating the number of unique entries.

MS Access Workaround — A subquery technique is provided as a trick to count distinct values in SQL environments that do not support COUNT(DISTINCT).

Conclusion — The lesson concludes by summarizing that SELECT DISTINCT is essential for viewing only unique data.

Bottom line: You will be able to efficiently retrieve only the unique values from specified columns in your database tables. You can write queries that eliminate duplicate entries, ensuring your reports and analyses reflect distinct data points, such as unique countries or product types.

This lesson on SELECT DISTINCT is hands-on and example-driven. You will be able to efficiently retrieve only the unique values from specified columns in your database tables. You can write queries that eliminate duplicate entries, ensuring your reports and analyses reflect distinct data points, such as unique countries or product types.

What You'll Be Able To Do

Identify scenarios where duplicate data needs to be suppressed.
Write a SELECT statement using DISTINCT on a single column.
Differentiate the results of a standard SELECT versus SELECT DISTINCT.
Calculate the total number of unique values in a column using COUNT.
Apply subquery techniques to count distinct values in non-standard SQL environments.

Topics Covered in SELECT DISTINCT

Introduction and Analogy (0:00 - 0:10) — The SELECT DISTINCT statement is introduced using the analogy of sorting unique crayon colors.
Use Case Example (0:10 - 0:20) — The need for DISTINCT is demonstrated using the customers table and the country column to list unique origins.
Basic Syntax and Query (0:20 - 0:35) — The standard syntax SELECT DISTINCT column FROM table is shown using the country column example.
Result Comparison (0:35 - 0:45) — The difference between a standard SELECT and one using DISTINCT is explained, highlighting the filtering of duplicates.
Counting Unique Values (0:45 - 0:55) — The COUNT(DISTINCT column) function is introduced as the method for calculating the number of unique entries.
MS Access Workaround (0:55 - 1:10) — A subquery technique is provided as a trick to count distinct values in SQL environments that do not support COUNT(DISTINCT).
Conclusion (1:10 - 1:20) — The lesson concludes by summarizing that SELECT DISTINCT is essential for viewing only unique data.

SQL Cheat Sheet

SELECT DISTINCT column FROM table; — Returns only the unique, non-duplicated values in the column
```
SELECT DISTINCT country FROM customers;
```
COUNT(DISTINCT column) — Counts how many unique values exist in the specified column
```
SELECT COUNT(DISTINCT country) FROM customers;
```
SELECT column FROM table; — Retrieves all rows and columns, including any duplicates
```
SELECT country FROM customers;
```
SELECT COUNT(*) FROM (SELECT DISTINCT column FROM table); — Subquery method to count unique values (useful in MS Access)
```
SELECT COUNT(*) FROM (SELECT DISTINCT country FROM customers);
```

Comparison Table

Query Type	Result Set	Duplicates
SELECT country	All rows returned	Included
SELECT DISTINCT country	Only unique values	Filtered out
COUNT(*)	Total number of rows	Included in count

Common Pitfalls

Mistake: Using DISTINCT on multiple columns expecting unique values per column. Avoid: DISTINCT applies to the combination of all selected columns.
Mistake: Trying to use COUNT(DISTINCT) in older SQL versions like MS Access. Avoid: Use a subquery to select distinct values, then count the resulting rows.
Mistake: Assuming DISTINCT permanently changes the underlying data. Avoid: DISTINCT only filters the output of the query, not the stored table data.

FAQs

Does DISTINCT work if I select more than one column? Yes, but it returns unique combinations of all selected columns. A row is only considered a duplicate if every column matches another row exactly.
Why would I use DISTINCT instead of just grouping the results? DISTINCT is simpler and faster when you only need the unique list itself, not aggregate calculations associated with those groups.
Is COUNT(DISTINCT) standard across all SQL databases? Most modern databases support it, but older systems like MS Access require using a subquery to achieve the same result.

Hey there, data Wranglers. Today we're diving into the SQL select distinct statement. Imagine you're sorting through a big box of crayons and you only want to see the unique colors. That's what select distinct does for your data. Let's look at an example. We have a customers table with a country column. There might be multiple entries from the same country, but what if we only want to list each country once? That's where we can use select distinct. Here's the syntax. Select distinct, followed by the column name. In this case, country. This query will give us all the unique country names from our customers table. See? We went from a list with duplicates to just the unique entries. It's like magic, but with data. Without distinct, you'd get every country listed for every customer. But with distinct, it filters out the duplicates. Easy, right? Now let's count those distinct values using count distinct column name. This tells us how many unique countries there are. MS Access doesn't support count, but you can use a subquery to get the same result. Like this. Just a little trick to keep in your SQL toolbox. And that's your quick dive into the select distinct statement. Remember, it's perfect for when you want to see just the unique data. Thanks for watching. Like and subscribe to get notified about our latest videos. For more coding tips and tricks, keep it locked right here on W3schools.com. Don't forget to try out what you've learned in our interactive editor. Happy coding.

Frequently asked questions

Does DISTINCT work if I select more than one column?

Yes, but it returns unique combinations of all selected columns. A row is only considered a duplicate if every column matches another row exactly.

Why would I use DISTINCT instead of just grouping the results?

DISTINCT is simpler and faster when you only need the unique list itself, not aggregate calculations associated with those groups.

Is COUNT(DISTINCT) standard across all SQL databases?

Most modern databases support it, but older systems like MS Access require using a subquery to achieve the same result.

Ready to continue?