SqlTutorial-1 : Lesson-7 : Class-1 Microsoft SQL Server Training: Free Online-Learning Classes (OLC) for T-SQL Sql-SubQueries: Nested and Co-related

A subquery is a Sub-SELECT nested inside another query. // OR //
Defining a SELECT query within another query is called nested query.

INDEX

1. Introduction
   
1. What are Sub-Queries
2. Restrictions
2. Nested Sub-Queries
   1. Sub-Queries in SELECT(Inline-Views)
   2. Sub-Queires in FROM-clause (Inline-Views)
   3. Sub-Queries in WHERE-Clause
3. Co-related Sub-Queries
   1. Cor-related Vs Nested
   2. Finding N-Max examples

1. Introduction

1.1: What is Sub-Query?

A Subquery also called INNER QUERY / INNER SELECT / SUB-SELECT.

1. The Parent-query i.e, outer-query can be any DML statement but child-query(sub-query) must be only SELECT-statement. For example, an UPDATE statement or INSERT-statement or DELETE-statement can contain SELECT-statement, but reverse is not possible. 
2. You can write sub-query in SELECT-clause, FROM-clause and WHERE clause. 
3. Defining sub-query followed by SELECT and FROM clauses is said to be in-line view. Hence, there are 3 types of sub-queries.
   
   • In-line view queries (Derived Table) 
   • Normal Sub-queries 
   • Co-related sub-queries.
4. A sub-query is subject to the following restrictions:
   
   • A view created by using sub-query cannot be updated. 
   • If sub-query returns single value, then you can compare it with comparison operator. Otherwise (returning multiple values) use ANY, SOME, ALL, IN.
   • If sub-query is used with comparison operator, it must include only one column name/expression(except that EXISTS and IN operate on SELECT *) 
   • The COMPUTE, ORDER BY and INTO clauses cannot be specified in sub-query. 
   • The ntext, text, and image type columns cannot be included into the subqueries.

1.2: Restrictions

A subquery have following restriction:

1. A Sub-Query cannot use DISTINCT key if it includes GROUP-BY
2. A Sub-Query cannot use COMPUTE and INTO clauses
3. A Sub-query can use ORDER-BY if it also have TOP()
4. A Sub-query generated view is cannot be Updated.
5. A Sub-query cannot includes the columns of type: NTEXT, TEXT, and IMAGE.
6. A Sub-query should be compared with using:
   1. ANY, SOME, or ALL
   2. EXISTS or NOT EXISTS

2.Nested Sub-Queries

2.1. Sub-queries in SELECT clause

Examples:

1. Getting Max and Min salaries of employees simultaneously.

   SELECT (SELECT MAX(col1) FROM T1) AS Maximum ,
                (SELECT MIN(col1) FROM T1) AS Minimum

2. Doing calculation with Sub-query
   Find how meny employess are there those salary is less than maximum salary.

   SELECT (Select Max(salary) from Employee) - Salary
   FROM Employee

2.2.Sub-Queires in FROM-clause (Inline-Views)

Examples:

1. Simple in-line view ( Creating a derived table).

   SELECT *
   FROM (SELECT 'Fred' As FirstName, 'Flintstone' As LastName)

2. Getting Maximum value of multiple tables(Complex in-line view)

   SELECT *
   FROM (SELECT 'Fred' As FirstName, 'Flintstone' As LastName)

3. Performing calculations on inline-view

   SELECT col1+col2 as Total
   FROM (SELECT M1 as col1, M2 as col2 FROM table1)

4. In-line view with JOIN operation.

   SELECT *
   FROM Table1 t1
       INNER JOIN (Select * from Table2) t2 ON t1.col1 = t2.col1

Equals to

SELECT * FROM Table1 t1 INNER JOIN Table2 t2 ON t1.col1=t2.col1



5. Store the result of a JOIN operation in new table (SELECT INTO + Derived Table)

   SELECT *
   INTO newJoinTable
   FROM (SELECT * FROM T1 INNER JOIN T2 ON T1.col1 = T2.col1) AS T

6. Finding the employee who is getting Maximum salary using JOIN.

   SELECT *
   FROM Employee e
        INNER JOIN (Select Max(salary) as m From Employee) M
             ON e.salary = M.m

