SELECT Statement in SQL

This article will help you understand some of the most common SQL SELECT use cases with examples. Large amounts of data in diverse types are stored in databases. Have you considered how to choose data from a database?

This is where the SQL SELECT statement comes in. When you think about SQL, the SELECT statement is one of the first things you come across. It is as if when you are learning a new programming language, you will begin with a metaphorical “Hello World” example that shows the most basic command that may be used. 

You can also explore: What are Constraints in SQL?

In this article, I shall discuss the following topics:

1. What is the SQL SELECT statement
2. Filter SELECT statement
3. Using DISTINCT with SELECT statement
4. Sorting the results with SELECT statement
5. Using GROUP BY with SELECT statement
6. To use SELECT with the HAVING clause

What is the SQL SELECT statement?

The SELECT statement is used to retrieve data from single or multiple tables. It’s obvious from the name! You can choose all of the rows in a table or just the ones that fulfill a given criterion or set of circumstances.

You can also explore: All About DML Commands in SQL

We can say that the syntax of the SQL SELECT statement is straightforward-

–To select complete data from the table 

SELECT * FROM TblName;

–To select a few columns

SELECT ColName1, ColName2, ColName(N) FROM TblName; 

Let me take an example of some basic information about books available in a publishing house to understand the SELECT statement better. The table looks like this:

 In the world of SQL, the very first command for this table would be as follows:

Now let me explain the query to you-

• SELECT SQL keyword which shows what we wish to display or retrieve.
• The * (asterisk) symbol signifies “everything, all columns”.
• FROM is an SQL keyword that denotes the tables being used. 
• Books is the name of the table we retrieve the data from.

This command means “show all data from the Books table”. You can see that the SELECT statement, which is used to access or display information from database tables, is already included in this query. As a result, our database will generate something similar to:

I used the asterisk sign (*) to show all of the data from the table in the example above. We can even use a specific column name instead of the asterisk sign:

The query above is similar to the previous one. However, instead of SELECT *, which meant “show all the columns”, I used SELECT book_title, which means: “select the column book_tile”.

You can also explore: How to use a CASE statement in SQL

Our query above means “show all book titles from the table Books”. As a result, we’ll see the following:

Super! We got all the books! Isn’t it simple, right?

Next, let us try to retrieve more than one column to select by separating the column name with the use of commas (,).

This time, SELECT book_title, publish_year, copies_sold means “select the specified columns”. The entire SQL query will show all the book titles alongside the corresponding year they were published and the number of copies that were sold from the table Books.

When I run this query in our database, as a result, we’ll get something like this:

Let’s look at some more complex scenarios where the SELECT command can be used in different ways.

How to Filter SQL SELECT statement?

If you have a table with a large number of rows, you may want to limit the number of rows returned by the query. Let me take the above Books table to explain the filter functions like WHERE, LIKE, and TOP with our SELECT statement.

Before diving into the details, let’s take a glance at the syntax of the commands using WHERE, LIKE and TOP functions.

WHERE-

              SELECT column_name1,…, column_name(n) FROM table_name WHERE condition;

LIKE- 

            SELECT column_name1,…, column_name(n) FROM table_name WHERE condition LIKE pattern;

TOP-  

         SELECT TOP (n) FROM table_name;

Now let’s assume we want the list of books that were translated. So, I will write the below query to get the result-

Here is the resulting table-

Now, for example, we want to retrieve the book whose title starts with the letter ‘E’. Here I will write a query using the LIKE clause to get the desired output-

Let’s check out the output below-

Next, we can use the following query to obtain only two entries from the table.

Following is the result which we will obtain-

At the same time, we can limit the output of the SQL SELECT statement with a percent value. Such as, the following query returns only 40 percent of the total data set.

Take a look at the output below:

Moving ahead, let’s check out the usage of the DISTINCT function with the SELECT statement.

How to use DISTINCT with SELECT statement?

