Open In App

SQL | Subquery

Last Updated : 30 Jul, 2024
Summarize
Comments
Improve
Suggest changes
Like Article
Like
Save
Share
Report
News Follow

In SQL a Subquery can be simply defined as a query within another query. In other words we can say that a Subquery is a query that is embedded in WHERE clause of another SQL query. Important rules for Subqueries:

  • You can place the Subquery in a number of SQL clauses: WHERE clause, HAVING clause, FROM clause. Subqueries can be used with SELECT, UPDATE, INSERT, DELETE statements along with expression operator. It could be equality operator or comparison operator such as =, >, =, <= and Like operator.
  • A subquery is a query within another query. The outer query is called as main query and inner query is called as subquery.
  • The subquery generally executes first when the subquery doesn’t have any co-relation with the main query, when there is a co-relation the parser takes the decision on the fly on which query to execute on precedence and uses the output of the subquery accordingly.
  • Subquery must be enclosed in parentheses.
  • Subqueries are on the right side of the comparison operator.
  • Use single-row operators with single row Subqueries. Use multiple-row operators with multiple-row Subqueries.

Syntax: There is not any general syntax for Subqueries. However, Subqueries are seen to be used most frequently with SELECT statement as shown below:

SELECT column_name
FROM table_name
WHERE column_name expression operator
(SELECT column_name FROM table_name WHERE ...);

Sample Table:

DATABASE

NAME ROLL_NO LOCATION PHONE_NUMBER
Ram 101 Chennai 9988775566
Raj 102 Coimbatore 8877665544
Sasi 103 Madurai 7766553344
Ravi 104 Salem 8989898989
Sumathi 105 Kanchipuram 8989856868

STUDENT

NAME ROLL_NO SECTION
Ravi 104 A
Sumathi 105 B
Raj 102 A

Sample Queries:

  • To display NAME, LOCATION, PHONE_NUMBER of the students from DATABASE table whose section is A
SELECT NAME, LOCATION, PHONE_NUMBER 
FROM DATABASE
WHERE ROLL_NO IN (SELECT ROLL_NO
FROM STUDENT
WHERE SECTION='A');
  • Explanation : First subquery executes “ SELECT ROLL_NO from STUDENT where SECTION=’A’ ” returns ROLL_NO from STUDENT table whose SECTION is ‘A’.Then outer-query executes it and return the NAME, LOCATION, PHONE_NUMBER from the DATABASE table of the student whose ROLL_NO is returned from inner subquery. Output:
NAME ROLL_NO LOCATION PHONE_NUMBER
Ravi 104 Salem 8989898989
Raj 102 Coimbatore 8877665544
  • Insert Query Example:

Table1: Student1

NAME ROLL_NO LOCATION PHONE_NUMBER  
Ram 101 chennai 9988773344  
Raju 102 coimbatore 9090909090  
Ravi 103 salem 8989898989  

Table2: Student2

NAME ROLL_NO LOCATION PHONE_NUMBER  
Raj 111 chennai 8787878787  
Sai 112 mumbai 6565656565  
Sri 113 coimbatore 7878787878  
  • To insert Student2 into Student1 table:
INSERT INTO Student1 
SELECT * FROM Student2;
  • Output:
NAME ROLL_NO LOCATION PHONE_NUMBER  
Ram 101 chennai 9988773344  
Raju 102 coimbatore 9090909090  
Ravi 103 salem 8989898989  
Raj 111 chennai 8787878787  
Sai 112 mumbai 6565656565  
Sri 113 coimbatore 7878787878  
  • To delete students from Student2 table whose rollno is same as that in Student1 table and having location as chennai
DELETE FROM Student2 
WHERE ROLL_NO IN (SELECT ROLL_NO
FROM Student1
WHERE LOCATION = 'chennai');
  • Output:
1 row delete successfully.
  • Display Student2 table:
NAME ROLL_NO LOCATION PHONE_NUMBER  
Sai 112 mumbai 6565656565  
Sri 113 coimbatore 7878787878  
  • To update name of the students to geeks in Student2 table whose location is same as Raju,Ravi in Student1 table
UPDATE Student2 
SET NAME='geeks'
WHERE LOCATION IN (SELECT LOCATION
FROM Student1
WHERE NAME IN ('Raju', 'Ravi'));
  • Output:
1 row updated successfully.
  • Display Student2 table:
NAME ROLL_NO LOCATION PHONE_NUMBER  
Sai 112 mumbai 6565656565  
geeks 113 coimbatore 7878787878  



Previous Article
Next Article

Similar Reads