7. Find whether or not two columns of a table are equal.

   Step-1: In the In-line view compare the two columns of the table(s) returns true or false.
   Step-2: If you get all ‘true’ then the columns are equal otherwise un-equal.
   
   
   SELECT CASE
            WHEN 'true'=ALL (
                     SELECT CASE WHEN (col1 = col3)
                             THEN 'true'
                             ELSE 'false'
                          END
             FROM Table
            )
           THEN 'equals'
            ELSE 'unequal'
      END
   AS Result

2.3: Sub-Queries in WHERE-Clause

Generally, subquery take one of these formats:

• WHERE expr [NOT] in (Subquery)
• WHERE expr comp_operator [ANY|ANY|SOME|ALL](Subquery)
• WHERE [NOT] exists(subquery)

Examples:

1. Find name of employee getting maximum salary. (SELECT + SELECT)

   SELECT ename FROM Employee WHERE salary =(SELECT MAX (salary) FROM Employee)

2. Comparing with operators

   If Sub-query returns single value, then you can compare it with =, !=, <. <= etc. But when sub-query returns multiple values, use SOME, ANY, ALL, IN operators to perform comparisons.
   SELECT * FROM employee WHERE id IN(SELECT id FROM title)

   Finding Maximum of salary without using MAX() function.

   SELECT Salary FROM Employee where salary >=All(select salary from Employee)

3. Inserting values from existing table (INSERT + SELECT)

   -- Copying all columns
    INSERT INTO T5 Select * from T4
   -- Copying selected columns
    INSERT INTO T5(col1,col2) SELECT col1, col2 FROM T4

4. Deleting highest salaried employee (SELECT + DELETE)

   DELETE FROM Employee WHERE salary=(select Max(salary) from Employee)

5. Interchange the Max salary and Mini salary. (SELECT + UPDATE)

   UPDATE Employee
    SET salary=CASE
   

             WHEN salary=(select Max(salary) FROM Employee)

                THEN (select Min(salary) FROM Employee) 

             WHEN salary=(select Min(salary) FROM Employee) 

               THEN (select Max(salary) FROM Employee)

    ELSE salary END

6. Multiple levels of Nested queries is possible.

   SELECT * FROM Employee
   

    WHERE ID IN ( SELECT ID FROM Title 

    WHERE ID IN (SELECT id FROM Employee 

   WHERE Start_Date > '3-1-2003') )

3. Co-Related Sub-Queries

3.1: Co-Related Sub-queries Execution

1. First Outer query executes and submit values to the inner query.
2. Then, Inner query executes by using value returned by outer-query.
3. The condition applied on outer query checked.

The mapping of iterations
1 MainQuery : N Sub-query : 1 Main query Condition
1 MainQuery : N Sub-query : 1 Main query Condition.. M times.

3.2: Example: Find the N-Maximum of salary

1. 1st Maximum.

   SELECT Salary
   FROM Employee E1
   WHERE 0 = (SELECT COUNT(*)    --Read as “Selected value is less than 0 no. elements” 
              FROM Employee E2
              WHERE E1.salary <E2.Salary)

2. 2nd Maximum.

   SELECT Salary
   FROM Employee E1
   WHERE 1= (SELECT COUNT(*) --Read as “Selected value is less than 1 no. elements”
             FROM Employee E2
             WHERE E1.salary <E2.Salary)

3. Generic Nth Maximum

   SELECT Salary
   FROM Employee E1
   WHERE N-1 = (SELECT COUNT(*)
                FROM Employee E2
                WHERE E1.salary <E2.Salary)

4. Subqueries vs Correlated Subqueries:
    Difference between Subquery and correlated subquery

The main difference between Normal Sub-query and Co-related sub-query are:

• Looping

  Co-related sub-query loop under main-query; whereas normal sub-query; therefore correlated Subquery executes on each iteration of main query. Whereas in case of Nested-query; Subquery executes first then outer query executes next. Hence, the maximum no. of executes are NXM for correlated subquery and N+M for subquery.

• Execution:

  Correlated uses feedback from outer query for execution whereas Nested Subquery provides feedback to Outerquery for execution. Hence, Correlated Subquery depends on outer query whereas Nested Sub-query does not.

• Performance:

  Using Co-related sub-query performance decreases, since, it performs NXM iterations instead of N+M iterations. ¨ Co-related Sub-query Execution.