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!