Properties of SQL Sub-Query

Adding Subquery in a Select Statement in SQL Server 2012

Properties of SQL Sub-Query

A sub-query must be enclosed in parenthesis.

A sub-query must be put in the right hand of the comparison operator.

A sub-query cannot contain an ORDER-BY clause.

A query can contain more than one sub-query.

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:

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

SELECT * FROM  EmployeeDetail where emp_no = '39' ;  

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

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') 

Adding Subquery in a Select Statement in SQL Server 2012