In this example:

  • The outer query selects the customer_name from the customers table.
  • The inner subquery calculates the total number of orders for each customer by counting the rows in the orders table where the customer_id matches between the two tables.
  • The result is a list of customer names along with the corresponding total number of orders.


Use subqueries for the following purposes:

  • To define the set of rows to be inserted into the target table of an INSERT or CREATE TABLE statement
  • To define the set of rows to be included in a view or materialized view in a CREATE VIEW or CREATE MATERIALIZED VIEW statement
  • To define one or more values to be assigned to existing rows in an UPDATE statement
  • To provide values for conditions in a WHERE clause, HAVING clause, or START WITH clause of SELECTUPDATE, and DELETE statements
  • To define a table to be operated on by a containing queryYou do this by placing the subquery in the FROM clause of the containing query as you would a table name. You may use subqueries in place of tables in this way as well in INSERTUPDATE, and DELETE statements.Subqueries so used can employ correlation variables, both defined within the subquery itself and those defined in query blocks containing the subquery. Refer to table_collection_expression for more information.Scalar subqueries, which return a single column value from a single row, are a valid form of expression. You can use scalar subquery expressions in most of the places where expr is called for in syntax. Refer to “Scalar Subquery Expressions” for more information.

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top