SQL - Sub Queries

SQL is the standard language for the Relational Database, for storing, manipulating and retrieving stored data. For basics of SQL (link)

Why Sub Query? 🤨

It is easy to understand and maintain. Subqueries use the result of the query in the outer query.
Subqueries divide the complex queries into parts so that a complex query can be broken into a series of logical steps. It can also replace complex joins and union.

Sub Queries

A subquery or a nested query is a query within another SQL query. subquery return data that will be used in the main query as a condition to further restrict the retrieved data.

Subqueries can be used with the SELECT, INSERT, UPDATE, DELETE statements with operators like =, !=, <, >, BETWEEN, IN etc.

Subquery with SELECT statement:

Subqueries are mostly used with select statement. The basic syntax is:
SELECT ColumnName FROM TableName
WHERE ColumnName Operator (SELECT ColumnName FROM TableName);

Subquery with INSERT statement:

The INSERT statement uses the result from the subquery to insert into another table. The basic syntax is:
INSERT INTO TableName
SELECT ColumnName FROM TableName
WHERE ColumnName Operator (SELECT ColumnName FROM TableName);

Subquery with UPDATE statement:

Multiple Columns can be updated using the subquery with the UPDATE statement. The basic syntax is:
UPDATE TableName SET ColumnName = Value
WHERE ColumnName Operator (SELECT ColumnName FROM TableName);

Subquery with DELETE statement

Subquery can be used with DELETE statement like with any other statements above. The basic syntax is:
DELETE FROM TableName
WHERE ColumnName Operator (SELECT ColumnName FROM TableName);