Which of the following keyword is used to eliminate duplicate records in SQL?

Question

Prepp Team · Accepted Answer

Understanding How to Eliminate Duplicate Records in SQL

In SQL databases, you often encounter tables with multiple rows. Sometimes, you might want to retrieve only the unique rows, meaning you want to eliminate duplicate records from the result set of a query. Duplicate records occur when two or more rows have identical values across all selected columns.

SQL provides specific keywords to handle this requirement. The goal is to filter the query output so that each distinct combination of values in the selected columns appears only once.

SQL Keyword for Eliminating Duplicate Records

Let's look at the options provided and determine which SQL keyword is used to eliminate duplicate records:

ELIMINATE
DUPLICATE
NODUPLICATE
DISTINCT

Standard SQL uses the DISTINCT keyword to achieve this. When you include DISTINCT in your SELECT statement, it tells the database engine to return only unique rows in the result set, effectively eliminating duplicate records.

Let's consider a simple example. Suppose you have a table called Customers with columns CustomerID and City, and you want to list all the unique cities where your customers are located.

Original Data (Customers Table):

CustomerID	City
101	New York
102	Los Angeles
103	New York
104	Chicago
105	Los Angeles

If you select the City column without using DISTINCT:

SELECT City FROM Customers;

Result:

City
New York
Los Angeles
New York
Chicago
Los Angeles

This result includes duplicate cities ('New York' appears twice, 'Los Angeles' appears twice).

Now, if you use the DISTINCT keyword:

SELECT DISTINCT City FROM Customers;

Result:

City
New York
Los Angeles
Chicago

This result set only contains the unique cities, successfully eliminating duplicate records.

The other options listed (ELIMINATE, DUPLICATE, NODUPLICATE) are not standard SQL keywords used for eliminating duplicate records in this manner.

Therefore, the correct keyword used to eliminate duplicate records in SQL is DISTINCT.

Revision Table: SQL Duplicate Elimination

Concept	SQL Keyword	Purpose	Example Use
Eliminating Duplicate Records	`DISTINCT`	To retrieve only unique combinations of values in the selected columns from a query result set.	`SELECT DISTINCT column1, column2 FROM table_name;`
Selecting All Records (including duplicates)	`ALL` (default behavior, often omitted)	To retrieve all rows from the table, including duplicate records.	`SELECT ALL column1 FROM table_name;` (Same as `SELECT column1 FROM table_name;`)

Additional Information on SQL Duplicates

While DISTINCT is the primary way to remove duplicate rows in a SELECT statement's result set, there are other concepts related to handling duplicates in SQL:

Identifying Duplicates: You can identify duplicate rows (based on certain columns) using techniques involving GROUP BY and HAVING clauses to count occurrences.
Deleting Duplicates: Removing duplicate rows permanently from a table requires different SQL statements, often involving subqueries, Common Table Expressions (CTEs), or joins, depending on the database system.
Primary Keys & Unique Constraints: To prevent duplicate records from being inserted into a table in the first place, you can define a PRIMARY KEY (which enforces uniqueness and non-nullability for one or more columns) or a UNIQUE constraint (which enforces uniqueness, allowing NULLs unless also specified as NOT NULL).
UNION vs. UNION ALL: The UNION operator combines the result sets of two or more SELECT statements and automatically removes duplicate rows. The UNION ALL operator combines result sets but includes all rows, including duplicates. This is different from SELECT DISTINCT, which works within a single SELECT statement.