Difference Between Nested Subquery, Correlated Subquery and Join Operation
Nested subqueries, correlated subqueries, and join operations are common methods for querying data, but they all have different behaviors and serve various purposes. Joins are used to combine two or more different tables based on a common field between them. We can easily retrieve data from multiple tables using joins. Similarly, Nested and correla
8 min read
How to Use the IN Operator with a SubQuery in SQL?
In this article, we will see the use of IN Operator with a SubQuery in SQL. IN operator is used to compare the column values with the list of values. The list of values is defined after IN query in SQL. If we don't know the exact list of values to be compared, we can generate the list of values using the query. Syntax: Without subquery: SELECT colu
2 min read
SQL Server Subquery
In SQL Server, Subqueries are a powerful feature used to perform complex queries and combine data from multiple tables or multiple data sets. Subqueries can be used in different business cases and in different scenarios to join data from an inner query with an outer query. In this article let us see what is a subquery, where it can be used, and how
5 min read
SQL Server Correlated Subquery
Correlate subquery is a great tool in SQL servers that allows users to fetch the required data from the tables without performing complex join operations. In SQL, a subquery is a query nested inside another query. A correlated subquery is a specific type of subquery that references columns from the outer query, creating a relationship between the t
7 min read
How to Update Table Rows in PL/SQL Using Subquery?
Updating table rows in PL/SQL via subqueries allows precise data modifications based on specific conditions. By integrating subqueries within the UPDATE statement, rows can be selectively targeted for updates, enhancing data management efficiency. This article explores the concept of updating rows in PL/SQL using subqueries, providing insights into
4 min read
SQL Join vs Subquery
The difference between SQL JOIN and subquery is that JOIN combines records of two or more tables whereas Subquery is a query nested in another query. SQL JOIN and Subquery are used to combine data from different tables simplifying complex queries into a single statement. Here we will discuss SQL JOIN vs Subquery in detail, and understand the differ
4 min read
How to Update Table Rows in SQL Server using Subquery ?
Updating table rows in SQL Server using a subquery is a common operation that allows us to modify records based on values derived from another table or query. In this article, we will explain how to update table rows in SQL Server using subquery with the help of examples. Updating Table Rows Using a Subquery in SQL ServerA subquery allows us to per
3 min read
How to Use the IN Operator With a SubQuery?
The IN Operator in SQL allows to specifies multiple values in WHERE clause, it can help you to easily test if an expression matches any value in the list of values. The use of IN Operator reduces the need for multiple OR conditions in statements like SELECT, INSERT, UPDATE, and DELETE. Sub Queries:The SQL queries where one or more SELECT statements
2 min read
How to Update Table Rows in SQLite Using Subquery
SQLite is a lightweight, serverless RDBMS that is used to store and manipulate the data in the database in relational or tabular form. It can run on various operating systems like Windows, macOS, Linux and mobile operating systems like Android and iOS. SQLite is designed for single-user access. Multiple processes can read from an SQLite database si
4 min read
How to Update Table Rows in PostgreSQL Using Subquery?
PostgreSQL is a general-purpose object-relational database management system and is open-source. It ensures data integrity features like constraints, transactions and foreign key support. In this article, We will understand How to update table rows in PostgreSQL using subquery using various methods, examples and so on. Introduction to Update Statem
5 min read
MYSQL Subquery
A subquery is embedded inside another query and acts as input or output for that query. Subqueries are also called inner queries and they can be used in various complex operations in SQL. Subqueries help in executing queries with dependency on the output of another query. Subqueries are enclosed in parentheses. In this article, we will see how we c
4 min read
How to Update Table Rows Using Subquery in MySQL
Updating table rows using subqueries in MySQL enables precise modifications based on specific conditions or values from other tables. This technique leverages subqueries within the SET or WHERE clauses of the UPDATE statement, allowing dynamic and context-specific updates. This guide covers the syntax, methods, and examples of updating rows with ag
5 min read
Difference between Structured Query Language (SQL) and Transact-SQL (T-SQL)
Structured Query Language (SQL): Structured Query Language (SQL) has a specific design motive for defining, accessing and changement of data. It is considered as non-procedural, In that case the important elements and its results are first specified without taking care of the how they are computed. It is implemented over the database which is drive
2 min read
Configure SQL Jobs in SQL Server using T-SQL
In this article, we will learn how to configure SQL jobs in SQL Server using T-SQL. Also, we will discuss the parameters of SQL jobs in SQL Server using T-SQL in detail. Let's discuss it one by one. Introduction :SQL Server Agent is a component used for database task automation. For Example, If we need to perform index maintenance on Production ser
7 min read
SQL SERVER – Input and Output Parameter For Dynamic SQL
An Input Parameter can influence the subset of rows it returns from a select statement within it. A calling script can get the value of an output parameter. An aggregate function or any computational expression within the stored process can be used to determine the value of the output parameter. A parameter whose value is given into a stored proced
3 min read
Difference between T-SQL and PL-SQL
1. Transact SQL (T-SQL) : T-SQL is an abbreviation for Transact Structure Query Language. It is a product by Microsoft and is an extension of SQL Language which is used to interact with relational databases. It is considered to perform best with Microsoft SQL servers. T-SQL statements are used to perform the transactions to the databases. T-SQL has
3 min read
SQL - SELECT from Multiple Tables with MS SQL Server
In SQL we can retrieve data from multiple tables also by using SELECT with multiple tables which actually results in CROSS JOIN of all the tables. The resulting table occurring from CROSS JOIN of two contains all the row combinations of the 2nd table which is a Cartesian product of tables. If we consider table1 contains m rows and table2 contains n
3 min read
How to Execute SQL Server Stored Procedure in SQL Developer?
A stored procedure is a set of (T-SQL ) statements needed in times when we are having the repetitive usage of the same query. When there is a need to use a large query multiple times we can create a stored procedure once and execute the same wherever needed instead of writing the whole query again. In this article let us see how to execute SQL Serv
2 min read
SQL Query to Check if Date is Greater Than Today in SQL
In this article, we will see the SQL query to check if DATE is greater than today's date by comparing date with today's date using the GETDATE() function. This function in SQL Server is used to return the present date and time of the database system in a ‘YYYY-MM-DD hh:mm: ss. mmm’ pattern. Features: This function is used to find the present date a
2 min read
SQL Query to Add a New Column After an Existing Column in SQL
Structured Query Language or SQL is a standard Database language that is used to create, maintain and retrieve data from relational databases like MySQL, Oracle, SQL Server, Postgres, etc. In Microsoft SQL Server, we can change the order of the columns and can add a new column by using ALTER command. ALTER TABLE is used to add, delete/drop or modif
3 min read
SQL Query to Convert Rows to Columns in SQL Server
In this article we will see, how to convert Rows to Column in SQL Server. In a table where many columns have the have same data for many entries in the table, it is advisable to convert the rows to column. This will help to reduce the table and make the table more readable. For example, Suppose we have a table given below: NAMECOLLEGEROLL NUMBERSUB
2 min read
Dynamic SQL in SQL Server
In SQL Server, at times the SQL Queries need to be dynamic and not static, meaning the complete SQL query may be built dynamically at run time as a string using the user inputs and any specific application logic. This can be done in queries run from back-end applications or inside stored procedures. In this article let us look into the details abou
6 min read
Dynamic SQL and Temporary Tables in SQL Server
In SQL Server, creating and using Temp Tables using dynamic SQL is a good feature when we need to temporarily create tables at run time and delete automatically all within a session. They can be very useful when we need to store temporary data in a structured format and do data manipulation using Data Manipulation Language in SQL. In this article l
6 min read
How to SQL Select from Stored Procedure using SQL Server?
There may be situations in SQL Server where you need to use a stored procedure to get data from a SQL query. For direct data selection from a stored procedure within a query, SQL Server offers options like OPENQUERY and OPENROWSET. The usual way is running the stored procedure independently and then querying the outcomes. The idea of utilizing SQL
3 min read
SQL Quiz : Practice SQL Questions Online
This SQL quiz covers various topics like SQL basics, CRUD operations, operators, aggregation functions, constraints, joins, indexes, transactions, and query-based scenarios. We've included multiple-choice questions, fill-in-the-blank questions, and interactive coding challenges to keep things interesting and challenging. Whether you're a beginner l
3 min read
BULK INSERT in SQL Server(T-SQL command)
BULK INSERT in SQL Server(T-SQL command): In this article, we will cover bulk insert data from csv file using the T-SQL command in the SQL server and the way it is more useful and more convenient to perform such kind of operations. Let's discuss it one by one.  ConditionSometimes there is a scenario when we have to perform bulk insert data from .cs
3 min read
Difference between SQL and T-SQL
SQL (Structured Query Language) is the standard language for managing and manipulating relational databases, enabling operations like querying, updating, and deleting data. T-SQL (Transact-SQL), an extension of SQL developed by Microsoft, adds advanced features and procedural capabilities specifically for SQL Server. In this article, We will learn
4 min read
SQL Server | Convert Tables in T-SQL into XML
XML (Extensible Markup Language) is a widely-used markup language designed to store and transfer structured data between different systems and platforms. While HTML focuses on the visual representation of data OverviewXML is similar to HTML which is designed to structure and store data for sharing across different systems and platforms.Unlike HTML,
3 min read
SQL Exercises : SQL Practice with Solution for Beginners and Experienced
SQL (Structured Query Language) is a powerful tool used for managing and manipulating relational databases. Whether we are beginners or experienced professionals, practicing SQL exercises is important for improving your skills. Regular practice helps you get better at using SQL and boosts your confidence in handling different database tasks. So, in
15+ min read
SQL Query to Find Number of Employees According to Gender Whose DOB is Between a Given Range
Query in SQL is like a statement that performs a task. Here, we need to write a query that will find the number of employees according to gender whose DOB is in the given range. We will first create a database named “geeks” then we will create a table “department” in that database. Creating a Database : Use the below SQL statement to create a datab
2 min read
Article Tags :
three90RightbarBannerImg