T-SQL-Sub queries

A sub-query is used with the other SQL Server query and embedded with the WHERE clause. The subquery is used to return data, which is used in the main question as a condition to restrict the data to retrieve.

Sub queries are used with the Statements SELECT, INSERT, UPDATE, and DELETE with the operators =, <, >, >=, <=, IN and BETWEEN etc.

There are few rules that sub queries follows -

• A subquery is enclosed in parenthesis.
• A subquery includes the FROM clause and SELECT clause.
• The Optional GROUP BY, WHERE, and HAVING clauses are also used in the subquery.
• COMPUTE and FOR BROWSE clauses are not included by the subquery.
• We include an ORDER BY clause when a TOP term is included.
• We have subqueries to 32

Subqueries with SELECT Statement:

Syntax:

Subqueries are used with the SELECT statement.

Below is the syntax.

Example:

See the EMPLOYEES table:

We apply the subquery with the help of the SELECT statement. 

Output:

Subqueries with INSERT Statement

Sub queries can be used with INSERT statements. The selected data of the sub query can modify the character, number or date functions.

Syntax:

The syntax of INSERT Statement is:

Example:

The syntax is used to copy the complete EMPLOYEES table into the EMPLOYEES_BKP.

Subqueries with UPDATE Statement

The sub query is used with UPDATE statement in the conjunction. When we use a sub query with the UPDATE statement single or more columns.

Syntax:

Below is the basic syntax.

Example:

The below command update the SALARY by 0.25 times into the EMPLOYEES table for the EMPLOYEES whose AGE> =31.

Two rows are given below in the table:

Sub queries with DELETE Statement:

The sub query is used in conjunction with the DELETE statement, with other comments generated above.

Syntax:

The syntax is.

Example:

The query deletes the records of the table EMPLOYEES whose AGE is greater than or equal to 31.

It has two rows, and the EMPLOYEES table will have the below records.