Oracle Select Distinct Usage

**Understanding Oracle's SELECT DISTINCT: A Comprehensive Guide for Beginners**


The 'SELECT DISTINCT' statement is a powerful tool in Oracle SQL that allows you to retrieve unique values from a column in a table. For beginners just starting with Oracle, grasping the concept and usage of `SELECT DISTINCT` is essential for eliminating duplicate rows and obtaining meaningful insights from data.

In this blog, we'll guide you through the `SELECT DISTINCT` statement step by step, providing clear explanations and real-time examples to help you understand its significance and usage effectively.


### What is the SELECT DISTINCT Statement?


The "SELECT DISTINCT" statement gets only unique values from a column or if there are many columns then it will show distinct values for those combinations of column in a table.


It actually removes the duplicate data from the result output and make ensure that each row should return unique values.


The syntax for `SELECT DISTINCT` is straightforward and easy to use.


SELECT DISTINCT column_name


FROM table_name;


### Basic Usage of SELECT DISTINCT


Let's start with a simple example to understand the basic usage of `SELECT DISTINCT`.


Suppose we have a `students` table with a `city` column.


To retrieve unique cities from the `students` table:


SELECT DISTINCT city


FROM students;


### Real-time Example:

Unique Email Addresses


Imagine you have a `users` table with an `email` column.


To fetch unique email addresses from the `users` table:


SELECT DISTINCT email


FROM users;


### SELECT DISTINCT with Multiple Columns


You can also use `SELECT DISTINCT` with multiple columns to retrieve unique combinations of values.


For instance, consider a `sales` table with `product_id` and `sales_date` columns.


To get unique product and sales date combinations:


SELECT DISTINCT product_id, sales_date


FROM sales;


### SELECT DISTINCT with ORDER BY


You can combine `SELECT DISTINCT` with 'ORDER BY' to sort unique values.


Suppose you want to retrieve unique cities from the `students` table in alphabetical order:


SELECT DISTINCT city


FROM students


ORDER BY city ASC;


### Handling NULL Values with SELECT DISTINCT


`SELECT DISTINCT` treats NULL values as unique.


If you want to exclude NULL values while using `SELECT DISTINCT`, you can add a WHERE clause.


For example, to fetch non-NULL email addresses from the `users` table:


SELECT DISTINCT email


FROM users


WHERE email IS NOT NULL;


SELECT DISTINCT with Aggregate Functions


You can use SELECT DISTINCT with aggregate functions like COUNT, SUM, AVG, etc.


For instance, to count the number of unique cities in the students table:


SELECT COUNT(DISTINCT city) AS unique_cities_count


FROM students;


Using DISTINCT with JOINs


When using JOIN operations, SELECT DISTINCT can help retrieve unique combinations from joined tables.


Suppose you have students and courses tables and you want to fetch unique student-course combinations:


SELECT DISTINCT s.student_name, c.course_name


FROM students s


JOIN enrollments e ON s.student_id = e.student_id


JOIN courses c ON e.course_id = c.course_id;


### Conclusion


The `SELECT DISTINCT` statement is a valuable feature in Oracle SQL for retrieving unique values from columns.


It helps in eliminating duplicate rows and simplifying data analysis.


From basic usage to handling multiple columns and sorting, `SELECT DISTINCT` offers versatility to meet various data retrieval needs.


By understanding its syntax and applying it with real-time examples, beginners can master the `SELECT DISTINCT` statement and enhance their SQL skills.


By practicing and experimenting with different scenarios, you'll gain confidence in using `SELECT DISTINCT` effectively in your Oracle database queries.