Where can we find sub-query within another statement?
Explanation
All of the clauses provided may contain sub-queries within them.
Subqueries can be used with the SELECT, INSERT, UPDATE, and DELETE statements along with the operators like =, <, >, >=, <=, IN, BETWEEN, etc.
Theory
  • SQL - Sub Queries

    A Subquery or Inner query or Nested query is a query within another SQL query and embedded within the WHERE clause.
    A subquery is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved.
    Subqueries can be used with the SELECT, INSERT, UPDATE, and DELETE statements along with the operators like =, <, >, >=, <=, IN, BETWEEN etc.

    Example

    SELECT sub.*
      FROM (
            SELECT *
              FROM tutorial.sf_crime_incidents_2014_01
             WHERE day_of_week = 'Friday'
           ) sub
     WHERE sub.resolution = 'NONE'
     
    Let’s break down what happens:
    First, the database runs the “inner query” — the part between the parentheses:
    SELECT *
      FROM tutorial.sf_crime_incidents_2014_01
     WHERE day_of_week = 'Friday'
    
    Read more: SQL - Sub Queries
  • Rules that Sub Queries must follow

    • Subqueries must be enclosed within parentheses.
    • A subquery can have only one column in the SELECT clause, unless multiple columns are in the main query for the subquery to compare its selected columns.
    • An ORDER BY cannot be used in a subquery, although the main query can use an ORDER BY. The GROUP BY can be used to perform the same function as the ORDER BY in a subquery.
    • Subqueries that return more than one row can only be used with multiple value operators, such as the IN operator.
    • The SELECT list cannot include any references to values that evaluate to a BLOB, ARRAY, CLOB, or NCLOB.
    • A subquery cannot be immediately enclosed in a set function.
    • The BETWEEN operator cannot be used with a subquery; however, the BETWEEN operator can be used within the subquery.

    Read more: Sub Queries

Follow CodeGalaxy

Mobile Beta

Get it on Google Play
Send Feedback
Keep exploring
SQL quizzes
Cosmo
Sign Up Now
or Subscribe for future quizzes