EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login
Home Data Science Data Science Tutorials Oracle Tutorial DISTINCT in Oracle
Secondary Sidebar
Oracle Tutorial
  • Advanced
    • Timestamp to Date in Oracle
    • Oracle Golden Gate
    • Oracle Virtual Machine
    • Oracle Describe Table
    • Oracle Clauses
    • Oracle Having Clause
    • Oracle?Primavera
    • Oracle FOREIGN Key
    • PIVOT in Oracle
    • Oracle Alter Table
    • Oracle Queries
    • Oracle Views
    • Oracle Window Functions
    • Oracle String Functions
    • Oracle Date Functions
    • Oracle Analytic Functions
    • Oracle Aggregate Functions
    • Select in Oracle
    • INSERT in Oracle
    • DISTINCT in Oracle
    • Function in Oracle
    • Oracle GROUP_CONCAT
    • Oracle INSTR()
    • Oracle CONVERT
    • Oracle LENGTH()
    • Oracle EXISTS
    • Oracle REPLACE()
    • Oracle MERGE
    • Oracle LEAD()
    • Oracle EXTRACT()
    • Oracle LISTAGG()
    • Oracle SYS_CONTEXT()
    • Oracle COALESCE
    • Oracle NVL()
    • Oracle SYSDATE()
    • Oracle?Date Format
    • Oracle SYS_GUID()
    • Oracle WILDCARDS
    • Oracle Synonyms
    • Oracle Subquery
    • BETWEEN in Oracle
    • FETCH in Oracle
    • Oracle Index
    • Oracle Function-based Index
    • Oracle UNIQUE Index
    • Oracle Bitmap Index
    • Oracle Column
    • Oracle Triggers
    • Oracle Procedures
    • Sample Database for Oracle
    • Oracle LIKE Operator
    • ORDER BY in Oracle
    • Oracle ORDER BY DESC
    • GROUP BY in Oracle
    • Oracle GROUP BY HAVING
    • Oracle Aliases
    • Table in Oracle
    • Oracle Temporary Table
    • Oracle? Table Partition
    • Oracle rename table
    • Oracle CTE
    • Cursor in Oracle
    • Oracle LOCK TABLE
    • Oracle Tablespace
    • Oracle CARDINALITY
    • Oracle REGEXP
    • Oracle REGEXP_REPLACE
    • Oracle to_date
    • JSON in Oracle
    • Oracle COMMIT
    • Oracle GRANT
    • Oracle MD5
    • Oracle ROLLBACK
    • Oracle Users
    • Oracle TIMESTAMP
    • IF THEN ELSE in Oracle
    • Oracle While Loop
    • Oracle Clone Database
    • Oracle Backup Database
    • Oracle? XML
    • Oracle XMLAGG
    • Oracle XMLTABLE
    • Oracle Performance Tuning
    • Oracle B Tree Index
    • Oracle fusion
    • Oracle ebs
    • Oracle GRC
    • Oracle ERP
    • Oracle ASM
    • Oracle Cloud
    • Oracle HCM Cloud
    • Oracle Integration Cloud
    • Oracle Jinitiator
    • Oracle pathfinder
    • Oracle VirtualBox
    • Oracle Weblogic Server
    • Oracle decode
    • Oracle Exadata
    • Oracle ZFS
    • Oracle? utilities
    • JDBC Driver for Oracle
    • Oracle? DBA Versions
    • Oracle DBA Salary
  • Basic
    • Oracle Marketing Cloud
    • What is Oracle?
    • Career in Oracle
    • How to Install Oracle
    • Oracle Versions
    • What Is Oracle Database
    • Oracle Data Warehousing
    • Oracle Warehouse Builder
    • Career In Oracle Database Administrator
    • Career In Oracle DBA
    • What is Oracle RAC
    • Oracle DBA
    • Oracle? Vanderbilt
    • What is RMAN Oracle
    • Oracle Database Administration
    • Oracle Operators
    • Oracle Constraints
    • Oracle number
    • Oracle Data Types
    • Oracle UNIQUE Constraint
    • Oracle Check Constraint
  • Joins
    • Joins in Oracle
    • Inner Join in Oracle
    • Oracle Cross Join
    • Left Join in Oracle
    • OUTER Join in Oracle
    • Oracle Full Outer Join
    • Natural Join in Oracle
    • Oracle Self Join
    • Oracle hash join
    • Oracle? Update with Join
  • Oracle SET Operators
    • UNION in Oracle
    • Oracle UNION ALL
    • INTERSECT in Oracle
    • MINUS in Oracle
  • Interview Questions
    • Oracle Interview Questions
    • Oracle Apps Interview Questions
    • Oracle Apps Technical Interview Questions
    • Oracle Database Interview Questions
    • Oracle Forms Interview Questions
    • Oracle PL/SQL Interview Questions
    • Oracle RAC Interview Questions
    • Oracle SOA Interview Questions

Related Courses

Oracle Course Training

Oracle DBA Certification Course

MongoDB Certification Training

DISTINCT in Oracle

By Priya PedamkarPriya Pedamkar

distnict-in-oracle

Introduction to DISTINCT in Oracle

DISTINCT keyword in Oracle database separates or filters the duplicate rows in the result set returned by the SELECT statement (result set contains the column/columns present in the SELECT statement) so that the column or columns returned by the query contains all the values or records which are not duplicate and are unique to each other as the DISTINCT keyword compares each value of the column/columns present in the result set returned by the query with each other.

