SELECT Query

Introduction

The SELECT query is undoubtedly the most common query in any SQL. The SELECT statement is used to retrieve data from a table or group of tables in the form of a table which is known as the result set. Most of the time, we retrieve data from a table or tables we use the SELECT command. You must have observed that the 'Select' has been written in all capital letters. It is common practice among professionals to use all the keywords in the SQL in capital letters so as to distinguish the rest of the query. We shall follow the same throughout this article.

The SELECT statement is not used in an isolated manner. It is often combined with some other commands. Before we move on and explore various ways of using the SELECT statement, it is important to understand why we need the SELECT statement.

Consider that we have a table employee having thousands of employee IDs. How can we retrieve each and every employee ID? This is one of the examples where the SELECT statement is used. Almost every query in SQL has the SELECT statement in them.

Since the primary focus of this article is the SELECT query, we will restrain from using complicated clauses that require more insights into SQL.

Syntax: SELECT column_1, column_2, ….., FROM table_name;

Recommended topics, Coalesce in SQL and Tcl Commands in SQL

Various ways to use the SELECT query

When the entire table has to be selected.

Consider the following students table.

Syntax: SELECT * FROM students;

The above query will select the table as it is. We first use the SELECT command to select something from the table. Next, we specify what we want to select using the * sign. Here * sign is read as "all." Next, we use the FROM command to specify from where we want to retrieve the information. The FROM command is followed by the name of the table i.e., students.

The * sign is used to select everything present in the table. The output of the above query is :

When some of the columns of the table have to be selected.

If we are only interested in the names of the students from the above table, we can use the SELECT query specifying the name of the columns we want.

Syntax: SELECT name_of_the_column FROM  table_name;

SELECT name FROM students;

The above query will display all the names present in the students table.

It is worth noting that the output has not only the names of the different students but also the the "name" column is part of the output. We can change the name of the column in a table using the AS clause.

SELECT name AS students_name FROM students;

The above query will rename the name column in students table to students_name in the output.

SELECT statement with some condition(s).

Syntax: SELECT column_name FROM table_name WHERE conditions;

Whenever some condition is imposed while retrieving the information from the table, we use the WHERE  clause in SQL.

Example: From the students table find the stud_id and name of the students whose age is 21 years.

SELECT stud_id, name FROM students WHERE age=21;

The above query selects the columns with stud_id and name from the student's table having age equal to 21. Here the condition ( age =21) is specified using the WHERE clause. There can be multiple conditions. The output of the above query is:

Example: Query to fetch the name and branch of the students with age=20 and branch as CSE.

SELECT name, branch FROM students WHERE age=20 AND branch=”CSE”;

The above query will fetch the name and branch of those students whose age is 20 and branch is CSE. Here we are specifying multiple conditions. Whenever there are multiple conditions, we use the AND and the OR operator. The AND operator behaves similarly to the logical AND. The information is fetched only when all the conditions are true. Whereas in the case of the OR operator, even if one of the conditions is true, the information is fetched.

Also see, SQL EXCEPT