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 30 SQL courses, including a comprehensive SQL from A to Z track with the complete interactive SQL learning path or Creating Database Structure that will teach you about database constraints like UNIQUE.

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:

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.

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…

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:

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…

…and this table obeys the UNIQUE constraint set on the SSN column.

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?

The UNIQUE keyword marks a database constraint and is defined during table creation or later on a specific column or a set of columns. The DISTINCT keyword is used while fetching the result set from the database. It directly follows the SELECT keyword when we want to make sure that all the rows of the result set contain distinct values or a distinct set of values.

We know that it is always good to get as much hands-on practice as possible, so we’ve prepared a SQL Practice Set that will help you to learn and practice all the different features offered by SQL. To get yourself motivated, check out our article on the "SQL Practice Set" course.

Good luck!