Syntax

We will look now into the syntax of the distinct keyword in Oracle. We have two options in this case for syntax. The first syntax is to use distinct keyword without conditions and the second syntax is to use distinct keyword with conditions.

Syntax without Condition: Select DISTINCT(expressions) from table_names;

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Syntax with the Condition:Select DISTINCT(expressions) from table_names [where conditions];

Parameters

We will now look at the various parameters used in the two above syntaxes.

All in One Data Science Bundle(360+ Courses, 50+ projects)
Python TutorialMachine LearningAWSArtificial Intelligence
TableauR ProgrammingPowerBIDeep Learning
Price
View Courses
360+ Online Courses | 50+ projects | 1500+ Hours | Verifiable Certificates | Lifetime Access
4.7 (86,171 ratings)
  • expressions: we provide the column names or expressions that we want to execute.
  • table_names: We provide the table names from which we want the records. Important information to note here is that there should be at least one table name after the clause.
  • [Where conditions]: It is optional If we provide the where condition than we will retrieve the values after query execution only if the condition that was mentioned is satisfied.

Note: One Important Information to keep in mind is that the DISTINCT keyword considers null value also as a unique value. So, in case if we have null values in a column and we are using the DISTINCT keyword to fetch unique values then it will also display the NULL value in the output.

How to Use DISTINCT in Oracle?

By now we have discussed what is a DISTINCT keyword in oracle, the syntax of DISTINCT keyword and the various parameters used in the syntax.

Now we will look into various examples to learn how to efficiently use DISTINCT in Oracle. Before we move into the examples, we will use the EMPLOYEE table already created in the database. The table consists of five columns which are NAME, VEHICLE_NAME, EMPLOYEE_ID, VEHICLE_ID, and CITY. Below is the screenshot of the values present in the employee table. The query we will be using for this is the basic SELECT statement

SELECT  * from employee;

On executing in SQL developer we get all the values as shown below.

Output: Employee table

Employee table

Now let us begin with the examples.

1. Unique Values in a Column without Condition

In this example, we are going to use DISTINCT to find the unique values in a particular column of a table without having any conditions. As an example, we are going to find the unique values present in the vehicle column of the employee table. The query is shown below:

Code:

select DISTINCT(vehicle_name) from employee;

Output:

In the above query as you can see we have provided the column name as well as the table name. On executing the above query in SQL developer we get the below output.

 Unique Values

2. Unique Values in a Column with Condition

In this example, we are going to find unique values in a column just like the previous example but with a slight modification. In this, we are going to extract the unique values based on some condition. As an example let us get the unique values of column vehicle name from employee table when the city is not Kolkata. The query for the same is provided below.

Code:

select DISTINCT(VEHICLE_NAME) from employee where CITY != 'KOLKATA';

Output:

If you see the above query the query should return the values which satisfy the where condition. On executing the above query in SQL developer we get the below output.

DISTINCT in Oracle 3

As you can see in the previous example we had six unique values whereas in the second example we have five unique values as we had added a specific condition in the second example.

3. Unique Values From Multiple Columns

In this example, we are going to find unique values on multiple columns by using DISTINCT. So earlier examples we had used single columns but in this case, we are going to use multiple columns. For example, suppose we want to find the unique values of both vehicle name and city and the results should be in ascending order based on the column city. Let us look at the query for the same below.

Code:

select DISTINCT VEHICLE_NAME,CITY from employee ORDER BY CITY ASC;

Output:

In the above query first, the data is extracted in ascending order based on column city and then both values in the columns vehicle name and city are used to get the unique pairs. On executing the above query in SQL developer we get the below output.

DISTINCT in Oracle 4

4. DISTINCT with NULL values

Earlier in the article, we had discussed that DISTINCT treats NULL values as unique values so if we have a column with null values in it. It will be treated as a unique value. For example, we have null values in the CITY column which you can see in the screenshot provided at the beginning of the article where all values of the employee table are shown. So, we will apply DISTINCT on the Column CITY to find out the impact of null values on the result. The query for the same is shown below.

Code:

select DISTINCT CITY from employee;

Output:

On executing the above query in SQL developer we get the below output.

DISTINCT in Oracle 5

As we can see the output shows null as a distinct value. So this shows that DISTINCT treats null as a unique value.

Conclusion – DISTINCT in Oracle

In this article, we discussed the DISTINCT keyword in oracle, the syntaxes used for using DISTINCT in queries, the parameters used in those syntaxes and then through various examples we came to know how we can use DISTINCT in oracle to solve our required tasks.

Recommended Articles

This is a guide to DISTINCT in Oracle. Here we discuss DISTINCT keyword in oracle, the syntaxes used for using DISTINCT in codes and output. You can also go through our other related articles to learn more –

  1. Joins in Oracle
  2. What Is Oracle Database
  3. Oracle String Functions
  4. Oracle Warehouse Builder
  5. Guide to Top Versions of Oracle
  6. ORDER BY in MySQL | How to Works?
  7. Guide to this Keyword in C#
Popular Course in this category
Oracle Training (14 Courses, 8+ Projects)
  14 Online Courses |  8 Hands-on Projects |  120+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course

Related Courses

Oracle DBA Database Management System Training (2 Courses)4.9
All in One Financial Analyst Bundle- 250+ Courses, 40+ Projects4.8
1 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Data Science Course

SPSS, Data visualization with Python, Matplotlib Library, Seaborn Package

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more