SQL SubQuery

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);