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;
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.
Nested SELECT query
A nested SELECT query is a query inside another query. Sometimes the information provided in the table in the form of columns cannot be directly used in a query. We may have to first figure out a condition and then apply that condition to our table.
Consider the following player's table.
The columns of the table are player_id, name, team, and salary in crores. If we want to find the details of only those players whose salary is more than or equal to the average of the salaries given, there is no direct way to do it. Since we don't have any average salary column, we cannot directly apply the WHERE clause. This is where the nested query comes into play.
SELECT * FROM players WHERE salary >= (SELECT AVG(salary) FROM players);
The output of the above query is:
The inner query will first calculate the average salary using the AVG() function, which is provided in the SQL. After that, it will return the value of the average salary. The outer query will now compare the salaries of each player and see if their corresponding value is greater than or equal to the average value(75 in this case). Only the first three players have a salary greater than or equal to the average salary. Hence they are displayed.
Frequently asked questions
- Write the basic syntax of a SELECT statement?
SELECT column_name1, column_name2,..... FROM table_name;
- What are the various operators used with the SELECT statement?
The logical, comparison and arithmetic operators are some of the examples of the operators that can be used with the SELECT statement.
- Write a query to display only the name of the tables in a database.
SELECT table_name FROM information_schema.tables
- What is a nested select query?
A SELECT statement inside another SELECT statement is known as a nested select query.
- The SELECT clause is used to fetch information from a given table in the form of a table.
- The SELECT statement is often used with the WHERE clause to specify certain conditions.
- We can use various operators with the SELECT statement. They are logical, comparison, and arithmetic operators.
- SELECT query inside a SELECT query is known as a nested query.
Never stop learning. Explore more here !
Also, you can check out the Top 100 SQL Problems to get hands-on experience with frequently asked interview questions and land your dream job.