What’s the Difference Between UNIQUE and DISTINCT in SQL?
We use the words unique and distinct interchangeably in our day-to-day life. However, these words play very different roles in SQL. Let’s find out what is so different about them and how we can use them in SQL.
Despite the words unique and distinct being synonyms, they cover very different things in SQL. The UNIQUE keyword in SQL plays the role of a database constraint; it ensures there are no duplicate values stored in a particular column or a set of columns. On the other hand, the DISTINCT keyword is used in the SELECT statement to fetch distinct rows from a table.
The best place to learn about SQL and databases is LearnSQL.com. It is an online platform designed to help you master all database topics. It offers over 70 SQL courses, including a comprehensive SQL from A to Z track with the complete interactive SQL learning path.
Let’s have a closer look at both the UNIQUE and DISTINCT keywords in SQL.
The DISTINCT Keyword in SQL
The DISTINCT keyword in SQL is used with the SELECT statement. By using SELECT immediately followed by the DISTINCT keyword, all the duplicate rows are removed from the result set.
Let’s see an example of DISTINCT in action.
We are given the Cars table, which contains the brand and model information for some cars:
| Id | Brand | Model | ProductionYear |
|---|---|---|---|
| AQ16 | Audi | Q7 | 2011 |
| FK19 | Ford | Kuga | 2019 |
| JC07 | Jeep | Cherokee | 2012 |
| JW15 | Jeep | Wrangler | 2015 |
| TS14 | Tesla | Model S | 2014 |
| TY20 | Tesla | Model Y | 2020 |
Let’s run a simple SELECT statement to get all the brands of cars produced after 2011:
SELECT Brand FROM Cars WHERE ProductionYear > 2011; |
The result of the above query is as follows.
| Brand |
|---|
| Ford |
| Jeep |
| Jeep |
| Tesla |
| Tesla |
As you can see, we have duplicate values for Jeep and Tesla. What if we want a single record for each value? We use the DISTINCT keyword:
SELECT DISTINCT Brand FROM Cars WHERE ProductionYear > 2011; |
And the result is…
| Brand |
|---|
| Ford |
| Jeep |
| Tesla |
Now you see how easy and convenient it is to use the DISTINCT keyword.
To get a deeper insight on the DISTINCT keyword in SQL, check out our article on What Is the Role of DISTINCT in SQL?.
The UNIQUE Keyword in SQL
The UNIQUE keyword in SQL marks one of its database constraints. By using the UNIQUE constraint on a column or a set of columns, you can prevent duplicate values being stored in this column or set (i.e. the combination of columns’ values must be unique, although each value might occur multiple times in the same column).
Let’s see the UNIQUE constraint in action. We’ll create a Person table that stores a person’s name, social security number, and nationality.
CREATE TABLE Person ( RowId INTEGER PRIMARY KEY, FirstName varchar(50), LastName varchar(50), SSN varchar(20) UNIQUE, Nationality varchar(30)); |
Here, the SSN column must store only non-duplicate values; hence, there is a UNIQUE keyword in this column’s definition, which imposes the UNIQUE constraint on it.
For more practice with table creation and database design, check out our track on Creating Database Structure. It includes basics of table creation, SQL data types, constraints, and more. And for some insight into the track, read this article.
Now, let’s insert some values into the Person table.
INSERT INTO Person VALUES (1, 'John', 'Black', '321456987123', 'American');INSERT INTO Person VALUES (2, 'Isabella', 'Sanchez', '987456345123', 'Spanish');INSERT INTO Person VALUES (3, 'Klaus', 'Schmidt', '987456345123', 'German'); |
We get an error at the third INSERT statement. Why? Because a UNIQUE constraint has been defined on the SSN column; hence, the same value cannot appear twice in the SSN column. Both Isabella and Klaus have the same SSN value, so we get an error message. We did not obey the rules set by the UNIQUE constraint.
If we change the last INSERT statement to…
INSERT INTO Person VALUES (3, 'Klaus', 'Schmidt', '876456345123', 'German'); |
… it is accepted into the table. After executing all three INSERT statements, including the corrected third statement, the Person table looks like this:
| RowId | FirstName | LastName | SSN | Nationality |
|---|---|---|---|---|
| 1 | John | Black | 321456987123 | American |
| 2 | Isabella | Sanchez | 987456345123 | Spanish |
| 3 | Klaus | Schmidt | 876456345123 | German |
The UNIQUE constraint prevents any INSERTs or UPDATEs to a table that result in duplicate values in the column or set of columns that implements the constraint.
So, the table below violates the UNIQUE constraint set on the SSN column…
| RowId | FirstName | LastName | SSN | Nationality |
|---|---|---|---|---|
| 1 | John | Black | 321456987123 | American |
| 2 | Isabella | Sanchez | 987456345123 | Spanish |
| 3 | Klaus | Schmidt | 987456345123 | German |
…and this table obeys the UNIQUE constraint set on the SSN column.
| RowId | FirstName | LastName | SSN | Nationality |
|---|---|---|---|---|
| 1 | John | Black | 321456987123 | American |
| 2 | Isabella | Sanchez | 987456345123 | Spanish |
| 3 | Klaus | Schmidt | 876456345123 | German |
You can define the UNIQUE constraint during table creation, as it was shown here, or after the table is created. The UNIQUE constraint can also be later modified or removed. To learn how to do all these, check out the article What Is a Unique Constraint in SQL?. We also offer an interactive course on SQL Constraints to help you grasp the different constraints SQL makes available.
Putting It All Together: UNIQUE vs. DISTINCT in SQL
We’ve defined the UNIQUE and DISTINCT keywords. But what are the actual differences between them? We summarized them in table:
| UNIQUE | DISTINCT | |
|---|---|---|
| Purpose | Enforces uniqueness of all values in a column or columns. | Filters out duplicate values in a set of results. |
| Usage | Table definitions and constraints | SQL queries |
| Scope | A column or a set of columns in a table. | One or more columns in a SELECT statement. |
| Keyword Type | Constraint in table creation or modification. | Clause used in SELECT queries. |
| SQL Command | Often used with CREATE TABLE or ALTER TABLE. |
Used with SELECT. |
| Example | ALTER TABLE users ADD CONSTRAINT unique_email UNIQUE (email); |
SELECT DISTINCT name, age FROM users; |
| Data Integrity | Ensures data integrity by preventing duplicates in column(s). | Does not affect data integrity; filters results for the query. |
To learn more about SQL we recommend our SQL from A to Z track, which is the most comprehensive SQL learning track with 7 courses that cover beginner and advanced topics. Sign up today and good luck!
