Learn how to select only distinct (unique) values from the database tables using the SQL Select Distinct Statement. It fetches duplicate rows as one.
Use of Select Distinct Statement
Sometimes database tables may include entire duplicate rows or some columns containing duplication values in rows. In the case, our requirement is only to fetch the unique rows or only unique column values, you can make use of the SELECT DISTINCT Statement.
SQL Select Distinct Statement Syntax
The syntax is the same as the SELECT statement with just the addition of a DISTINCT keyword.
SELECT DISTINCT col_1, col_2, ...
The query is written as per the above syntax will return all the rows of the database but if a row is entirely the duplicate of another, it will return it only once. You can also use the DISTINCT statement with
* symbol as to select all unique rows with all columns.
SELECT DISTINCT * FROM table_name;
Again, you can any other SQL statements like WHERE condition with this query as well. You can also choose to select particular DISTINCT column values as well.
SELECT DISTINCT col_1 FROM table_name;
SELECT DISTINCT col_2 FROM table_name;
Both of the above two queries may give different numbers of rows count in the result set, depending of the duplicate values present in each of the mentioned columns of the table in the query.
Select Distinct Example
In our demo database names as
school, we’ll run the SELECT DISTINCT query on the
employees table. Before you see the DISTINCT query results, have a look at the following screenshot showing all the table columns and rows.
It contains four duplicate rows. None of the rows are exactly duplicate of another due to the reason that ID is the primary key here which makes every row unique in itself.
Row with ID numbers 3 and 6 have all other columns identical except the column last_name. Row with ID numbers 4 and 7 have all the columns identical except ID.
The following query has a distinct keyword in front of the column name first_name, so it will now fetch all the rows having unique first_name column values.
SELECT DISTINCT first_name, last_name, email FROM employees
Now, we’ll fetch single DISTINCT column rows. In the first query, we’ll fetch only the first_name column and in the next query, we’ll only fetch the last_name column.
SELECT DISTINCT first_name FROM employees
You can clearly see, out of the total 6 rows as two were duplicate with first_names, Manmeet, and Simrandeep, only 4 rows are fetched in the DISTINCT column selection query.
SELECT DISTINCT last_name FROM employees
You can see the screenshot of the result set of the second query, there are only three rows fetched for the DISTINCT column rows selection for last_name.
Therefore, the DISTINCT keyword applied on different column checks for the unique value on the applied columns isolatedly, irrespective of whether the other columns for the same row may or may not contain unique values. I hope, all of you are cleared about my idea of explaining to you about the DISTINCT keyword property by running it on two different columns.
At any instance of time whenever you wanted to know about how many unique values are contained in an entire table column, you can further write the DISTINCT keyword with the column name in the SQL COUNT function. The syntax for the same is written below, followed by an example.
SELECT COUNT(DISTINCT col_name) FROM table_name
SELECT COUNT(DISTINCT last_name) FROM employees
I hope you find this guide useful. If so, do share it with others who are willing to learn SQL. If you have any questions related to this article, feel free to ask them in the comments section.
Helpful Associated Guide.
Also, don’t forget to Subscribe to WTMatter!