A SubQuery is a query in a query. SQL subquery is usually added in
the WHERE clause of the SQL statement. Most of the time, a subquery is used
when you know how to search for a value using a SELECT statement, but do not
know the exact value in the database.
Subqueries are an alternate way of returning data from multiple
tables. A subquery is also known as Inner Query & Nested Query.
Subqueries can be used with the following SQL statements along
with the operators like =, <, >, >=, <=, LIKE IN, NOT IN etc.
SELECT
INSERT
UPDATE
DELETE
A SubaQuery mostly used perform following tasks,
- Check
whether the query selects any rows.
- Compare
an expression to the result of the query.
- Determine
if an expression is included in the results of the query.
Example of Subquery
Subqueries with SELECT statement
Subquery usually returns only one record, but it can also return
multiple records when used with operators LIKE IN, NOT IN in the where clause.
The query syntax would be like,
SELECT Id, Name
FROM Student
WHERE Id IN
(SELECT Id FROM
Student_Marks WHERE Marks >= 75);
Subqueries with INSERT statement
INSERT statement can be used with subqueries. Here is an example
of subqueries using INSERT statement.
INSERT INTO Student_Subject (Id, Subject)
SELECT Id, 'Maths' AS Subject
FROM Student
WHERE Id IN
(SELECT Id FROM
Student_Marks WHERE Marks >= 75);
Subqueries with an UPDATE statement
UPDATE statement can be used with subqueries, you can set new
column value equal to the result returned by a single row subquery. Here is an
example of subqueries using an UPDATE statement.
UPDATE Student
SET Grade='A'
WHERE Id IN
(SELECT Id FROM
Student_Marks WHERE Marks >= 75);
Subqueries with a DELETE statement
DELETE statement can be used with subqueries. Here is an example
of subqueries using a DELETE statement.
DELETE FROM Student
WHERE Id IN
(SELECT Id FROM
Student_Marks WHERE Marks < 35);