In this SQL Tutorial, you’ll learn- What is SELECT DISTINCT in SQL? and How to use SQL SELECT DISTINCT command? We will learn in detail about SQL DISTINCT command with 3 in-depth examples. So, let’s start the Learnings –
SQL SELECT DISTINCT
The SQL SELECT DISTINCT statements are basically the combination of two different clauses- SELECT and DISTINCT. Both of these SQL commands are used for different purposes-
1) SQL SELECT command : You can use SELECT command to fetch (or select) multiple records (or data) from the tables.
2) SQL DISTINCT command : DISTINCT commands are used to get only distinct or unique values from the specified column list.
In order to use SQL DISTINCT command, you must know the syntax of it, so let’s see it first –
The generic syntax of SQL DISTINCT statement is like –
SELECT DISTINCT your_column_name FROM your_table_name;
Example 1: SQL SELECT DISTINCT on One column
Sometimes, you may want to get unique records and data from multiple columns of a table, and you can easily do with using a SQL DISTINCT statement. Below is a diagram by Wikipedia that explains- How SQL SELECT statement gets the DISTINCT columns “C1” and “C2” from a table “T” –
Don’t worry, if you did not understand this diagram completely.
Let’s create a sample database and table, insert some records and practically use the DISTINCT command on multiple columns to understand the whole concept better. Here’re the 4 Simple steps of doing it –
Step 1) Create a Database & Table for practice,
Step 2) Insert some records,
Step 3) Prepare SQL Query using the DISTINCT command,
Step 4) Run the query and Verify the Resultset.
Btw, Step 1 and 2 can be optional if you already have some database and table for practice purpose. I’ve just listed them here for saving some of your time in doing setup and all.
To set up the Database schema and records, you can just download and run this setup-database.sql file from my Google drive or Copy/Paste the below SQL Queries directly in your SQL terminal.
1) Creating DB and Tables
CREATE DATABASE db_tbl; USE db_tbl; CREATE TABLE tbl_movies ( movie_id INT PRIMARY KEY AUTO_INCREMENT, movie_name VARCHAR (255) NOT NULL, price NUMERIC (5, 2) DEFAULT 0, start_date DATE NOT NULL, end_date DATE NOT NULL ); ALTER TABLE tbl_movies AUTO_INCREMENT = 101;
2) Inserting some Records
INSERT INTO tbl_movies ( movie_name, price, start_date, end_date ) VALUES ( -- First record 'Steve Jobs: 14 Years in Apple', 25.50, '20200422', '20201126' ), ( -- Second record 'ShubhamKLogic.com: A Tech Biography', 30.00, '20210101', '20310101' ), ( -- And a few more... 'Dangal', 28.50, '20180201', '20181201' ), ( 'Avengers: End of Game', 28.00, '20190123', '20190422' ), ( 'Friends', 28.50, '20190123', '20190422' ), ( 'The Family Man', 28.00, '20190123', '20190422' ), ( 'Life of Pie', 28.50, '20160901', '20170101' );
3) Preparing Query using SQL SELECT DISTINCT statement
So far, we have designed and created a Database, Table and inserted some Records in it, that gives us below simple results –
Assume, we need the list of the PRICE of movies that are less than $30.00, so you’ll need to write a SQL query similar to this –
SELECT PRICE as MOVIES_PRICE FROM TBL_MOVIES WHERE PRICE < 30.00;
Query Results: Once you’ll run the above SQL query, you’ll get the following results.
As you can see, our query executed successfully, and we got 6 records of movies price in the resultset. But, if you look carefully, the MOVIES_PRICE column is having duplicate values (28.50 and 28.00) and we don’t need those duplicates. So, what to do?
Solution: Use SQL DISTINCT command.
We can make use of DISTINCT command of SQL to filter out all the duplicate records. Here is a simple query of doing it –
SELECT DISTINCT PRICE FROM TBL_MOVIES WHERE PRICE < 30.00;
In the above Query, we’ve used the DISTINCT keyword on one column of the table i.e. PRICE, because, inside the table TBL_MOVIES, the column PRICE is containing many duplicate values, but the SELECT query removed all the records having duplicate values of PRICE. This is how we can use the DISTINCT keyword.
4) Verifying the Resultset
Once, you’ll execute the above query, you’ll get the below results:
+--------+ | PRICE | +--------+ | 25.50 | | 28.50 | | 28.00 | +--------+ 3 rows in set (0.00 sec)
Example 2: SQL SELECT DISTINCT Multiple Columns
In the above example, we have learned How to use the SQL DISTINCT statement on a single column, but You can also use the SQL DISTINCT on multiple columns of a table. In this scenario, here is how we’ll prepare the query of SQL SELECT unique values from Multiple columns –
SELECT DISTINCT PRICE AS MOVIE_PRICE, MOVIE_NAME FROM TBL_MOVIES WHERE PRICE < 30.00;
Output: Once you’ll run the above query, you’ll successfully get the following Resultset –
Now, if you’ll check the values of MOVIE_PRICE column, then you’ll find that some of the values like 28.50 are repeated 3 times. Is this a correct SQL query?
Technically speaking, The reason of duplicate values in MOVIE_PRICE column is, when you specify multiple columns after the DISTINCT command, it checks the duplicacy by considering the combination of values of all the cells of a record.
In simple words, you’ll not find two rows that have same values in MOVIE_PRICE and NAME. Any of the value would definitely be different. Although the value of a particular column gets match, yet at least one cell must be unique in the remaining column list for the same two rows.
3. Using SQL SELECT with DISTINCT for Counting
Another important use of SQL DISTINCT statement is to count the number of records on the basis of some unique column values. Here is a query of SQL to SELECT & count DISTINCT records of Movies having Price greater than $28.00 –
SELECT DISTINCT Count(*) FROM TBL_MOVIES WHERE PRICE > 28.00;
Output: Once you’ll run the above SQL query, you’ll successfully get the below output:
+-------------+ | COUNT(*) | +-------------+ | 4 | +-------------+ 1 row in set (0.06 sec)
What We Learned?
In this SQL tutorial, We have learned What is select distinct in SQL? and How to select two distinct columns in SQL? Also, we have seen how to count fetched records using DISTINCT command in SQL.
I hope you found this article helpful and improved your knowledge in SQL Programming. If you have any doubt or queries, Feel free to comment below or submit your question via Google, I’ll definitely try to give you an answer within a day.
And, Don’t forget to check other Popular related SQL Articles that you may like:
- Do you really know? How to insert multiple rows in a Table? (Tutorial)
- Practice SQL Query Interview Questions – For Beginners (Interview Related)
- Master SQL Query Interview Questions – For Intermediate (Interview Related)
- Top mistakes that every Modern programmer does (Guide)
- Complete List of SQL Commands (Index Page)