DISTINCT vs GROUP BY

Although it may seem so, they do NOT do exactly same thing.

While GROUP BY groups results only by those columns that are explicitly listed after the clause, DISTINCT groups results by ALL columns that are present in SELECT statement (and not only by the one it precedes).

So following queries

SELECT DISTINCT article_id FROM article_authors
SELECT article_id FROM article_authors GROUP BY article_id
return same results,

but

SELECT DISTINCT article_id, author_id FROM article_authors
SELECT article_id FROM article_authors GROUP BY article_id
return different results,

though

SELECT DISTINCT article_id, author_id FROM article_authors
SELECT article_id FROM article_authors GROUP BY article_id, author_id
again return same results.

As a sidenote, with GROUP BY you haveĀ HAVING clause by your hand as well.

  1. by sobstel • August 2011 • blog archive