Do you think IIT Guwahati certified course can help you in your career?
Introduction
Hello, Ninja! We hope you all are doing well. Differentiating between unique and distinct commands could be a confusing part of SQL. Look through this post to understand this in the simplest possible terms.
Unique and Distinct are two somewhat similar terms in SQL, but they are still different. The article will first tell you about the concepts behind Unique and Distinct keywords and their differences.
The UNIQUE Keyword in SQL
The UNIQUE constraint guarantees that every value entered in a column is different from every other value. As the values cannot be repeated, the UNIQUE keyword in Sql prevents two entries from having the same values in the same column. You can use unique keywords with more than one column. Columns with the UNIQUE constraint can have NULL values.
Some of the major points on the UNIQUE constraint are listed below:
If two tuples have the same UNIQUE constraint, you will get an error message as the output for the query.
The ALTER TABLE statement can be used as a tool in SQL to add or remove a unique constraint from an existing table.
When you specify a UNIQUE constraint, a unique index is immediately built, which enables the database to effectively enforce the data's uniqueness.
Use the UNIQUE constraint whenever you wish to guarantee that the data in a certain column or collection of columns is unique. It is a strong tool for protecting data integrity in your database.
Example
CREATE TABLE students(
Student_Id INT UNIQUE,
Student_name VARCHAR (25),
Student_marks INT
);
insert INTO students VALUES(1, 'Harry', 95);
insert INTO students VALUES(2, 'John', 85);
insert INTO students VALUES(3, 'Larry', 63);
insert INTO students VALUES(4, 'Tom', 95);
insert INTO students VALUES(5, 'Angela', 63);
SELECT * from students
In the above example, a table will be created with three columns, and the UNIQUE keyword is given to the Student_id column, ensuring that every tuple inserted into the table will have a unique Student_id field. If two tuples have the same Student_id field, you will get an error message.
SQL Unique Constraint on ALTER Table
It is employed to change the characteristics of an existing unique constraint on a table, such as adding or removing columns from the constraint.
Syntax:
ALTER TABLE <table_name>
ADD UNIQUE (<column_name>);
DROP a Unique Constraint
The DROP statement with UNIQUE in SQL is used to remove a unique constraint from a table.
Syntax:
ALTER TABLE <table_name>
DROP CONSTRAINT <column_name>;
The DISTINCT Keyword in SQL
The DISTINCT is always used with the SELECT keyword. In some circumstances, you should get data out of the fields while simultaneously removing duplicate results from the output of your queries. The SELECT DISTINCT keyword can be used to retrieve unique field data from tables in conjunction with conditions, such as particular columns. Requesting unique values from fields can be done for a variety of reasons. Tasks like reporting and analysis can benefit significantly from using the SELECT DISTINCT query.
Some of the major points on the DISTINCT keyword are:
It is common practice to utilize the DISTINCT clause for operations like removing duplicates, counting unique values, grouping data, and generating lists of unique items.
The major advantage of the DISTINCT keyword includes saving time and effort, along with improvement in the meaningfulness and usefulness of the data.
DISTINCT keywords operate on a single column only hence the GROUP BY clause is required to eliminate duplicates from multiple columns.
If possible, the DISTINCT clause should be used in conjunction with indexes, without subqueries, and with small data sets.
Example
CREATE TABLE students(
Student_Id INT,
Student_name VARCHAR (25),
Student_marks INT
);
insert INTO students VALUES(1, 'Harry', 95);
insert INTO students VALUES(2, 'John', 85);
insert INTO students VALUES(3, 'Larry', 63);
insert INTO students VALUES(4, 'Tom', 95);
insert INTO students VALUES(5, 'Angela', 63);
SELECT DISTINCT Student_marks from students
In the above example, there is a repetition of 63 and 95 in the Student_marks column, but due to the use of the DISTINCT keyword, the query result has a unique Student_marks value.
Working of DISTINCT with Aggregates
Aggregates like COUNT, AVG, MAX, etc. can be utilized with the DISTINCT clause. Their implementation is shown in the following code examples.
DISTINCT COUNT Statement
The DISTINCT COUNT() clause returns the number of rows that have unique field data in the table. Its example is shown below:
CREATE TABLE students(
Student_Id INT,
Student_name VARCHAR (25),
Student_marks INT
);
insert INTO students VALUES(1, 'Harry', 95);
insert INTO students VALUES(2, 'John', 85);
insert INTO students VALUES(3, 'Larry', 63);
insert INTO students VALUES(4, 'Tom', 95);
insert INTO students VALUES(5, 'Angela', 63);
SELECT COUNT(DISTINCT Student_marks ) from students
Output:
3
AVG DISTINCT Statement
The AVG DISTINCT () function returns the average value of a column of numeric data.
The given example describes its usage:
CREATE TABLE students(
Student_Id INT,
Student_name VARCHAR (25),
Student_marks INT
);
insert INTO students VALUES(1, 'Harry', 95);
insert INTO students VALUES(2, 'John', 85);
insert INTO students VALUES(3, 'Larry', 63);
insert INTO students VALUES(4, 'Tom', 95);
insert INTO students VALUES(5, 'Angela', 63);
SELECT AVG(DISTINCT Student_marks ) from students
Output:
81.0
MAX DISTINCT Statement
The MAX DISTINCT function returns the greatest value of the chosen column. Its usage is shown by the following example:
CREATE TABLE students(
Student_Id INT,
Student_name VARCHAR (25),
Student_marks INT
);
insert INTO students VALUES(1, 'Harry', 95);
insert INTO students VALUES(2, 'John', 85);
insert INTO students VALUES(3, 'Larry', 63);
insert INTO students VALUES(4, 'Tom', 95);
insert INTO students VALUES(5, 'Angela', 63);
SELECT MAX(DISTINCT Student_marks ) from students
The UNIQUE keyword in SQL prevents two records from having identical values in a column.
The DISTINCT keyword in SQL helps to eliminate duplicate values while retrieving the data.
2
Use the UNIQUE keyword if you're ever in a situation where you want to treat two NULL values differently from one another.
Use the DISTINCT keyword to treat two NULL values differently in some instances but the same way in others.
3
It allows one or more fields or columns of a table to identify a record in a database table uniquely.
It is used with SELECT and helps to return different values in the result set.
4
A UNIQUE constraint can be used as a primary key.
DISTINCT only affects the result set of a query.
Frequently Asked Questions
What is SQL?
SQL is a programming language that is used for managing and accessing data stored in relational databases. It provides the necessary commands to define the database structure, change the database data, provide security restrictions, and carry out several other operations.
What are the types of SQL commands?
There are five types of SQL commands; Data definition language(DDL), Data Manipulation Language(DML), Data Control Language (DCL), Transaction control language(TCL), and Data Query Language(DQL).
Which keyword in MySQL is used to rename a column name?
In SQL, you can rename the tables' columns already created and present in the database. For this purpose, the ALTER TABLE and RENAME keywords are used to rename a column in MYSQL.
Conclusion
This blog examines the fundamental ideas of UNIQUE and DISTINCT keywords, the distinctions between them, and some frequently asked issues in the field. The site has effectively defined the difference between UNIQUE and DISTINCT, which can be confusing.
We hope you liked the article and gained profound insights into this topic. You can further refer to the Distinct keyword blog for more understanding of the DISTINCT keyword.
84+ registered