SQL distinct: description, examples, properties

Often when using SQL for samplinginformation from the tables, the user receives redundant data, consisting of the presence of absolutely identical duplicate lines. To exclude this situation, use the SQL distinct argument in the Select clause. In this article, we will consider examples of using this argument, as well as situations in which it is better to abandon the argument.

Before we begin to consider specific examples, we will create a couple of necessary tables in the database.

Preparing tables

Imagine that we have stored in our databaseinformation about the wallpaper, presented in two tables. This is the Oboi table (wallpaper) with the fields id (unique identifier), type (type of wallpaper - paper, vinyl, etc.), color, struct and price. And the Ostatki table (leftovers) with the fields id_oboi (reference to the unique identifier in the Oboi table) and count (the number of rolls in the warehouse).

Fill in the tables with data. In the table with wallpaper add 9 entries:

In the table with the remainders there are also nine records:

Let's start by describing how to use distinct in SQL.

Place distinct in the Select clause

The distinct argument should be placed immediately afterthe Select keyword in the queries. It is applied immediately to all the columns specified in the Select clause because it will exclude absolutely identical rows from the result of the query. Thus, it is sufficient to specify "select distinct" when writing the SQL query. The only exception is the use of distinct inside the aggregate functions, which we will consider a little later.

It should be remembered that most DBMS does not recognize your request like this:

Here, the argument is specified several times or specified once, but before the second, third or other selectable column. You will get an error with a reference to inaccuracies in the syntax.

Using distinct in standard queries

Obviously, with a well-structured structuretables and their filling, within a single table, situations are excluded when absolutely identical strings are encountered. Therefore, the execution of the "Select distinct *" query with a selection from one table is practically impractical.

Imagine a situation where we need to find out what type of wallpaper we have, just for convenience, sort by type:

And we get the result:

As you can see, there are duplicate rows in the table. If we add to the Select distinct clause:

then we get the result without repeating:

Thus, if the data were correctly entered intable, then immediately after the call or the request of buyers, we will be able to answer that the liquid wallpaper, wallpaper and acrylic wallpaper is not available in the store. Given that the assortment in stores is usually not limited to one hundred wallpaper, it would be quite labor-intensive to scan the list of non-unique types.

Using distinct within aggregate functions

The SQL distinct argument can be used with anyaggregate function. But for Min and Max, its use will not have any effect, and when calculating the sum or average value, it is rarely possible to imagine a situation where it would not be necessary to take into account the repetitions.

Let's say we want to know how much our warehouse is full, and for this we send a request that calculates the total number of rolls in the warehouse:

The query will return a response 143. If we change to:

then we get only 119, because the wallpaper under articles 3 and 7 are in stock in the same quantity. However, it is obvious that this answer is incorrect.

Most often, SQL is used with the Count function. So, without difficulty we can find out how many unique types of wallpaper we generally have:

And get the result 5 - paper ordinary andtwo-layer, vinyl, fabric and non-woven. Surely everyone saw an advertisement like: "Only we have more than 20 kinds of different wallpapers!", Which means that in this store there are not a couple of dozen rolls of everything, but wallpaper of the most diverse modern types.

Interestingly, in one query, you can specifymultiple Count functions with or without the distinct attribute. That is, this is the only situation where distinct in Select can not be present more than once.

When should I reject the use of an argument

The use of the SQL distinct argument should be discarded in one of two ways:

1. You select from tables and are surethe uniqueness of the values ​​in each. In this case, the use of the argument is inexpedient, because this is an additional load on the server or client (depending on the type of DBMS).
2. You are afraid of losing the necessary data. We will explain.

Suppose the boss asks you to display a list of wallpaper that you have, indicating only two columns - type and color. By habit, you specify the argument distinct:

And - lose some of the data:

It may appear that we have only one type of paper wallpaper (conventional and two-layered), although in fact even in our small table they have two articles (the result without distinct):

Therefore, as in writing any query, with the distinct argument one must be careful and correctly solve the problem with its application depending on the task in hand.

Alternative distinct

The opposite of the argument distinct is the argumentAll. When you use it, duplicate lines are saved. But since by default the DBMS thinks that it is necessary to print all the values, the All argument is more of a specifier than a real functional argument.