SQL SUB QUERY (Part -12)

 Sub Query is a Query within another Query. There are 2 types of Sub Queries:

1)     Single Row sub query

2)     Multi Row sub query

In other words, A Subquery or Inner query or a Nested query is a query within another SQL query and embedded within the WHERE clause.

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

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

• A subquery may occur in :
• - A SELECT clause
• - A FROM clause
• - A WHERE clause

Subqueries are most frequently used with the SELECT statement. The basic syntax is as follows −

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

A subquery is also called an inner query or inner select, while the statement containing a subquery is also called an outer query or outer select. Many Transact-SQL statements that include subqueries can be alternatively formulated as joins. 

Based on Outer (OQ) and Inner Queries (IQ), Sub Query is classified into 2 types:

a)      Non-Correlated sub query:- Inner query will execute first while Outer query will execute later.

 

b)     Correlated sub query: Outer query will execute first while Inner query will execute later.

(OQ) select * from table1 where <condition> (select * from table_) (IQ)

See the Example below in Database to find the Employee who is having maximum salary. 

Here,

 Outer Query is select * from employee where emp_salary

 Inner Query is (select max(emp_salary) from employee)

 First, Inner Query will be executed, and then later Output will be given to Outer Query.