When working with a database, it is highly possible that you may encounter scenarios where you need to retrieve unique values from the tables. These values could be in the form of a specific column or a combination of columns found in the table. In Oracle SQL, there are two distinct keywords you can use to achieve this: DISTINCT and UNIQUE. Although their names sound like they may do the same thing, there are subtle differences that make them unique. In this article, we will explore DISTINCT vs. UNIQUE in Oracle SQL and provide some examples.
DISTINCT
The DISTINCT clause in SQL is used to retrieve only distinct or unique records from a table. In other words, it eliminates duplicate rows and returns only unique rows. These values could be in the form of a specific column or a combination of columns found in the table. Let's take a look at an example:
Consider the following table:
| ID | Name | Color |
|---|---|---|
| 1 | Apple | Red |
| 2 | Orange | Orange |
| 3 | Apple | Green |
Let's say we want to retrieve only the unique names from this table. We can use the following SQL statement:
SELECT DISTINCT Name
FROM Fruits;
The output of this query will be:
| Name |
|---|
| Apple |
| Orange |
Note that the duplicate record "Apple" was eliminated. The DISTINCT keyword is recognizing only unique values and not including duplicate records.
UNIQUE
The UNIQUE keyword is also used for achieving the same result as the DISTINCT keyword. But, there is a subtle difference between DISTINCT and UNIQUE. UNIQUE is a constraint that can be used to ensure that all the values in a column are unique. While DISTINCT is used to eliminate duplicate rows in a result set. UNIQUE can be used to create a unique index on a table. When you create a UNIQUE constraint, all the values in the constrained columns must be unique. If you try to insert a duplicate value, the database will reject it, and you will receive an error.
Here is an example:
Let's create a table called "students" and insert some data:
CREATE TABLE students (
student_id NUMBER,
first_name VARCHAR2(50),
last_name VARCHAR2(50),
CONSTRAINT students_pk PRIMARY KEY(student_id)
);
INSERT INTO students (student_id, first_name, last_name)
VALUES (1, 'John', 'Doe');
INSERT INTO students (student_id, first_name, last_name)
VALUES (2, 'Jane', 'Doe');
INSERT INTO students (student_id, first_name, last_name)
VALUES (3, 'John', 'Smith');
Now, let's add a UNIQUE constraint to ensure that all the first names in the "students" table are unique:
ALTER TABLE students
ADD CONSTRAINT students_uk UNIQUE (first_name);
If you try to insert a duplicate value, you will receive an error:
INSERT INTO students (student_id, first_name, last_name)
VALUES (4, 'John', 'Doe');
ORA-00001: unique constraint (STUDENTS_UK) violated
As you can see, the UNIQUE constraint ensures that there are no duplicate values in the "first_name" column.
DISTINCT vs. UNIQUE
To sum up, DISTINCT is a query that selects unique values from a table, while UNIQUE is a constraint that prevents the insertion of duplicate values into a column. Although both keywords serve similar purposes, they are used in different contexts. In general, if you need to eliminate duplicate rows from a query result set, use DISTINCT. If you need to ensure the uniqueness of a column or set of columns in a table, use UNIQUE.
Conclusion
In this article, we have discussed the differences between DISTINCT and UNIQUE in Oracle SQL. We have shown that both keywords are useful and have different use cases. It is important to understand the differences when working with Oracle SQL. By using the appropriate keyword, you can ensure that you are achieving your goals effectively and efficiently.
let's delve deeper into the topics we covered earlier.
DISTINCT in SQL
The DISTINCT keyword is used in SQL to eliminate duplicates from the result set. The result set returns only unique records. It is used with the SELECT statement to retrieve unique values from a single column or a combination of columns. Here are a few things to note about the DISTINCT keyword:
- The DISTINCT keyword is applied to the entire row and not a single column.
- It is not case-sensitive, meaning that it considers “APPLE” and “apple” as the same value.
- It slows down query performance because it has to compare each row for uniqueness.
Here's an example of using the DISTINCT keyword in SQL:
SELECT DISTINCT Color
FROM Fruits;
This query retrieves the unique values from the "Color" column of the "Fruits" table.
UNIQUE Constraint
A UNIQUE constraint is used to ensure that all values in a column or a set of columns in a table are unique. It can be applied to a single column or multiple columns. A unique constraint creates a unique index on the column(s), which allows the database to check for duplicates quickly. Here are a few things to note about the UNIQUE constraint:
- It can be used to define a primary key constraint for a table.
- It does not allow NULL values in the column.
- It prevents duplicates from being inserted into the column.
- It can improve query performance because it avoids the use of the DISTINCT keyword.
Here's an example of using the UNIQUE constraint in SQL:
CREATE TABLE Instructors (
instructor_id INTEGER PRIMARY KEY,
instructor_name VARCHAR(50) NOT NULL,
email VARCHAR(50) UNIQUE
);
In this example, we are defining the "email" column as a unique constraint. This ensures that each email address inserted into the column is unique.
DISTINCT vs. UNIQUE
Although both DISTINCT and UNIQUE eliminate duplicates and return unique values, they are used in different contexts. DISTINCT is used in a SELECT statement to retrieve unique values in a column or a set of columns. UNIQUE is used to prevent duplicates from being inserted into a column. Here are a few differences between the two:
- DISTINCT is used to retrieve unique values from a result set, while UNIQUE is used to ensure that values in a column or a set of columns are unique.
- DISTINCT is slower than UNIQUE because it has to compare each row for uniqueness, while UNIQUE avoids duplication by creating a unique index.
- DISTINCT can be used with multiple columns, while UNIQUE applies only to a single column or a set of columns.
- DISTINCT can be used with NULL values, while UNIQUE does not allow NULL values.
To sum up, both DISTINCT and UNIQUE are useful but in different contexts. If you want to retrieve only unique values from a result set, use the DISTINCT keyword. If you want to ensure that a column or a set of columns contain only unique values, use the UNIQUE constraint.
Popular questions
-
What is the difference between DISTINCT and UNIQUE in Oracle SQL?
Answer: DISTINCT is used to retrieve unique values from a result set, while UNIQUE is used to ensure that values in a column or a set of columns are unique. -
Can I apply the DISTINCT keyword to multiple columns?
Answer: Yes, you can use the DISTINCT keyword with multiple columns to retrieve unique combinations of values in those columns. -
Does the UNIQUE constraint allow NULL values?
Answer: No, the UNIQUE constraint does not allow NULL values in the column(s) to which it is applied. -
Which keyword may slow down query performance: DISTINCT or UNIQUE?
Answer: DISTINCT may slow down query performance because it has to compare each row for uniqueness. -
What is the difference in case-sensitivity when using the DISTINCT keyword?
Answer: DISTINCT keyword is not case-sensitive, meaning that it considers “APPLE” and “apple” as the same value.
Tag
Uniqueness
