Class-20

Understanding the SELECT DISTINCT Statement in SQL

When working with databases, it is common to encounter duplicate records in a table. The SELECT DISTINCT statement in SQL is a powerful tool that allows us to retrieve unique values from one or more columns, eliminating duplicates from the result set.

Using SELECT DISTINCT with a Single Column

The SELECT DISTINCT statement is similar to a regular SELECT statement, but it ensures that only unique values are returned. Here is the basic syntax:

SELECT DISTINCT column_name
FROM table_name;

For example, if we have an actor table and we want to retrieve unique first names, we can use:

SELECT DISTINCT first_name
FROM actor;

This query will return all distinct first names from the actor table, ensuring that duplicate values do not appear more than once.

Combining SELECT DISTINCT with ORDER BY

We can use the ORDER BY clause along with SELECT DISTINCT to sort the unique values in ascending or descending order:

SELECT DISTINCT first_name
FROM actor
ORDER BY first_name DESC;

This ensures that the unique first names are sorted from Z to A.

Using SELECT DISTINCT with Multiple Columns

When using SELECT DISTINCT with multiple columns, SQL will return unique combinations of values across the selected columns. The syntax is:

SELECT DISTINCT column1, column2
FROM table_name;

For example, if we want to get unique combinations of first_name and last_name from the actor table:

SELECT DISTINCT first_name, last_name
FROM actor;

Here, the combination of first_name and last_name must be unique for the row to be included in the result.

Practical Example: Finding Unique Ratings in a Film Table

Let's say we have a film table containing various movie ratings. We want to know what different ratings exist in the table. We can use:

SELECT DISTINCT rating
FROM film;

This will return a list of all unique ratings in the film table.

Now, if we also want to check unique combinations of rating and rental_duration, we can use:

SELECT DISTINCT rating, rental_duration
FROM film;

This will provide distinct pairs of ratings and rental durations, showing all unique combinations present in the table.

Best Practices and Considerations

• SELECT DISTINCT should be used carefully with large datasets as it may impact performance due to additional processing required to remove duplicates.
• If you only need unique values from a single column, SELECT DISTINCT is efficient and useful.
• When using multiple columns, remember that distinctness is applied across all selected columns.
• Using ORDER BY with SELECT DISTINCT improves readability and organizes the results better.

Conclusion

The SELECT DISTINCT statement is an essential SQL feature that helps filter out duplicate records, providing a cleaner and more meaningful dataset. Whether you're retrieving unique values from a single column or across multiple columns, understanding how SELECT DISTINCT works enables more efficient and effective database queries.