SQL DISTINCT Keyword

Understanding the SQL DISTINCT Keyword

The DISTINCT keyword in SQL eliminates duplicate values from your query results, returning only unique values. It's like asking for a list of different categories without any repetition.

Basic Syntax

Common SQL DISTINCT Interview Questions

How does DISTINCT work with NULL values?
Can DISTINCT be used with multiple columns?
What's the difference between DISTINCT and GROUP BY?
How does DISTINCT affect performance?

Using DISTINCT in SQL Queries

Single Column DISTINCT

The simplest form of DISTINCT returns unique values from a single column, removing all duplicates from the result set. In SQL, DISTINCT treats NULL values as equal, so if you have multiple NULL values, only one will appear in the results.

Multi-Column DISTINCT

DISTINCT can be applied to multiple columns, returning unique combinations of values across those columns. This means rows are considered duplicates only when they have the same values in all specified columns.

DISTINCT with Other SQL Features

DISTINCT with COUNT

Combining DISTINCT with COUNT allows you to count the number of unique values in a column, which is useful for data analysis and insights.

Note on NULL values: COUNT(DISTINCT) follows standard SQL NULL handling rules - NULL values are excluded from the count. If you have multiple NULL values in a column, none of them will be counted in COUNT(DISTINCT), unlike DISTINCT alone which returns a single NULL if NULL values exist.

DISTINCT vs. GROUP BY

While both DISTINCT and GROUP BY can be used to eliminate duplicates, they serve different purposes:

DISTINCT: Simpler syntax, focused solely on removing duplicates
GROUP BY: More powerful for aggregations (COUNT, SUM, AVG, etc.)
Use DISTINCT: When you only need unique values
Use GROUP BY: When you need to perform calculations on groups
Order of operations: DISTINCT is processed after the SELECT list is evaluated, but before ORDER BY

Practical DISTINCT Examples with TokTuk

Example 1: Finding Unique Video Titles

This query returns a list of all unique video titles on the TokTuk platform, which could be useful for building title filters or understanding content diversity.

Example 2: Finding Unique User Interactions

This query identifies all the different ways users are interacting with content on TokTuk, showing each unique type of interaction that exists in the system.

Example 3: Complex Analysis with DISTINCT

This query shows how to combine DISTINCT with aggregation functions, counting the number of unique viewers for each video.

DISTINCT Performance Considerations

While DISTINCT is a powerful tool, it comes with some performance implications that are important to understand:

Memory usage: Can consume significant memory with large result sets
Alternative approaches: Consider GROUP BY or subqueries for better performance in some cases
Indexing: Proper indexes on the DISTINCT columns can improve performance

SQL Learning Hub