A column in a table often has multiple duplicate values, and you may only wish to list the different (distinct) values. Only distinct (different) data is returned with the DISTINCT command.

Let’s see the syntax of such statements–

         SELECT DISTINCT column_name1,…, column_name(n) FROM table_name;

Let me now provide an example to demonstrate this. For instance, I want to select the DISTINCT year from our table-

We will get the below table as the result-

Next, let us try to understand how to do sorting with the SELECT statement.

How to sort the results with SELECT statement in SQL?

You might want to display the rows in a different order than SQL Server does when it produces the results. The SQL ORDER BY clause can be used to do this. 

Following is the syntax to write a query using ORDER BY clause-

            SELECT column_name1,…, column_name(n)

           FROM table_name

          ORDER BY column_name1,…, column_name(n)  ASC/DESC;

Now let us take an example and return rows in ascending order of titles of the books.

So, the following is the output that we will get from the above query-

Taking the previous example, I will now try to return the rows in the descending order of the titles.

The output table was turned upside down by altering one word in the query from ASC to DESC.

You have to keep the below points in mind when using the ORDER BY clause-

• You can sort on one or more columns and in either ascending (ASC) or descending order (DESC).
• By default, the ORDER BY clause sorts the records in the column(s) in ascending order.
• To sort the records in decreasing order, use the DESC keyword.

Moving on in this article, let us understand how to use SQL SELECT with the GROUP BY clause.

How to use GROUP BY with SELECT statement?

Instead of individual detail rows from a database, the GROUP BY clause can be used to provide aggregated results.

              SELECT column_name1,…, <aggregate function>column_name (n)

             FROM table_name

             GROUP BY column_name1,…, column_name (n) ;

Note that to group the result set by one or more columns, the GROUP BY statement is generally used with aggregate functions like MAX (), MIN (), COUNT (), SUM (), AVG (), etc.

Now suppose I want the list of the number of books published each year.

You can check out the output table below-

You can see that a new name – row_count – has been assigned to the result using the AS keyword. “Assigning an alias” is the term for this.

Note that for every column that isn’t a constant and isn’t used inside an aggregation function, you should put it in the GROUP BY clause. If I add book type to the SELECT but not to the GROUP BY in the prior query, for example, I will get the following error:

Next in this article, let us understand how to use SQL SELECT with the HAVING clause.

How to use SELECT with the HAVING clause?

The WHERE clause can be used to filter individual rows. But what if you wish to filter on the output of an aggregated function? This is not possible in the WHERE clause because such results do not exist in the original table.  We can utilize the HAVING clause to accomplish this.

                 SELECT column_name1,…, column_name(n) 

                 FROM table

                WHERE condition

               GROUP BY column_name1,…, column_name (n)

               HAVING condition

              ORDER BY column_name1,…, column_name(n)  ASC/DESC;

For instance, I want the list of the number of books published each year where the number of books is > 1 and are sorted in descending order.

Here is the output table-

Also, Read: SQL Tutorial for Beginners

Takeaway

When accessing information from databases, the SELECT statement is by far the most commonly used SQL command. Its primary use is to display data from tables, but it is capable of much more. 

By giving column names in your query, you can pick individual columns from a table. The wildcard operator (*) can be used to pick all columns in a database. The SELECT command can also be used to filter entries based on sophisticated conditions, connect data from several tables, and provide running averages in more advanced scenarios.

We hope that this article provided you with a clear understanding of the potential of this SQL command.

For more insights, read: Subqueries in SQL

Top Trending Tech Articles:
Career Opportunities after BTech Online Python Compiler What is Coding Queue Data Structure Top Programming Language Trending DevOps Tools Highest Paid IT Jobs Most In Demand IT Skills Networking Interview Questions Features of Java Basic Linux Commands Amazon Interview Questions

Recently completed any professional course/certification from the market? Tell us what liked or disliked in the course for more curated content.

Click here to submit its review with Shiksha Online.