MySQL Distinct Clause
In MySQL, the DISTINCT keyword in a SELECT statement is crucial for eliminating duplicate rows and ensuring data uniqueness. Whether applied to a single column or multiple columns, DISTINCT filters only unique values or combinations, enhancing data accuracy in analysis. This powerful tool evaluates row uniqueness after FROM, WHERE, and SELECT clauses, but before ORDER BY, making it indispensable for fetching distinct records from tables.
Syntax
The syntax for using the DISTINCT in MySQL is as follows:
Transform Your Career
Choose from our industry-leading programs designed for career success
Modern Software and AI Engineering Program
Master full-stack development with AI integration
+1000 moreModern Data Science and ML with specialisation in AI
Advanced data science techniques with AI specialization
+1000 moreAdvanced AIML with Specialisation in Agentic AI
Deep dive into AIML with focus on Agentic systems
+1000 moreDevOps, Cloud & AI Platform Engineering
Build and manage AI-powered cloud infrastructure
+1000 moreAI Engineering Advanced Certification by IIT-Roorkee
Premier AI engineering certification from IIT-Roorkee
Parameters
Here, column1, column2, ... represent the columns from which we want to retrieve distinct values, and table_name represents the name of the table from which we want to retrieve data. We can also use the asterisk (*) symbol to select all columns in a table.
When we execute distinct in MySQL will return only the unique values from the specified columns. If we specify multiple columns in the SELECT statement, MySQL will return only the unique combinations of those columns.
MySQL DISTINCT Clause with a single expression
Distinct in MySQL can also be used with a single expression to retrieve unique values from that expression in a table. This can be useful when we want to retrieve unique values from a single column or expression in a table.
The syntax for using DISTINCT with a single expression in MySQL is as follows:
Here, column_expression represents the expression from which we want to retrieve distinct values, and table_name represents the name of the table from which we want to retrieve data.
When we execute this query, MySQL will return only the unique values from the specified expression in the table.
DISTINCT in MySQL with a single expression in MySQL is a useful tool for retrieving unique values from a single column or expression in a table. By eliminating duplicate values, we can ensure that our data analysis is based on accurate information and avoid any confusion or inaccuracies that may arise from duplicate rows in a table.
Sure, let's take an example of a table called students with the following columns: id, name, age, gender, grade. To demonstrate a single expression, we can use the DISTINCT keyword to retrieve unique values from a single column. For example, to retrieve a list of unique grades from the student's table, we can use the following query:
This query will return a list of unique grades in the student's table, and the output might look something like this:
MySQL DISTINCT Clause with Multiple Expressions
Distinct in MySQL can also be used with multiple expressions to retrieve unique combinations of values from those expressions in a table. This can be useful when we want to retrieve unique combinations of values from multiple columns or expressions in a table.
The syntax for using DISTINCT with multiple expressions in MySQL is as follows:
Here, column_expression1, column_expression2, ... represent the expressions from which we want to retrieve distinct values, and table_name represents the name of the table from which we want to retrieve data.
When we execute distinct in MySQL will return only the unique combinations of values from the specified expressions in the table.
DISTINCT claus with multiple expressions in MySQL is a useful tool for retrieving unique combinations of values from multiple columns or expressions in a table. By eliminating duplicate values, we can ensure that our data analysis is based on accurate information and avoid any confusion or inaccuracies that may arise from duplicate rows in a table.
Let's consider the same example we saw in single expressions for multiple expressions. To demonstrate multiple expressions, we can use the DISTINCT keyword with multiple columns in the SELECT statement. For example, to retrieve a list of unique name and gender combinations from the student's table, we can use the following query:
This query will return a list of unique name and gender combinations in the student's table, and the output might look something like this:
Conclusion
Here are the key points to remember about the DISTINCT clause in MySQL:
- The DISTINCT clause is used to retrieve only distinct (unique) values from one or more columns in a table.
- It is used in a SELECT statement to eliminate duplicate rows and return unique values.
- The DISTINCT clause can be used with a single expression or multiple expressions to retrieve unique values from one or more columns or expressions in a table.
- When we execute a query with the DISTINCT clause, MySQL will return only the unique values or combinations of values from the specified columns or expressions in the table.
- The DISTINCT clause can have a performance impact on large tables, as it requires MySQL to perform additional operations to eliminate duplicates.
- In some cases, it may be more efficient to use other methods such as GROUP BY or subqueries to retrieve unique values.
Google
Amazon
Microsoft
Flipkart
Adobe1200+ more