T-SQL-Sub queries

by Online Tutorials Library July 14, 2022

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.

  SELECT column_name [, column_name]   FROM   table1 [, table2]   WHERE column_name OPERATOR      (SELECT column_name [, column_name]      FROM table1 [, table2]      [WHERE])   

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.

  SELECT *  FROM EMPLOYEES     WHERE ID IN (SELECT ID FROM CUSTOMER WHERE SALARY > 45000)  

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:

  INSERT INTO table_name [ (column1 [, column2 ]) ]      SELECT [ *|column1 [ column2 ]      FROM table1 [table2 ]    [ WHERE VALUE OPERATOR ]  

Example:

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

  INSERT INTO EMPLOYEES_BKP   SELECT * FROM EMPLOYEES    WHERE ID IN (SELECT ID FROM EMPLOYEES)  

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.

  UPDATE table   SET column_name = new_value   [ WHERE OPERATOR [ VALUE ]      (SELECT COLUMN_NAME      FROM TABLE_NAME)      [ WHERE) ]   

Example:

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

  UPDATE EMPLOYEES      SET SALARY = SALARY * 0.35      WHERE AGE IN (SELECT AGE FROM EMPLOYEES_BKP WHERE 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.

  DELETE FROM TABLE_NAME   [WHERE OPERATOR [VALUE]  (SELECT COLUMN_NAME   FROM TABLE_NAME)    [ WHERE) ]   

Example:

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

  DELETE FROM EMPLOYEES      WHERE AGE IN (SELECT AGE FROM EMPLOYEES_BKP WHERE AGE >=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

T-SQL-Sub queries

T-SQL-Sub queries

Subqueries with SELECT Statement:

Syntax:

Example:

Subqueries with INSERT Statement

Syntax:

Example:

Subqueries with UPDATE Statement

Syntax:

Example:

Sub queries with DELETE Statement:

Syntax:

Example:

Swift Deinitialization

Tableau File Types

You may also like