The DISTINCT statement allows the repetitive data in certain columns of the table to be retrieved as one.

“But what good does that do to us?” The question may come to our mind, for example, we may have more than one department in our company and more than one staff in each department.

Now, if you wish, let’s continue with a small example:

Company Table

Example query:

SELECT DISTINCT position FROM company where department_no = 12

Output:

Company positions

As we can see in the result above, it brought the staff of the personnel in the 12th department. In doing so, it brought only 1 record from each position.

Note: The word DISTINCT is automatically applied to the fields that will be written after it. In short; If DISTINCT is to be used on more than one field, there is no need to write DISTINCT before each field.

Differences Between DISTINCT and GROUP BY

DISTINCT and GROUP BY functions produce exactly the same or very close values. However, in cases where query performance is important, the usage areas of both are different.

  • SQL DISTINCT: It equals the repeated data. In other words, it would be a problem to bring the same data many times in a large data, it is worth using DISTINCT for this. Only 1 of the duplicate data is fetched.

  • SQL GROUP BY: It is used to group on its name. Repeated data is brought by grouping.

If you are going to operate with Aggregate SQL Functions such as SUM, MAX, MIN, COUNT, AVG, it will be more useful to use GROUP BY.

Moreover:

The DISTINCT Command is written next to the word SELECT. For instance:

SELECT DISTINCT position FROM company

GROUP BY command is to be written using WHERE, it is used after the condition is written. For instance:

SELECT * FROM company where department_no = 12 GROUP BY position

If the condition is not used, it can be used after FROM.

SELECT * FROM company GROUP BY position