What is a nested query in Oracle?

A subquery in the WHERE clause of a SELECT statement is also called a nested subquery. A subquery can contain another subquery. Oracle Database imposes no limit on the number of subquery levels in the FROM clause of the top-level query. You can nest up to 255 levels of subqueries in the WHERE clause.

What is subquery in Oracle with example?

A subquery is a SELECT statement nested inside another statement such as SELECT , INSERT , UPDATE , or DELETE . Typically, you can use a subquery anywhere that you use an expression. Consider this following subquery example that uses the products table from the sample database.

What are different types of sub queries?

Types of Subqueries

  • Single Row Sub Query: Sub query which returns single row output.
  • Multiple row sub query: Sub query returning multiple row output.
  • Correlated Sub Query: Correlated subqueries depend on data provided by the outer query.

How do nested queries work?

A subquery, also known as a nested query or subselect, is a SELECT query embedded within the WHERE or HAVING clause of another SQL query. The data returned by the subquery is used by the outer statement in the same way a literal value would be used.

How many nest subqueries are allowed?

A subquery can be nested inside the WHERE or HAVING clause of an outer SELECT , INSERT , UPDATE , or DELETE statement, or inside another subquery. Up to 32 levels of nesting is possible, although the limit varies based on available memory and the complexity of other expressions in the query.

What is the difference between correlated and uncorrelated subquery?

A correlated subquery can be thought of as a filter on the table that it refers to, as if the subquery were evaluated on each row of the table in the outer query. An uncorrelated subquery has no such external column references.

How many types of subquery are there in Oracle?

There are three broad types of a subquery in SQL. This chapter from OCA Oracle Database 11g: SQL Fundamentals I Exam Guide explains differences between a single-row subquery, multiple-row subquery and correlated subquery .