What are the Different types of Subqueries in Oracle?

Subqueries in Oracle

A subquery is a SELECT statement that is included inside another SQL statement. It is sometimes referred to as an inner query or nested query. SELECT, INSERT, UPDATE, or DELETE are all possible options for the outer SQL query. Giving the parent (outer) query a set of rows or a single value is the main purpose of a subquery. Usually, subqueries are inserted in parenthesis.

Due to Oracle Database’s continuous support of SQL standards, many SQL examples—including subqueries—are frequently applicable across several database management systems. One incredibly powerful method that doesn’t necessary require programming knowledge is the use of subqueries.

Types of Subqueries in Oracle

Subqueries can be roughly classified according to their link to the outer query and the number of rows they return.

Single-Row Subqueries

When a subquery returns a maximum of one row to the outer query, it is said to be single-row. Examples of single-value comparison operators that can be used with this kind of subquery are =, >, <, >=, <=, and <> (not equal to). The query will fail with an error if a single-row subquery produces more than one row.

To demonstrate, let’s look at a simple NEWSPAPER table:

CREATE TABLE NEWSPAPER (
    Feature VARCHAR2(15),
    Section CHAR(1),
    Page NUMBER
);

INSERT INTO NEWSPAPER VALUES ('National News', 'A', 1);
INSERT INTO NEWSPAPER VALUES ('Sports', 'D', 1);
INSERT INTO NEWSPAPER VALUES ('Editorials', 'A', 12);
INSERT INTO NEWSPAPER VALUES ('Business', 'E', 1);
INSERT INTO NEWSPAPER VALUES ('Weather', 'C', 2);
INSERT INTO NEWSPAPER VALUES ('Doctor Is In', 'F', 6);

Example: Find all features that appear on the same page as ‘Weather’.

SELECT Feature, Section, Page
FROM NEWSPAPER
WHERE Page = (SELECT Page FROM NEWSPAPER WHERE Feature = 'Weather');

Output:

FEATURE	S	PAGE
Weather	C	2

In this case, the subquery (SELECT Page FROM NEWSPAPER WHERE Feature = 'Weather') returns a single value (2), which the outer query then uses for comparison.

Multi-Row Subqueries

The outer query can receive zero, one, or more rows back from multi-row subqueries. They need to be used with multi-value operators like IN, NOT IN, ANY, ALL, EXISTS, and NOT EXISTS since they can return several rows.

Let’s prepare a few tables for these instances:

CREATE TABLE CATEGORY (
    CategoryName VARCHAR2(20) PRIMARY KEY,
    ParentCategory VARCHAR2(20),
    SubCategory VARCHAR2(20)
);

INSERT INTO CATEGORY VALUES ('ADULTFIC', 'ADULT', 'FICTION');
INSERT INTO CATEGORY VALUES ('ADULTNF', 'ADULT', 'NONFICTION');
INSERT INTO CATEGORY VALUES ('ADULTREF', 'ADULT', 'REFERENCE');
INSERT INTO CATEGORY VALUES ('CHILDRENFIC', 'CHILDREN', 'FICTION');
INSERT INTO CATEGORY VALUES ('CHILDRENNF', 'CHILDREN', 'NONFICTION');
INSERT INTO CATEGORY VALUES ('CHILDRENPIC', 'CHILDREN', 'PICTURE BOOK');

CREATE TABLE BOOKSHELF (
    Title VARCHAR2(100) PRIMARY KEY,
    Publisher VARCHAR2(20),
    CategoryName VARCHAR2(20),
    Rating VARCHAR2(2),
    CONSTRAINT CATFK FOREIGN KEY (CategoryName) REFERENCES CATEGORY(CategoryName)
);

INSERT INTO BOOKSHELF VALUES ('The Great Novel', 'Penguin', 'ADULTFIC', '4');
INSERT INTO BOOKSHELF VALUES ('Science Facts', 'Harper', 'ADULTNF', '5');
INSERT INTO BOOKSHELF VALUES ('Kids Adventure', 'Scholastic', 'CHILDRENFIC', '3');
INSERT INTO BOOKSHELF VALUES ('Cookbook Basics', 'Penguin', 'ADULTREF', '4');
INSERT INTO BOOKSHELF VALUES ('Little Bear', 'Harper', 'CHILDRENPIC', '2');
INSERT INTO BOOKSHELF VALUES ('History of Australia', 'LocalPub', 'ADULTNF', '5');

