Where do we use SELECT DISTINCT COMMAND?
The "SELECT DISTINCT" command is used in SQL (Structured Query Language) to retrieve unique values from a specific column or columns in a database table. It is commonly used in queries when you want to eliminate duplicate values and retrieve only distinct or unique values.
.png "SELECT DISTINCT")
Here are a few scenarios where you might use the "SELECT DISTINCT" command:
- Eliminating duplicate entries: If a table contains multiple rows with the same values in a particular column, using "SELECT DISTINCT" allows you to retrieve only the unique values from that column.
- Filtering unique values: When you have a large dataset and want to filter out duplicate values in a specific column, "SELECT DISTINCT" can help you identify the unique values.
- Aggregating unique values: In combination with aggregate functions like COUNT, SUM, AVG, etc., "SELECT DISTINCT" can be used to calculate aggregate values based on unique values in a column.
- Joining tables: When joining multiple tables, "SELECT DISTINCT" can be useful to retrieve unique combinations of columns across the joined tables. This helps prevent duplicate rows in the result set.
- Where do we use SELECT DISTINCT COMMAND?
- DISTINCT keyword on single column
- DISTINCT keyword on multiple columns
- DISTINCT keyword with COUNT() function
- Conclusion
ALSO READ👉
DISTINCT keyword on single column
Here's an example of how to use the "DISTINCT" keyword in a SQL query:
Syntax
| SELECT DISTINCT column_name
FROM table_name; |
|---|
In the above query, you need to replace "column_name" with the actual name of the column from which you want to retrieve distinct values, and "table_name" with the name of the table you are querying.
Let's consider a table called "students" with the following structure:
Example
| CREATE TABLE students (
id INT PRIMARY KEY, name VARCHAR(50), course VARCHAR(50), age INT ); |
|---|
Now, let's assume the "students" table has the following data
| ID | Name | Course | Age |
|---|---|---|---|
| 1 | Priya | Biology | 19 |
| 2 | Rahul | Physics | 21 |
| 3 | Ananya | Chemistry | 20 |
| 4 | Siddharth | Biology | 19 |
| 5 | Nisha | Mathematics | 22 |
| 6 | Arjun | Physics | 20 |
| 7 | Aishwarya | Chemistry | 19 |
To retrieve the distinct courses from the "students" table, you can use the following query:
Example
| SELECT DISTINCT course
FROM students; |
|---|
The result of this query would be:
| Subject |
|---|
| Biology |
| Physics |
| Chemistry |
| Mathematics |
DISTINCT keyword on multiple columns
The DISTINCT keyword can be used on multiple columns to retrieve unique combinations of values. Here's an example using the "students" table:
To retrieve distinct combinations of name and course from the "students" table, you can use the following query:
Example
| SELECT DISTINCT name, course
FROM students; |
|---|
The result of this query would be:
| Name | Course |
|---|---|
| Priya | Biology |
| Rahul | Physics |
| Ananya | Chemistry |
| Siddharth | Biology |
| Nisha | Mathematics |
| Arjun | Physics |
| Aishwarya | Chemistry |
In this result, you can see that each row represents a unique combination of "name" and "course". The DISTINCT keyword ensures that only distinct combinations are returned, eliminating any duplicate rows based on both the "name" and "course" columns.
Using DISTINCT with multiple columns allows you to retrieve unique combinations of values and is useful when you want to identify and work with distinct sets of records based on multiple criteria.
DISTINCT keyword with COUNT() function
You can use the DISTINCT keyword in combination with the COUNT function to get the count of distinct values in a column
Following is the syntax for using the DISTINCT keyword with COUNT() function:
Syntax
| SELECT COUNT(DISTINCT column_name) FROM table_name WHERE condition; |
|---|
Where, column_name is the name of the column for which we want to count the unique values and table_name is the name of the table that contains the data.
Example
To get the count of distinct departments in the given table, you can use the following query:
Example
| SELECT COUNT(DISTINCT department) AS distinct_department_count
FROM employees; |
|---|
The result of this query would be:
| distinct_department_count |
|---|
| 3 |
In this example, the DISTINCT keyword is applied to the "department" column, and the COUNT function counts the number of distinct departments in the "employees" table. The alias "distinct_department_count" is used to give a meaningful name to the result column.
Distinct count on multiple column
If you want to count distinct combinations of values across multiple columns, you can achieve this using a subquery.
SELECT COUNT(*) AS distinct_count
FROM (
SELECT DISTINCT column1, column2
FROM your_table
) AS distinct_values;
This query first creates a temporary table with distinct combinations of values from column1 and column2, and then counts the rows in that temporary table to get the distinct count. Replace column1 and column2 with your actual column names, and your_table with your table's name.
Conclusion:
The DISTINCT keyword in SQL is used to retrieve unique values from one or more columns in a table. It eliminates duplicate rows and returns only distinct values. It can be combined with other SQL functions and clauses to perform calculations and filter data based on specific conditions. The order of the results is not guaranteed, as the focus is on uniqueness rather than a specific order.
0 Comments
Post a Comment