Subquery In T-SQL

Introduction

Subqueries can be used to know answers from queries like "which market region has got maximum profit more than market(X)". For such query, two queries have to be executed: the first query finds individual market’s profit and the second finds the comparison and so will calculate comparison output. Sub query is an approach that provides the capability of embedding the first query into the other.

Syntax for Subquery

SELECT 		<column, ...>
FROM 
<table>
WHERE
expression operator
    			(SELECT	 <column, ...>
       			  FROM
<table>
      			  WHERE
<condition>
 			)

For example, the following statement describes the above answer.

SELECT	* 
FROM 	Markets
WHERE
Profit > (SELECT 	Markets
  			  WHERE	Name= “X”)

Rules need to be defined:

• A subquery must be enclosed in parenthesis.
• A subquery must be put in the right hand of the comparison operator, and
• A subquery cannot contain an ORDER-BY clause.
• A query can contain more than one sub-query.

Types of Subqueries

• Single-Row subquery (subquery returns only one row)
• Multiple-Row subquery (subquery returns multiple rows)
• Multiple column subquery (subquery returns multiple columns)

Let’s look at each subquery in detail.

Single-Row Subquery

The operators that can be used with single-row subqueires are =, >, >=, <, <=, and <>.

For example, the following statement retrieves the details of the employee holding the highest salary.

SELECT 	*
FROM
Markets
WHERE
Profit = (SELECT 	MIN(Profit) 
  			   FROM	
Markets
  			  )

Having-clause can also be used with this type of subquery.

For example, the following statement returns all market regions where the minimum Profit is more than the minimum profit in the market region X.

SELECT 	market_region, 
MIN(Profit)
FROM
Markets
GROUP BY
market_region
HAVING
MIN(Profit) > (SELECT 	MIN(Profit) 
        				 FROM
Markets 
        				 WHERE
market_region = ‘X’
)

Multiple-Row Subquery

If you observe the below statement, it is totally wrong, because the operator “=” cannot be used with subquery returns multiple rows.

SELECT 	Profit, 
market_region 
FROM
Markets
WHERE
Profit > (SELECT 	MIN(Profit)
   FROM
   		Markets
   GROUP BY
market_region
  )

Operators that can be used with multiple-row subqueries are:

1. IN (equal to any member in the list)
2. ANY (compare values to each value returned by the subquery)

For example, the following statement finds the market_regions where profit is the same as the minimum profit of the some other market_region.

SELECT 	market_regions,
profit 
FROM
Markets
WHERE
Profit IN (SELECT 	MIN(Profit) 
        			     FROM
Markets 
        		 	     GROUP BY
market_regions
     			    )

For example, the following statement finds market where profit is more than the minimum profit has been generated in any market_regions.

SELECT 	Profit,
market_regions 
FROM
employee
WHERE
Profit > ANY (SELECT 	MIN(Profit) 
        				FROM
Markets 
        				GROUP BY
market_regions)

Multiple-Column Subquery

In multiple-column subqueries, rows in the subquery results are obtained in the main query in pair-wise comparison. I meant, column-to-column comparison and row-to-row comparison.

For example, the following statement lists all purchased items whose quantity and product id match to an item of order_id “N3621”.

SELECT 	order_id, 
product_id,
qty
FROM
order_table
WHERE
(product_id, qty) IN (SELECT 	product_id, 
quantity 
FROM 
order_table
WHERE
order_id = “N3621”
)
    		AND
order_id = N3621

(Note: You can put a subquery in the FROM clause in your main query)

For example, the following SQL query finds the list of all employees in each department where their salary is beyond the average.

 SELECT 	e1.emp_name, 
e1.emp_sal, 
e1.dept_id, 
emp_salary_ave
 FROM
employee e1
               	(SELECT 	dept_id,
 AVE(emp_sal) salary_ave
              	 FROM
employee  e2
                	 GROUP BY
dept_id
)
 WHERE
e1.dept_id = e2.dept_id
        		AND
e1.emp_sal > e2.salary_ave