CREATE TABLE BOOKSHELF_CHECKOUT (
    Title VARCHAR2(100),
    Name VARCHAR2(50),
    CheckoutDate DATE,
    ReturnedDate DATE,
    PRIMARY KEY (Title, Name),
    CONSTRAINT TitleFK FOREIGN KEY (Title) REFERENCES BOOKSHELF(Title)
);

INSERT INTO BOOKSHELF_CHECKOUT VALUES ('The Great Novel', 'John Doe', DATE '2023-01-01', DATE '2023-01-15');
INSERT INTO BOOKSHELF_CHECKOUT VALUES ('Science Facts', 'Jane Smith', DATE '2023-02-10', DATE '2023-02-28');
INSERT INTO BOOKSHELF_CHECKOUT VALUES ('Kids Adventure', 'John Doe', DATE '2023-03-01', NULL);

IN and NOT IN Operators

The IN operator determines if the list that the subquery returns contains a value. NOT IN verifies that there isn’t a value.

Example (IN): Find all categories that have at least one book checked out.

SELECT DISTINCT C.ParentCategory, C.SubCategory
FROM CATEGORY C
WHERE C.CategoryName IN (SELECT B.CategoryName
                         FROM BOOKSHELF B
                         WHERE B.Title IN (SELECT BC.Title FROM BOOKSHELF_CHECKOUT BC));

Output:

PARENTCA	SUBCATEGORY
ADULT	FICTION
ADULT	NONFICTION
CHILDREN	FICTION

Example (NOT IN): Find all books that have not been checked out.

SELECT Title
FROM BOOKSHELF
WHERE Title NOT IN (SELECT Title FROM BOOKSHELF_CHECKOUT);

Output:

TITLE

Cookbook Basics

Little Bear

History of Australia

ANY and ALL Operators

Comparison operators are used with these operators. ANY denotes “greater than at least one” or “less than at least one” of the values found in the result set of the subquery. A value that is “greater than all” or “less than all” is called ALL. Although they might be rather intuitive, they can also be challenging at times and an EXISTS form are sometimes used instead.

Example (ANY): Find features in NEWSPAPER that are on a page number greater than any page in Section ‘A’. This means the page number is greater than 1 OR greater than 12. So, any page greater than 1 will satisfy this.

SELECT Feature, Page
FROM NEWSPAPER
WHERE Page > ANY (SELECT Page FROM NEWSPAPER WHERE Section = 'A');

Output:

FEATURE	PAGE
Editorials	12
Weather	2
Doctor Is In	6
Births	7
Classified	8

Example (ALL): Find features in NEWSPAPER that are on a page number greater than all pages in Section ‘A’. This means the page number must be greater than both 1 AND 12.

SELECT Feature, Page
FROM NEWSPAPER
WHERE Page > ALL (SELECT Page FROM NEWSPAPER WHERE Section = 'A');

Output:

No rows selected.

(There are no pages greater than 12 in our sample NEWSPAPER table.)

EXISTS and NOT EXISTS Operators

The presence of the rows that the subquery returns is checked using the EXISTS operator. The EXISTS condition is true if the subquery yields any rows. If the subquery produces no records, then NOT EXISTS is true. These frequently accompany associated subqueries.

Example (EXISTS): Find all categories that have books associated with them in the BOOKSHELF table.

SELECT C.CategoryName
FROM CATEGORY C
WHERE EXISTS (SELECT 1 FROM BOOKSHELF B WHERE B.CategoryName = C.CategoryName);

Output:

CATEGORYNAME

ADULTFIC

ADULTNF

ADULTREF

CHILDRENFIC

CHILDRENPIC

Example (NOT EXISTS): Find all categories that currently have no books in the BOOKSHELF table.

SELECT C.CategoryName
FROM CATEGORY C
WHERE NOT EXISTS (SELECT 1 FROM BOOKSHELF B WHERE B.CategoryName = C.CategoryName);

Output:

CATEGORYNAME

CHILDRENNF

Correlated Subqueries

A correlated subquery is one that uses its primary (outer) query to refer to a column in a table. For every row that the parent statement processes, a correlated subquery is re-evaluated, in contrast to other subqueries that are run once and then give the outer query a result set. For row-by-row comparisons or computations that rely on the particular row that the outer query is processing, this makes them extremely potent.

Example: Find all books in the BOOKSHELF table that have a Rating higher than the average Rating for their respective CategoryName. (Note: We’re using Rating as a numeric value for this example, assuming ‘5’ is higher than ‘4’, etc.).

