Distinct Clause MySQL Essential Training

Distinct Clause MySQL Essential Training

MYSQL

Distinct Clause MySQL Essential Training




In this lesson you’ll learn about the MySQL DISTINCT clause which is used with select commands. The Distinct Clause MySQL Essential Training lesson’s main purpose is when querying data from a table, you may get duplicate rows. In order to remove these duplicate rows, you use the DISTINCT clause in the SELECT statement.
The following statement is gives you an syntax for the distinct clause with select statement.
SELECT DISTINCT columns FROM table_name WHERE where_conditions;
MySQL Distinct Example
Lets take a look at how the distinct is working with real time example.

Distinct Clause MySQL Essential Training
Some employees have the same designation like artist and etc. To remove the duplicate designation, you add the DISTINCT clause to the SELECT statement as follows:

Distinct Clause MySQL Essential Training




The duplicate last names are eliminated in the result set when we used the DISTINCT clause.
MySQL DISTINCT and NULL values
If a column has NULL values and you use the DISTINCT clause for that column, MySQL keeps one NULL value and eliminates the other because the DISTINCT clause treats all NULL values as the same value.
For example, in this Distinct Clause MySQL Essential Training lesson the emp_details table, we have many rows whose contact column has NULL values. When we use the DISTINCT clause to query the employees contact, we will see unique states and a NULL value as the following query:

Distinct Clause MySQL Essential Training
MySQL DISTINCT with multiple columns
We can use the DISTINCT clause with more than one column. In this case, MySQL uses the combination of all columns to determine the uniqueness of the row in the result set.
For example, to get the unique combination of city and state from the customers table, you use the following query:

Distinct Clause MySQL Essential Training
In MySQL we have another similar function which acts like a distinct clause is called group by clause. If we use the group by clause in the SELECT statement without using aggregate functions, the GROUP BY clause behaves like the DISTINCT clause.
The following statement uses the GROUP BY clause to select the unique designation of employees in the emp_details table.




Distinct Clause MySQL Essential Training
In Distinct Clause MySQL Essential Training We can achieve the same result by using the DISTINCT clause. Consider the following example,

Distinct Clause MySQL Essential Training
Whether we talk about the both distinct clause and group by clause is where the group by is sorting the result and the distinct won’t do that. In case we use the order by statement at the end of the distinct clause then we can get the same result as we get in the group by clause. Consider the following example,

Distinct Clause MySQL Essential Training
Distinct Clause MySQL Essential Training
MySQL DISTINCT and aggregate function
The Distinct Clause MySQL Essential Training lesson we learn about aggregate functions are perform the important role in the distinct clause. We can use these aggregate functions like SUM, AVG, COUNT etc, with distinct clause to remove duplicate rows before MySQL applies the aggregate function to the result set.
It will shows the count number of city in the address column. lets see how it can done with the MySQL.
MySQL DISTINCT with LIMIT clause
In MySQL some case we use the DISTINCT clause with the LIMIT clause, MySQL stops searching immediately when it finds the number of unique rows specified in the LIMIT clause.
Lets see how it can be achieved with real time example.
Distinct Clause MySQL Essential Training




We hope you have a clear idea about what is distinct clause and its functionalities with understanding examples.

Leave a Reply