PHP Tutorials

Important Link

Home » sql » Distinct

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.

File Name :

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

SELECT COUNT(DISTINCT Country) FROM ittutorial;

Itechtuto