Back to articles list May 20, 2021 - 5 minutes read What’s the Difference Between UNIQUE and DISTINCT in SQL? Martyna Sławińska Martyna is a software developer with a passion for programming, automation, and innovation. Currently working for the global corporate IT consulting sector, she is a new-generation techie who hails from Warsaw University of Technology, Poland. She has certifications in both database administration and Java development. Her interests include creating small automation devices using embedded systems and various electronic components. In her free time, she practices yoga, which helps her to center her thoughts and come up with new ideas. Tags: sql learn sql UNIQUE DISTINCT 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. 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: IdBrandModelProductionYear AQ16AudiQ72011 FK19FordKuga2019 JC07JeepCherokee2012 JW15JeepWrangler2015 TS14TeslaModel S2014 TY20TeslaModel Y2020 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: RowIdFirstNameLastNameSSNNationality 1JohnBlack321456987123American 2IsabellaSanchez987456345123Spanish 3KlausSchmidt876456345123German 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… RowIdFirstNameLastNameSSNNationality 1JohnBlack321456987123American 2IsabellaSanchez987456345123Spanish 3KlausSchmidt987456345123German …and this table obeys the UNIQUE constraint set on the SSN column. RowIdFirstNameLastNameSSNNationality 1JohnBlack321456987123American 2IsabellaSanchez987456345123Spanish 3KlausSchmidt876456345123German 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. Good luck! Tags: sql learn sql UNIQUE DISTINCT You may also like What Is the Role of DISTINCT in SQL? The DISTINCT keyword in SQL plays an important role in removing duplicate values from your results. This article provides examples showing exactly how DISTINCT works. Read more LearnSQL Track of the Season: Creating Database Structure Data engineering is the future. See why it is worth learning in 2021 and why you should choose our SQL courses to do so. Read more What Is Data Engineering? What’s data engineering and how does it fit in with data science and Big Data? Read this article to find out. Read more Data Types in SQL SQL supports a lot of different data types – including some that don’t get used all that often. Take 5 minutes to learn about all the data types in SQL. Read more Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.