Javatpoint Logo
Javatpoint Logo

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:

ID NAME AGE ADDRESS SALARY
1 Hamilton 23 Australia 34000
2 Warner 34 England 22000
3 Martin 28 China 25000
4 Twinkle 30 Turkey 50000
5 Tinu 32 Nepal 45000
6 Michal 31 Bhutan 20000
7 Harper 20 Bangladesh 15000

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

Output:

ID NAME AGE ADDRESS SALARY
1 Hamilton 23 Australia 34000
4 Twinkle 30 Turkey 50000
5 Tinu 32 Nepal 45000

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:

ID NAME AGE ADDRESS SALARY
1 Hamilton 23 Australia 34000
2 Warner 34 England 22000
3 Martin 28 China 25000
4 Twinkle 30 Turkey 50000
5 Tinu 32 Nepal 45000
6 Michal 31 Bhutan 20000
7 Harper 20 Bangladesh 15000

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.

ID NAME AGE ADDRESS SALARY
1 Hamilton 23 Australia 34000
3 Martin 28 China 25000
4 Twinkle 30 Turkey 50000
7 Harper 20 Bangladesh 15000

Next TopicT-SQL Transactions




Help Others, Please Share

facebook twitter pinterest

Learn Latest Tutorials


Preparation


Trending Technologies


B.Tech / MCA