Could you please tell me what are the effects of using the DISTINCT keyword before a group of different column names in a SELECT statement? Recently, I created a query with DISTINCT in front of the first column in a list of three, and to my pleasant surprise, it had the effect of returning only results in which the three-column combination was unique, which is exactly what I wanted, but didn't expect to achieve. Does the DISTINCT keyword act on all columns included after it, or all combinations of them? Any wisdom you could provide would be greatly appreciated.
Yes, DISTINCT works on all combinations of column values for all columns in the SELECT clause.
It's a never-ending source of wonder for me how so many people (not you!) think that DISTINCT applies only to one column. I've seen queries like this:
SELECT DISTINCT(T1.id), T2.id, T2.amt FROM T1 JOIN T2 ON T2.T1id = T1.id
The intent here, of course, was to obtain only one row per
T1.id. However, the above query is exactly the same as this one:
SELECT DISTINCT T1.id, T2.id, T2.amt FROM T1 JOIN T2 ON T2.T1id = T1.id
When you realize that the relationship of T1 to T2 is one-to-many, it's obvious that this query will return more than one row for each
It's worth repeating: DISTINCT is not a function.
Dig Deeper on Oracle and SQL
Related Q&A from Rudy Limeback
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback. Continue Reading
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading