Calling SELECT DISTINCT on multiple columns

Question

I am unclear on exactly on,

SELECT DISTINCT col1, col2, ...
FROM table_name

When called on one column it gives back only distinct values of that column. What happens when we use it with multiple columns and should we ever do this?

• Erwin Brandstetter: See: postgresql.org/docs/current/static/sql-select.html#SQL-DISTINCT
• user1822: Unrelated, but: a common misconception that I see is that distinct (col1), col2 is something different than distinct col1, col2. The parentheses won't change anything

Answer 1

How does this work exactly?

It gives you distinct combinations of all the expression in the SELECT list.

SELECT DISTINCT col1, col2, ... 
FROM table_name ;

is also equivalent to:

SELECT col1, col2, ... 
FROM table_name 
GROUP BY col1, col2, ... ;

Another way to look at how it works - probably more accurate - is that it acts as the common bare SELECT (ALL) and then removes any duplicate rows. See Postgres documentation about SELECT: DISTINCT clause.

Should we ever do this?

Of course. If you need it, you can use it.