Exists and Not Exists Operators

     

  • Inner Query returns Boolean Value

    •  

  • If Boolean value is True executes Outer Query else Outer Query doesn't executes

    •  

     

    Syntax:

     

    Exists Operator with Greater Than

     

  • As PRICE column consists of Value as 22, Outer query returned result set

    •  

    Not Exists Operator with Greater Than

     

  • As Price column consists of value as 22, Inner query returns result as FALSE
  • Hence Outer query not executed

    •  

     

    Exists Operator with Less Than

     

  • As Price Less than doesn't exists in PRICE column, query returned FALSE vlaue.
  • Hence Outer Query not executed

    •  

    Not Exists Operator with Less Than

     

     

    Derived Table - From Clause

     

  • Sub Queries followed FROM clause is termed as DERIVED TABLES

    •  

     

     

     

     

    Scalar_Subquery_Select_Clause

     

     

     

    Nested SubQuery

     

     

     

     

    SubQuery Combinations

     

     

    Select - Update:

     

     

    Select - Delete:

     

     

    Select - Insert:

     

    Correlated(Co-related) Sub Query

     

  • Also termed as REPEATING SUB-QUERY
  • Execution process starts from OUTER QUERY

    •  

    Sub Queries are Uni-directional as execution starts from Inner – Outer Query

     

    Correlated Sub-Queries are Bi-Directional as execution starts from Outer – Inner Query

     

     

  • A Query is Co-related Sub-Queriy when Outer Query table name is provided with alias name and that alias name is used in Inner Query.

    •