welcome welcome - 5 months ago 10
MySQL Question

DISTINCT as a clause or a function

I was doing some experiments with the

DISTINCT
keyword and some particular situations caught up my attention.

First of all I noticed that I can put some parenthesis with
DISTINCT
, for example:

SELECT DISTINCT(NAME) FROM EMPLOYEE;


is ok, while

SELECT DISTINCT(NAME, SURNAME) FROM EMPLOYEE;


gives me an error. Why?

And what's the sense of allowing operations like this one?

SELECT DISTINCT(NAME), COUNT(SURNAME) FROM EMPLOYEE;

Answer

DISTINCT (or DISTINCTROW) is not a function; it is an option of the SELECT statement that tells MySQL to strip duplicate rows from the resultset generated by the query. By default it returns all the generated rows, including the duplicates.

It can also be used together with COUNT() aggregate function (as COUNT(DISTINCT expr)) and it has the same meaning: it ignores the duplicates.

Because DISTINCT is not a function, DISTINCT(NAME) is interpreted as DISTINCT followed by the expression (NAME) (which is the same as NAME).

SELECT DISTINCT(NAME, SURNAME) FROM EMPLOYEE doesn't work because (NAME, SURNAME) is not a valid MySQL expression.