Learn about the SELECT DISTINCT command in SQL

Learn about the SELECT DISTINCT command in SQL Picture 1

SELECT DISTINCT is a basic but extremely useful command to remove duplicate records in a data table. This article from TipsMake will guide you on how to use and practical applications of the SELECT DISTINCT command in SQL.

Function of SELECT DISTINCT command in SQL

Remove duplicate records 

When using DISTINCT, SQL scans through the records and returns only the values ​​that are different. If a column contains multiple identical values, only a single value will be displayed in the results.

Use with multiple columns

The SELECT DISTINCT statement can be applied to multiple columns at once. In this case, SQL will return unique combinations of values ​​in the specified columns.

Syntax

The basic syntax of this command is:

SELECT DISTINCT column1, column2, .

FROM table_name;

In which, column1, column2, . are the columns you want to query and table_name is the name of the table containing the data.

Handling NULL values

If the query column contains a NULL value, the DISTINCT command returns only a single NULL value, treating all NULL values ​​as the same.

Syntax of the SELECT DISTINCT command

The syntax of the SELECT DISTINCT command is quite simple and easy to understand. You just need to master the basic structure to be able to apply it to your queries.

Basic structure

The syntax of the SELECT DISTINCT command is as follows:

SELECT DISTINCT column1, column2, . FROM table_name WHERE condition;

In there:

1. column1, column2, . are the columns from which you want to get unique values.
2. table_name is the name of the data table.
3. condition is the data filtering condition (if needed).
4. SELECT DISTINCT: The keyword specifies that you want to query for unique values.
5. column1, column2, .: Are the columns that you want to get unique values ​​from.
6. table_name: The name of the table containing the data you want to query.
7. WHERE condition: (Optional): Filter condition to determine which data will be displayed.

Syntax

To better illustrate the syntax, the following example will best demonstrate the syntax of the command: Suppose you have a KHACH_HANG table with columns such as MA_KH, TEN, SODIENTHOAI. If you want to get a list of different customer phone numbers, you can write the command as follows:

SELECT DISTINCT SODIENTHOAI FROM KHACH_HANG;

This command will return a list of unique phone numbers without any duplicates.

Learn about the SELECT DISTINCT command in SQL Picture 2

How to use SELECT DISTINCT command in SQL

Some examples of SELECT DISTINCT command in SQL

To help you better understand how to use SELECT DISTINCT, we will look at some specific examples.

Example 1: Get unique values ​​from a column

You have a customer list table with columns customer_id, customer_name, and address. To get a list of unique cities that customers come from, you can use the following statement:

SELECT DISTINCT city

FROM customers;

Example 2: Get unique values ​​from multiple columns

If you want to get a list of unique value pairs from two columns, such as city and customer_name, you can do the following:

SELECT DISTINCT city, customer_name

FROM customers;

Example 3: Combined with WHERE condition

You can also combine SELECT DISTINCT with a WHERE condition to filter the results. For example, if you want to get only the unique cities from the customers table that have customers whose names start with the letter 'A', you can write:

SELECT DISTINCT city

FROM customers

WHERE customer_name LIKE 'A%';

When to use SELECT command without DISTINCT

Although SELECT DISTINCT is very useful, there are cases where you don't need to use it. Instead, you can use a regular SELECT statement to retrieve data.

Data aggregation

One of the typical cases where you don't need to use SELECT DISTINCT is when you want to aggregate data.

If you are using functions like SUM(), AVG(), COUNT(), you may not need DISTINCT as these functions automatically handle the data in their own way. For example:

SELECT COUNT(*) FROM state_name;

When all data is needed

If you need all the records without removing duplicates, you can use the regular SELECT command. When you want to see all the records in a table without removing duplicates. For example:

SELECT * FROM table_name;

Detailed data analysis

In some cases, you may want to analyze the data in more detail without removing duplicates. For example, if you want to see all invoices for each customer, you can use the following command:

SELECT MA_KH, MA_HD, TOTAL_TIEN FROM HOADON ORDER BY MA_KH;

This statement will return all invoices for each customer without removing any duplicates.

When there are no duplicate records

If you know for sure that the table does not contain duplicate records, using DISTINCT is unnecessary and can reduce the performance of the query.

Conclude

The SELECT DISTINCT command is a useful tool for querying unique values ​​in SQL. It helps eliminate duplicates and provides you with a clear and simple list of values. Understanding how to use SELECT DISTINCT will help you exploit your database effectively and gather accurate, useful information for analysis and decision making purposes.

Lesley Montoya

Update 12 February 2025