Adding Subquery in a Select Statement in SQL Server 2012

Today, I have provided an article showing you how to add a subquery to a select statement in SQL Server 2012.

Today, I am providing an article showing you how to add a subquery to a select statement in SQL Server 2012. A subquery is also called an inner query. The Transact-SQL language offers the ability to compare a column value to the result of another select statement. Such an inner select statement nested in the where clause of an outer SELECT Statement is also called a subquery. The statement which contains the subquery is called the outer query. A subquery is a query that is nested inside a select, insert, update, or delete statement, or inside another subquery. Here, we will see how to use a subquery with the select statement.
 

Properties of SQL Sub-Query

  1. A sub-query must be enclosed in parenthesis.
  2. A sub-query must be put in the right hand of the comparison operator.
  3. A sub-query cannot contain an ORDER-BY clause.
  4. A query can contain more than one sub-query.
Now create a table named EmployeeDetail with the columns emp_fname, emp_lname, emp_no, emp_add. The table looks as in the following:
 
img1.jpg
 

Subquery in SELECT Statement

 
You can construct a SELECT statement with a subquery.
  1. SELECT  
  2.  emp_fname,  
  3.  emp_lname,  
  4.  emp_add,  
  5.   (  
  6.     SELECT SUM(emp_no)  
  7.     FROM EmployeeDetail     
  8.   ) as Totalemp_no  
  9. FROM  
  10.   EmployeeDetail where emp_fname='Copper'  
In the preceding query I have inserted a fourth column as the subquery in the SELECT statement and named the column Totalemp_no. The  sum statement returns the total number. The preceding table shows the result set returned by the outer SELECT statement. Now press F5 to see the result:
 
img2.jpg
 

Subquery in FROM Clause

 
You can construct a FROM Clause with a subquery.
  1. SELECT * FROM (SELECT *  FROM EmployeeDetail WHERE emp_no = '39') EmployeeDetail where emp_fname='Copper' ;  
The following query also produces the same result:
  1. SELECT * FROM  EmployeeDetail where emp_no = '39' ;  
Now press F5 to see the result:
 
img3.jpg
 

Subquery in WHERE Clause

 
You can construct a WHERE Clause with a subquery. We can use the following operators with Subquery.
  • comparison operator
  • IN operator
  • ANY or All operator
  • EXISTS function
Creating another table
 
Now create a table named dept with the columns dept_name, dept_add. The table looks as in the following:
 
img4.jpg
 

Subquery and Comparison operator

 
This example shows the simple subquery that is used with the operator =.
 
The following query determines the first name and last name of the employee with the operator =.
  1. select emp_fname,emp_lname   
  2. from EmployeeDetail   
  3. where emp_add =(select dept_add  from dept where dept_name ='finance')   
Now press F5 to see the result:
 
img5.jpg 
 

Subquery and in operator

 
This example shows the simple subquery that is used with the IN operator:
  1. --subquery and in operator  
  2. select * from EmployeeDetail   
  3. where emp_add IN(select dept_add from dept where dept_name ='finance')  
Now press F5 to see the result:
 
img6.jpg 
 

Subquery and ANY operator

 
The ANY operator evaluates to true if the result is an inner query containing at least one row that satisfies the comparison. This example shows the simple subquery that is used with the ANY operator.
  1. --subquery and any operator    
  2. select emp_fname,emp_no     
  3. from EmployeeDetail  
  4. where emp_add >any(select emp_add from EmployeeDetail );   
Now press F5 to see the result:
 
img7.jpg
 
The "ALL" Operator evaluates to true if the evaluation of the table column in the inner query returns all the values of that column. This example shows the simple subquery that is used with the ALL operator.
  1. --subquery and all operator  
  2. select emp_fname, emp_no   
  3. from EmployeeDetail   
  4. where emp_add <=all (select emp_add from EmployeeDetail);  
Now press F5 to see the result:
 
img8.jpg