Oracle SELECT DISTINCT: Simple Guide for Beginners

Have you ever wondered how to remove duplicate information from your database results? Oracle’s SELECT DISTINCT is like having a magic eraser that removes duplicate rows from your data. Let’s learn this important database skill step by step!

What is SELECT DISTINCT?

Think of SELECT DISTINCT as a filter that removes duplicate entries from your data. It’s like having a list of student names where “John” appears three times, but you only want to see “John” once.

Basic Idea:

• DISTINCT = “show me only unique values”
• It removes duplicate rows automatically
• Makes your data cleaner and easier to read

Simple Syntax to Remember

The basic pattern is very easy:

SELECT DISTINCT column_name 
FROM table_name;

For multiple columns:

SELECT DISTINCT column1, column2 
FROM table_name;

Example 1: School Subject List

Let’s imagine you’re working with a student database at your school:

Step 1: Create a simple table

CREATE TABLE student_subjects (
    student_name VARCHAR2(30),
    subject VARCHAR2(20),
    grade CHAR(1)
);

Step 2: Add some student data

INSERT INTO student_subjects VALUES ('Alice', 'Math', 'A');
INSERT INTO student_subjects VALUES ('Bob', 'Math', 'B');
INSERT INTO student_subjects VALUES ('Alice', 'Science', 'A');
INSERT INTO student_subjects VALUES ('Charlie', 'Math', 'C');
INSERT INTO student_subjects VALUES ('Bob', 'English', 'B');
INSERT INTO student_subjects VALUES ('Alice', 'English', 'A');

Step 3: Find all unique subjects

SELECT DISTINCT subject 
FROM student_subjects;

What you’ll see:

SUBJECT
--------
English
Math
Science

Why this works: Even though Math appears 3 times in our data, DISTINCT shows it only once!

Example 2: Student Grade Combinations

Now let’s try something a bit more interesting – finding unique student and grade combinations:

Query:

SELECT DISTINCT student_name, grade 
FROM student_subjects 
ORDER BY student_name;

Result:

STUDENT_NAME    GRADE
------------    -----
Alice           A
Bob             B
Charlie         C

What happened:

• Alice had grade ‘A’ in multiple subjects, but we see her only once
• Each student-grade combination appears only once
• Data is much cleaner and easier to understand!

Example 3: Understanding NULL Values

Sometimes data has missing information (called NULL). Let’s see how DISTINCT handles this:

Create a new table:

CREATE TABLE student_info (
    student_name VARCHAR2(30),
    city VARCHAR2(20),
    hobby VARCHAR2(20)
);

Add data with some missing cities:

INSERT INTO student_info VALUES ('John', 'Mumbai', 'Cricket');
INSERT INTO student_info VALUES ('Sara', NULL, 'Reading');
INSERT INTO student_info VALUES ('Mike', 'Delhi', 'Football');
INSERT INTO student_info VALUES ('Lisa', NULL, 'Dancing');
INSERT INTO student_info VALUES ('Tom', 'Mumbai', 'Cricket');

Find unique cities:

SELECT DISTINCT city 
FROM student_info 
ORDER BY city;

Result:

CITY
----
Delhi
Mumbai
(null)

Important Point: Even though we have two students with missing cities (NULL), DISTINCT shows NULL only once!

Key Learning Points

What DISTINCT Does:

• Removes duplicate rows from your results
• Works with one column or multiple columns
• Treats NULL values as the same (shows only one NULL)

When to Use DISTINCT:

• Getting a list of unique values
• Removing duplicate information
• Creating clean reports
• Finding different categories in your data

Simple Rules to Remember:

1. Put DISTINCT right after SELECT
2. List the columns you want unique combinations for
3. Use ORDER BY to sort your results nicely
4. Remember: multiple NULLs become one NULL

Practice Exercise

Try this yourself! Create a table with your favorite movies and their genres, then use DISTINCT to find:

1. All unique genres
2. All unique movie-genre combinations

Example data to start with:

CREATE TABLE movies (
    movie_name VARCHAR2(50),
    genre VARCHAR2(20),
    year NUMBER
);

INSERT INTO movies VALUES ('Spider-Man', 'Action', 2021);
INSERT INTO movies VALUES ('Frozen', 'Animation', 2019);
INSERT INTO movies VALUES ('Batman', 'Action', 2022);
INSERT INTO movies VALUES ('Toy Story', 'Animation', 2020);

Why This Matters

Understanding SELECT DISTINCT helps you:

• Clean up messy data
• Create better reports
• Answer questions like “How many different subjects do we teach?”
• Prepare data for further analysis

Real-world Applications:

• Finding all unique products in a store
• Listing all different cities your customers live in
• Identifying all subjects taught in your school
• Getting unique email domains from a contact list

Quick Reference

Basic Pattern:

SELECT DISTINCT column_name FROM table_name;

With Sorting:

SELECT DISTINCT column_name 
FROM table_name 
ORDER BY column_name;

Multiple Columns:

SELECT DISTINCT col1, col2 
FROM table_name 
ORDER BY col1, col2;

Conclusion

SELECT DISTINCT is your best friend for removing duplicates and getting unique values from your database. Start with simple examples like finding unique subjects or cities, then gradually work with more complex combinations. Remember: practice makes perfect, so try creating your own tables and experimenting with different DISTINCT queries!