What is DISTINCT in mysql?

DISTINCT is used to remove duplicates rows from table. DISTINCT fetch only the unique records. The DISTINCT is used with SELECT statements.

When only one column is provided in the DISTINCT clause, the query will return the unique values for that expression.

When more than one column is provided in the DISTINCT clause, the query will retrieve unique combinations for the expressions listed.

DISTINCT clause doesn't ignore NULL values. your result will include NULL as a distinct value.

Syntax :-

SELECT DISTINCT list_of_column_name FROM table_name
WHERE search_condition ORDER BY expression;

With Single Expression

if you specify single column in your distinct query then it return unique rows based on the condition of that column. and it removes the duplicates from the result set.

SELECT DISTINCT NAME FROM ITTUTORIAL;

Example

SELECT DISTINCT city FROM state where state = 'bihar'
SELECT DISTINCT(city) FROM state where state = 'bihar'

Example - With Multiple column

We can also combine 2 or multiple columns to get distinct values. it return unique record, department and salary basis.

SELECT distinct Department, Salary FROM employee;

Example - With Multiple Expressions

SELECT DISTINCT city, state FROM customers;

Example

SELECT DISTINCT fnane FROM itechxpert ORDER BY fname;

DISTINCT and NULL values

When you specify a column that has NULL values in the DISTINCT clause, the DISTINCT clause will keep only one NULL value because it considers all NULL values are the same.

MySQL DISTINCT with multiple columns

SELECT DISTINCT state, city FROM customers WHERE state IS NOT NULL ORDER BY state, city;

output :-

state | city |
+---------------+----------------+
| BC | Tsawassen |
| BC | Vancouver |
| CA | Brisbane |
| CA | Burbank |
| CA | Burlingame |
| CA | Glendale |
| CA | Los Angeles |
| CA | Pasadena |
| CA | San Diego

Example

SELECT COUNT(DISTINCT Country) FROM ittutorial;

Previous Next

Review & Rating

0.0 / 5

0 Review

5

(0)

4

(0)

3

(0)

2

(0)

1

(0)