What is the Difference Between Unique and Distinct in SQL

The main difference between Unique and Distinct in SQL is that Unique helps to ensure that all the values in a column are different while Distinct helps to remove all the duplicate records when retrieving the records from a table.

Generally, SQL or Structure Query Language is the language that allows performing various operations on the data stored in the databases of the RDBMS. Furthermore, there are various commands, and constraints in SQL and the programmer can easily write the queries and execute them to perform insert, update, delete etc. Unique and Distinct are two of them which allows writing SQL queries.

Key Areas Covered

1. What is Unique in SQL
      -Definition, Functionality
2. What is Distinct in SQL
      -Definition, Functionality
3. Difference Between Unique and Distinct in SQL
      -Comparison of key differences

Key Terms

Distinct, Primary Key, SQL, Unique Key

Difference Between Unique and Distinct in SQL - Comparison Summary

What is Unique in SQL

Unique is a command or a constraint in SQL that ensures that all values in a column are different. Moreover, both unique and primary key provides a guarantee for the uniqueness of a column or a set of columns. Generally, the primary key already has the unique constraint. It is possible to have multiple unique constraints per table, but there can only be one primary key per table.

Difference Between Unique and Distinct in SQL

An example of creating a table in MySQL with Unique is as follows.

CREATE TABLE student(

id int not null.

name varchar(255),

int age,

UNIQUE (ID) );

Further, it is also possible to use unique constraint on an existing table. It will add a modification to the table. Below example puts unique constraint on the id column of the existing student table. Therefore, it is used with ALTER.

ALTER TABLE student

ADD UNIQUE (id);

What is Distinct in SQL

Distinct is an SQL command that helps to return only the distinct values. A column in a table can have the same value multiple times. In other words, it can contain duplicate values. Sometimes, the programmer may need to remove those duplicate values and to obtain distinct values available in the database. In that case, he can use Distinct.

SELECT DISTINCT age FROM students;

Moreover, the below statement will count different ages of the student.

SELECT COUNT (DISTINCT age) FROM Student;

Difference Between Unique and Distinct in SQL

Definition

Unique is a constraint in SQL that allows one or more fields or columns of a table to uniquely identify a record in a database table. But, Distinct is command used with ‘select’ that helps to return distinct or different values in the result set. Thus, this explains the main difference between Unique and Distinct in SQL.

Usage

Furthermore, unique is a constraint that prevents two records from having identical values in a column, while distinct helps to remove duplicate values when retrieving data.

Conclusion

In brief, SQL is a language that allows performing operations on data stored in the database. Unique and Distinct are two SQL  constraints. The main difference between Unique and Distinct in SQL is that Unique helps to ensure that all the values in a column are different while Distinct helps to remove all the duplicate records when retrieving the records from a table.

References:

1.SQL UNIQUE Constraint, Available here.
2.SQL SELECT DISTINCT Statement, Available here.
3.“DISTINCT Keyword.” Studytonight, Available here.

Image Courtesy:

1.”1954920″ (CC0) via Pixabay

About the Author: Lithmee

Lithmee holds a Bachelor of Science degree in Computer Systems Engineering and is reading for her Master’s degree in Computer Science. She is passionate about sharing her knowldge in the areas of programming, data science, and computer systems.

Leave a Reply