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