Adding Subquery in 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:

 

 

Subquery in SELECT Statement

 

You can construct a SELECT statement with a subquery.

SELECT
 emp_fname,
 emp_lname,
 emp_add,
  (
    SELECT SUM(emp_no)
    FROM EmployeeDetail
  ) as Totalemp_no
FROM
  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:

 

 

Subquery in FROM Clause

 

You can construct a FROM Clause with a subquery.

SELECT * FROM (SELECT *  FROM EmployeeDetail WHERE emp_no = '39') EmployeeDetail where emp_fname='Copper' ;

The following query also produces the same result:

SELECT * FROM  EmployeeDetail where emp_no = '39' ;

Now press F5 to see the result:

 

 

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:

 

 

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 =.

select emp_fname,emp_lname
from EmployeeDetail
where emp_add =(select dept_add  from dept where dept_name ='finance')

Now press F5 to see the result:

 

 

 

Subquery and in operator

 

This example shows the simple subquery that is used with the IN operator:

--subquery and in operator
select * from EmployeeDetail
where emp_add IN(select dept_add from dept where dept_name ='finance')

Now press F5 to see the result:

 

 

 

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.

--subquery and any operator
select emp_fname,emp_no
from EmployeeDetail
where emp_add >any(select emp_add from EmployeeDetail );

Now press F5 to see the result:

 

 

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.

--subquery and all operator
select emp_fname, emp_no
from EmployeeDetail
where emp_add <=all (select emp_add from EmployeeDetail);

Now press F5 to see the result: