MySQL

In MySQL, the DISTINCT keyword is part of the Data Query Language (DQL) and is used to return only unique (distinct) values from a column or set of columns in a query. This is particularly useful when you want to eliminate duplicate data from your result set and only display unique records. By applying DISTINCT, you can get a clearer view of the distinct values in a dataset, helping in reporting, analysis, and decision-making.

Here’s an overview of how to use the DISTINCT keyword along with examples and useful tips for new students:

1. Basic Usage of DISTINCT:

   • The DISTINCT keyword eliminates duplicate rows from the result set of a query.
   • Syntax:

   SELECT DISTINCT department_id 
   FROM employees;

   • This query returns the distinct department IDs from the employees table, i.e., it will only show each department ID once, even if multiple employees belong to the same department.

2. Using DISTINCT on Multiple Columns:

   • You can apply DISTINCT to multiple columns to get unique combinations of values.
   • Example:

   SELECT DISTINCT department_id, job_title 
   FROM employees;

   • This query will return distinct pairs of department_id and job_title. A combination of the same department and job title will appear only once.

3. Combining DISTINCT with Other Clauses:

   • You can combine DISTINCT with other clauses like WHERE, ORDER BY, and LIMIT.
   • Example:

   SELECT DISTINCT branch_id 
   FROM employees 
   WHERE salary > 60000 
   ORDER BY branch_id;

   • This query returns distinct branch_id values from employees who earn more than 60,000, sorted in ascending order.

4. Counting Distinct Values:

   • You can use the COUNT() function with DISTINCT to count the number of unique values in a column.
   • Example:

   SELECT COUNT(DISTINCT department_id) 
   FROM employees;

   • This query returns the number of distinct departments in the employees table.

5. Performance Considerations:

   • Using DISTINCT can sometimes slow down a query if applied to large datasets, as the database engine has to remove duplicates. Be mindful of performance when using it in complex queries with large tables.

6. DISTINCT and NULL Values:

   • DISTINCT treats NULL values as equal, meaning if a column contains multiple NULL values, they will be treated as a single distinct value.
   • Example:

   SELECT DISTINCT manager_id 
   FROM employees;

   • If multiple rows have a NULLmanager_id, only one NULL will be returned.

These examples illustrate how the DISTINCT keyword can be a powerful tool for filtering out duplicate rows and obtaining unique values in your query results. It is often used in reporting, analytics, and when trying to understand the variety of values in specific columns of a table.

To gain complete access, login with gmail or outlook, no need of signup, click here

SELECT DISTINCT state
FROM org_client
ORDER BY state;

SELECT DISTINCT city
FROM org_client
ORDER BY city;

SELECT DISTINCT city, state
FROM org_client
ORDER BY city, state;

-- MySQL and Postgres
SELECT DISTINCT DATE(order_date)
FROM act_order
ORDER BY DATE(order_date);

-- Oracle and MS SQL Server
SELECT CAST(order_date AS DATE) FROM act_order ORDER BY CAST(order_date AS DATE);

SELECT DISTINCT product_type_id
FROM prd_product
ORDER BY product_type_id;

Example 1:

Let's understand the process of extracting data from a specified table using DISTINCT sql clause. In this case, we aim to retrieve distinct/unique department names from the employee table.

Example 1 - Raw data from employee table

Example 1 - Query

SELECT DISTINCT department FROM org_employee ORDER BY department;

Example 1 - Query data mapping

In the image above, the green color indicates the data that has been chosen or meets the criteria for our query.

Example 1 - Query Output

Example 2:

Let's understand the process of extracting multiple columns from a specified table using DISTINCT sql clause. Here, a row will be considered as distinct if all selected columns together form a unique combination.

Example 2 - Raw data from employee table

Example 2 - Query

SELECT DISTINCT department, designation FROM org_employee ORDER BY department, designation ;

Example 2 - Query data mapping

In the image above, the green color indicates the data that has been chosen or meets the criteria for our query.

Example 2 - Query Output

Example 3:

Let's understand the process of extracting order date using DISTINCT sql clause on timestamp column. Here we are trying to get distinct order dates.

Example 3 - Raw data from order table

Example 3 - Query

SELECT DISTINCT DATE(order_date) as order_date FROM act_order ORDER BY DATE(order_date);

Example 3 - Step 1 Extract date from order date and time column.

As step 1 first we have to extract date part alone from date and time.

Example 3 - Query data mapping

In the image above, the green color indicates the data that has been chosen or meets the criteria for our query.

Example 3 - Query Output