DISTINCT as a clause or a function

Question

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 1

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.