-- Assuming Rating can be implicitly converted or is numeric
SELECT B1.Title, B1.Rating, B1.CategoryName
FROM BOOKSHELF B1
WHERE B1.Rating > (SELECT AVG(TO_NUMBER(B2.Rating))
                   FROM BOOKSHELF B2
                   WHERE B2.CategoryName = B1.CategoryName);

Output:

TITLE	R	CATEGORYNAME
Science Facts	5	ADULTNF
History of Australia	5	ADULTNF

In this example, the inner query (SELECT AVG(TO_NUMBER(B2.Rating)) FROM BOOKSHELF B2 WHERE B2.CategoryName = B1.CategoryName) is executed for each row of BOOKSHELF B1. It calculates the average rating for that specific category (B1.CategoryName) and then compares it to the rating of the current book (B1.Rating).

Placement of Subqueries

Subqueries can be positioned in several SQL statement clauses, each of which has a unique function.

WHERE Clause

Subqueries are most frequently placed here. The aforementioned examples demonstrate how subqueries in the WHERE clause are used to filter rows according to a criterion that is dependent on the inner query’s outcome.

FROM Clause (Inline Views)

A subquery that is positioned inside the FROM clause is called an inline view. The outer query can choose from it since it is regarded as a temporary, named table. This is helpful for streamlining intricate connections or pre-aggregating data.

Example: Calculate the percentage of total books that each category represents.

SELECT
    C.CategoryName,
    CategoryCount.CountPerCat,
    (CategoryCount.CountPerCat / TotalBooks.Total) * 100 AS Percentage
FROM
    CATEGORY C
JOIN
    (SELECT CategoryName, COUNT(*) AS CountPerCat FROM BOOKSHELF GROUP BY CategoryName) CategoryCount
ON C.CategoryName = CategoryCount.CategoryName
JOIN
    (SELECT COUNT(*) AS Total FROM BOOKSHELF) TotalBooks ON 1=1; -- Joining with a constant to get total books for all categories.

Output:

CATEGORYNAME	COUNTPERCAT	PERCENTAGE
ADULTFIC	1	20
ADULTNF	2	40
ADULTREF	1	20
CHILDRENFIC	1	20
CHILDRENPIC	1	20

In this case, CategoryCount and TotalBooks are inline views that supply aggregated data for use in the main query. Additionally suggests using inline views to circumvent the need to create actual views for some computations.

SELECT Clause (Scalar Subquery Expressions)

The term “scalar subquery expression” refers to a subquery in the SELECT clause. For each row processed by the outer query, it must return a maximum of one row and one column. There is an issue if it returns more than one row. NULL is the outcome if it yields no rows.

Example: List each book and the total number of books in its category.

SELECT
    B.Title,
    B.CategoryName,
    (SELECT COUNT(*) FROM BOOKSHELF WHERE CategoryName = B.CategoryName) AS BooksInCategory
FROM
    BOOKSHELF B;

Output:

TITLE	CATEGORYNAME	BOOKSINCAT
The Great Novel	ADULTFIC	1
Science Facts	ADULTNF	2
Kids Adventure	CHILDRENFIC	1
Cookbook Basics	ADULTREF	1
Little Bear	CHILDRENPIC	1
History of Australia	ADULTNF	2

In this example, (SELECT COUNT(*) FROM BOOKSHELF WHERE CategoryName = B.CategoryName) is a scalar subquery executed for each row of BOOKSHELF, providing the count of books within that specific category.

Important Considerations

• Nesting Depth: Subqueries in Oracle databases can be extensively nestled. The WHERE clause allows for the nesting of up to 255 layers of subqueries. Nonetheless, it is generally recommended to keep things as straightforward as possible in order to prevent complex reasoning and preserve readability.
• Performance: Subqueries, particularly correlated subqueries that re-execute for every outer row, can occasionally affect query performance despite their power. Oracle’s optimiser frequently looks for more effective access paths by attempting to “unnest” subqueries and combine them with the primary query. Seeking advice from a database administrator (DBA) to optimise statements using subquery processing is a smart idea for complex queries or enormous data volumes.
• DML Statements: INSERT, UPDATE, and DELETE statements can all use subqueries, not only SELECT statements. For instance, one functionality is INSERT USING SUBQUERIES.

Knowing how to use subqueries is like having a Swiss Army knife in your SQL toolbox, buddy. Their ability to deconstruct complicated data retrieval challenges into smaller, more manageable chunks improves your capacity to work with the database efficiently and retrieve the precise information you require.