SQL stands for Structured Query Language It is a domain-specific programming language used to access and manipulate data stored in a relational database. Using SQL, programmers can create new relational databases, insert records into existing databases, delete records from existing databases, update records in existing databases, and retrieve specific records from existing databases.
SQL (Structured Query Language) also plays an essential role in backend web development, where it can be used to manage the data of a website stored in a database. This database can include user data like user information or website data like products available on the website.
SQL allows programmers to use a variety of queries to manipulate data stored in the various tables of a database. Following the semantics and rules of SQL, programmers can create a variety of queries using different predefined keywords to manipulate the data stored in their databases. DISTINCT and TOP are keywords predefined in SQL that can be used in a query to select specific records.
DISTINCT Keyword in SQL
The DISTINCT keyword is used to filter out any distinct (all entries except repetitive) rows in the table of a database. It can be used with a SELECT keyword which returns all the unique records stored in the table specified by the programmer.
Syntax
SELECT DISTINCT col1, col2, … FROM table;Return value
Returns a temporary table including all the unique records stored in the database.
Example
Suppose a database containing usernames and programming languages in which each user is an expert. The code to create the table in the database and insert values as below:
CREATE TABLE USERS(
UserName NVARCHAR (30),
ProgrammingLanguage NVARCHAR (30),
PRIMARY KEY(UserName, ProgrammingLanguage)
);
INSERT INTO USERS VALUES ("Don Draper","C++" );
INSERT INTO USERS VALUES ("Don Draper","Python" );
INSERT INTO USERS VALUES ("John Wick","HTML" );
INSERT INTO USERS VALUES ("John Wick","JavaScript" );
INSERT INTO USERS VALUES ("Michael Scott","Java" );
INSERT INTO USERS VALUES ("Walter White","Python" );
INSERT INTO USERS VALUES ("Walter White","R" );
/* query in action to select USERS table*/
SELECT * FROM USERS;
The data in the USERS table is as follows:
Now, let’s find out the DISTINCT userName entries in the USERS table.
SELECT DISTINCT UserName as USERNAME FROM USERS;
The output table containing usernames is as follows:
If DISTINCT is not used in the query, the returned table will include repeating names because some users are experts in multiple languages. The output of the query SELECT UserNAME FROM USERS is:
SELECT UserName as USERNAME FROM USERS;
Above, we get some names repeated in the output because some users are experts in multiple languages.
TOP Keyword in SQL
The TOP keyword in SQL is used in a SELECT query along with a number to get the top n records from the table.
Syntax
SELECT TOP n column_name FROM table_name;
Return value
Returns a table containing top n records from the table name.
Example
To get the first 3 usernames from the above database, the query will be
SELECT TOP 3 UserName FROM USERS
CREATE TABLE USERS(
UserName NVARCHAR (30),
ProgrammingLanguage NVARCHAR (30),
PRIMARY KEY(UserName, ProgrammingLanguage)
);
INSERT INTO USERS VALUES ("Don Draper","C++" );
INSERT INTO USERS VALUES ("Don Draper","Python" );
INSERT INTO USERS VALUES ("John Wick","HTML" );
INSERT INTO USERS VALUES ("John Wick","JavaScript" );
INSERT INTO USERS VALUES ("Michael Scott","Java" );
INSERT INTO USERS VALUES ("Walter White","Python" );
INSERT INTO USERS VALUES ("Walter White","R" );
/* query in action */
SELECT TOP 3 UserName FROM USERS;
The returned table will be as follows:
Using SQL DISTINCT and TOP Together
In the output of the TOP query, the name Don Draper is repeated twice because the first two records in the database are of Don Draper. Now suppose, there is a need to extract the names of the first 3 users in our database but these names must be unique. In a situation like this, a SELECT query containing both DISTINCT and TOP keywords can be used. The query used to get the top 3 distinct names in our database is
CREATE TABLE USERS(
UserName NVARCHAR (30),
ProgrammingLanguage NVARCHAR (30),
PRIMARY KEY(UserName, ProgrammingLanguage)
);
INSERT INTO USERS VALUES ("Don Draper","C++" );
INSERT INTO USERS VALUES ("Don Draper","Python" );
INSERT INTO USERS VALUES ("John Wick","HTML" );
INSERT INTO USERS VALUES ("John Wick","JavaScript" );
INSERT INTO USERS VALUES ("Michael Scott","Java" );
INSERT INTO USERS VALUES ("Walter White","Python" );
INSERT INTO USERS VALUES ("Walter White","R" );
/* query in action */
SELECT DISTINCT TOP 3 UserName FROM USERS;
The output of this query is:
Taking a closer look at the query used, it must be noted that the DISTINCT keyword must be used before the TOP keyword.
The reason for this is execution order of the query. In the case of multiple keywords, SQL executes them from left to right meaning the DISTINCT keyword is executed first on USERS, and then the TOP keyword is executed on the table returned by the DISTINCT keyword.
This execution order can be confirmed via the execution plan in SQL server management studio. To use this feature, go to the Query section in the top menu, and select Include actual execution plan.
Execute the query and select the execution plan in the output window. The execution plan of the query is below:
Move right to left to read the execution order. Here, Stream Aggregate represents the DISTINCT keyword. Using the execution plan, it can be confirmed that the DISTINCT is executed first and then the TOP keyword in SQL.
