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:

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? We summarized them in table:

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!