Extracting Data Using Subqueries

Discussion in 'SQL Server' started by MinalS, Apr 18, 2016.

  1. Modified comparison operators are used for checking the values against the specific condition which are returned from the subquery..

    Modified comparison operators



    The comparison operators like =, < and > are used in a query. If the subquery returns more than one value, we need to apply the modified comparison operators in the subquery.

    The list of modified comparison operators used in SQL query are as listed below:
    1. > ANY: It is used for accessing the values which are greater than minimum values present in the list.
    2. > ALL: Accessing the values which are greater than maximum values present in the list
    3. <> ANY: It defines the values which are not equal to any values present in the list
    4. <> ALL: It states not equal to all values present in the list
    5. = ANY: It describes any of the values present in the list
    Example to demonstrate the use of modified comparison operators.
    Code:
    select studid, marks from student where marks > ALL ( select marks from student where studname = ‘Jill’)
    
    Output:
    [​IMG]
    Code:
    select * from academics where marks > ANY ( select marks from academics where name = ‘Peter’ )
    
    Output:
    [​IMG]

    IN keyword



    In SQL, if a subquery returns more than one value, user needs to find the value similar to any values in the list which are returned by the inner query. The IN keyword is used for this purpose.

    Syntax:
    Code:
    SELECT column_list FROM tablename
    WHERE column [ NOT ] IN ( select column from tablename [ WHERE condition_expression ] )
    
    Example:
    Code:
    select s.studid, s.marks from student As s join academics As a on s.marks = a.marks where a.location IN 
    ( ‘Wales’, ‘London’)
    
    Output:
    [​IMG]

    EXISTS keyword



    The subquery is used for verifying the valid records in the given set. The EXISTS keyword always provides the value as either TRUE or FALSE. The clause is used for checking the rows matching the specific condition present in the inner query and the value is passed to the outer query.

    The inner query is used for returning the result set from the table. Hence, these queries are known as table valued queries. The EXISTS keyword returns a TRUE value if the inner query consists of row.

    Syntax:

    Code:
    SELECT column_list FROM tablename WHERE EXISTS ( select column FROM tablename 
    [ WHERE expression ] )
    
    Example:

    Code:
    select SUM ( marks ) as total from academics where exists ( select * from student where studid = 301 )
    
    Output:
    [​IMG]

    Nested and correlated subqueries



    A subquery consists of more than one subqueries. When a query is dependent on the value of another query, subqueries are used. The subquery can be nested into various levels. The query can be nested upto 32 levels. The number of levels which can be nested are dependent on the database server memory.

    Example:

    Code:
    select productid from productdata where location = ( select location from orderdata where orderid = 321 )
    
    Output:
    [​IMG]

    Correlated subquery is used for defining a query which is dependent on the outer query for its execution. In a correlated subquery, the output of the inner query is dependent on the outer query result. The inner query is executed only once for a row which is provided by the outer query.

    Example:

    Code:
    select * from orderdata o where totalcost = ( select MIN ( totalcost ) from orderdata where orderid = o.orderid )
    
    Output:

    [​IMG]

    APPLY operator



    The APPLY operator, much similar to the join clause is used for combining the two different table expressions. The operator is used when user needs the table valued expression on the right side, and the expression is to be checked for every row from the left table expression.

    There are two type of APPLY operator as OUTER APPLY and CROSS APPLY. The CROSS APPLY is used for returning the rows from the left table expression if they are similar to the right table expression. The OUTER APPLY operator returns all the rows from the left expression irrespective of the match with the right expression.

    CROSS APPLY

    The CROSS APPLY returns only the rows which are matching with the inner result set.

    Example:
    Code:
    select o.orderid, o.quantity, pr.ProductID FROM orderdata o CROSS APPLY (
    select * from productdata p where o.quantity = p.quantity ) pr
    
    Output:

    [​IMG]

    OUTER APPLY

    The OUTER APPLY operator is used for returning the rows from the outer result set even when the rows are not present in the inner result. A NULL value is returned for the not matching rows.

    Code:
    select o.orderid, o.quantity, pr.ProductID FROM orderdata o OUTER APPLY 
    ( select * from productdata p where o.orderid = p.price ) pr
    
    Output:

    [​IMG]

    EXCEPT and INTERSECT operators



    The EXCEPT operator is used for comparing the two result sets and returning the data from the first set and not present in the second set. The order and number of columns must be the same in the result. The INTERSECT operator is used for returning the rows which are common from both the tables.

    Code:
    <expression> EXCEPT | INTERSECT <expression>
    
    Example:

    Code:
    select * from manufacturing EXCEPT select * from orderdata
    
    Output:

    [​IMG]

    Common Table expressions



    In SQL, the result sets returned after executing the query are known as temporary result sets. The result sets are not saved in the database but are available in the memory till the execution. The tables is dropped once the query is executed.

    CTE is used for creating the temporary result set. The syntax for CTE is as mentioned below:

    Code:
    WITH expression [ column_list ] AS ( CTE query_expression)
    
    Example:

    Code:
    WITH DemoCTE ( quantity ) 
    As
    ( 
    Select Top 3 quantity = ( select MAX ( quantity ) From DemoCTE ) 
    )
    Select quantity, MAX_quantity = ( select MAX ( quantity ) From DemoCTE) 
    From DemoCTE
    
    There is a need when user needs CTE frequently. These queries are known as recursive CTE. A recursive CTE contains the following members for creating the required result set.
    1. Anchor query: More than one query are joined using UNION, UNION ALL, INTERSECT or EXCEPT operators.
    2. Recursive query: More than one query expressions are joined using the UNION ALL operators. The result of the anchor query is accepted as the input.
    Syntax:
    Code:
    WITH expression ( column_list )
    As
    (
    ( CTE_query_definition )
    UNION ALL
    ( recursive_query_expression )
    )
    
    Example:

    Code:
    WITH Demo_CTE ( i )
    As
    (
        SELECT i = CONVERT ( VARCHAR ( 4000 ) , ‘Learn’ )
        UNION ALL
        SELECT i + ‘X’ FROM Demo_CTE where LEN ( i ) < 4
    )
    Select i FROM Demo_CTE
    ORDER BY i
    
     
    Last edited by a moderator: Jan 21, 2017

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice