The SELECT UNIQUE construct is an Oracle-only SQL statement. It is equivalent to SELECT DISTINCT.
SELECT UNIQUE is Oracle-specific and identical to SELECT DISTINCT — for cross-database compatibility, always prefer SELECT DISTINCT, which is the ANSI SQL standard.
Syntax
The syntax for SELECT UNIQUE is as follows:
SELECT UNIQUE "column_name"
FROM "table_name";
Example
We use the following table for our example.
Table Store_Information
Store_Name
Sales
Txn_Date
Los Angeles
1500
Jan-05-1999
San Diego
250
Jan-07-1999
Los Angeles
300
Jan-08-1999
Boston
700
Jan-08-1999
To select all distinct stores in Table Store_Information in Oracle, we key in,
SELECT UNIQUE Sales FROM Store_Information;
Result:
Sales
1500
250
300
700
Frequently Asked Questions
What is SELECT UNIQUE in SQL?
SELECT UNIQUE is an Oracle-specific syntax that returns only distinct values from a column. It behaves identically to SELECT DISTINCT but is not supported outside of Oracle databases.
Is SELECT UNIQUE the same as SELECT DISTINCT?
Yes, in Oracle they are equivalent. SELECT DISTINCT is the ANSI SQL standard and works across MySQL, SQL Server, PostgreSQL, and others, while SELECT UNIQUE only works in Oracle.
Can I use SELECT UNIQUE in MySQL or SQL Server?
No. SELECT UNIQUE is Oracle-only. Use SELECT DISTINCT instead for compatibility with MySQL, SQL Server, PostgreSQL, and other databases.
Which syntax should I prefer?
SELECT DISTINCT is recommended because it is the SQL standard and portable across all major databases. Only use SELECT UNIQUE in Oracle-specific contexts where legacy code requires it.
Exercises
For these exercises, assume we have a table called Region_Sales with the following data:
Table Region_Sales
Region
Year
Orders
Total_Sales
West
2013
1560
325000
West
2014
1820
380000
North
2013
790
148000
North
2014
995
185000
East
2013
1760
375000
East
2014
2220
450000
South
2013
1790
388000
South
2014
1695
360000
1. (True or False) The following two statements are equivalent in MySQL:
SELECT UNIQUE Region FROM Region_Sales;
SELECT DISTINCT Region FROM Region_Sales;
2. Which of the following statements are valid in Oracle?
a) SELECT DISTINCT Year FROM Region_Sales;
b) SELECT UNIQUE * FROM Region_Sales;
c) SELECT * FROM Region_Sales;
d) SELECT UNIQUE Year FROM Region_Sales;
3. What is the output for the following statement?
SELECT UNIQUE Region FROM Region_Sales WHERE Orders < 1000;