SQL: Single Row Subqueries

Last update on April 13 2024 13:04:11 (UTC/GMT +8 hours)

Single Row Subqueries

A single row subquery returns zero or one row to the outer SQL statement. You can place a subquery in a WHERE clause, a HAVING clause, or a FROM clause of a SELECT statement.

Contents:

Single Row SubQueries in WHERE clause
Using comparison operators in Single Row subqueries
Subqueries in a HAVING clause
Subqueries in a FROM clause
Error in Single Row Subqueries

Single Row Subqueries in WHERE clause

You can place a subquery in the WHERE clause of another query. Let's take an example of a query that contains a subquery placed in it's WHERE clause.

Sample table: agents

SQL Code:


-- Retrieve the 'agent_name', 'agent_code', and 'phone_no' columns from the 'agents' table.
SELECT agent_name, agent_code, phone_no
-- Specify the columns to be retrieved.
FROM agents
-- Specify the table from which data will be retrieved, which is 'agents'.
WHERE agent_code = 
-- Filter the rows where the 'agent_code' is equal to
(SELECT agent_code 
-- Retrieve the 'agent_code' column from the 'agents' table.
FROM agents
-- Specify the table from which data will be retrieved for the subquery, which is 'agents'.
WHERE agent_name = 'Alex');
-- Condition: Filter the 'agent_code' based on the 'agent_name' being 'Alex'.

Explanation:

This SQL code is a SELECT statement used to retrieve data from the 'agents' table based on certain conditions.

The purpose of this code seems to be fetching information about an agent named Alex.

The SELECT statement specifies the columns to be retrieved: 'agent_name', 'agent_code', and 'phone_no'.

FROM clause specifies the table from which data will be retrieved, which is 'agents'.

The WHERE clause filters the rows based on a condition. In this case, it retrieves rows where the 'agent_code' is equal to the result of a subquery.

The subquery (SELECT agent_code FROM agents WHERE agent_name = 'Alex') retrieves the 'agent_code' for the agent named 'Alex' from the 'agents' table. This subquery is used to filter rows based on the 'agent_code' associated with 'Alex'.

Output:

AGENT_NAME                               AGENT_CODE PHONE_NO
---------------------------------------- ---------- ---------------
Alex                                     A003       075-12458969

SQL Code:


-- Retrieve the 'agent_code' column from the 'agents' table.
SELECT agent_code 
-- Specify the column to be retrieved.
FROM agents
-- Specify the table from which data will be retrieved, which is 'agents'.
WHERE agent_name = 'Alex';
-- Condition: Filter the rows where the 'agent_name' is 'Alex'.

Explanation:

This SQL code is a SELECT statement used to retrieve data from the 'agents' table based on certain conditions.

The purpose of this code seems to be fetching the 'agent_code' for an agent named Alex.

The SELECT statement specifies the column to be retrieved: 'agent_code'.

FROM clause specifies the table from which data will be retrieved, which is 'agents'.

The WHERE clause filters the rows based on a condition. In this case, it retrieves rows where the 'agent_name' is equal to 'Alex'. This condition is used to find the specific agent whose code is needed.

Relational Algebra Expression:

Relational Algebra Tree:

Output:

AGENT_CODE
----------
A003

The simplified form of above code is :

SQL Code:


-- Retrieve the 'agent_name', 'agent_code', and 'phone_no' columns from the 'agents' table.
SELECT agent_name, agent_code, phone_no
-- Specify the columns to be retrieved.
FROM agents
-- Specify the table from which data will be retrieved, which is 'agents'.
WHERE agent_code = 'A003';
-- Condition: Filter the rows where the 'agent_code' is equal to 'A003'.

Explanation:

This SQL code is a SELECT statement used to retrieve data from the 'agents' table based on certain conditions.

The SELECT statement specifies the columns to be retrieved: 'agent_name', 'agent_code', and 'phone_no'.

FROM clause specifies the table from which data will be retrieved, which is 'agents'.

The WHERE clause filters the rows based on a condition. In this case, it retrieves rows where the 'agent_code' is equal to 'A003'. This condition is used to find the specific agent whose information is needed.

Relational Algebra Expression:

Relational Algebra Tree:

Pictorical Presentation:

SQL: Single Row Subqueries in WHERE clause.

Using comparison operators in Single Row subqueries

The previous example used the equality operator (=) in the WHERE clause. Other comparison operators such as <>, >, <, <= can be used with a single subquery. The following example uses ' <' operator in the outer query WHERE clause. The AVG() function is used in the subquery to get the average order amount, which is passed to the WHERE clause of the outer query. The final result of the entire query is to get the 'ord_num', 'ord_amount', 'ord_date', 'cust_code' and 'agent_code' with following conditions:

Condition in outer query:
the 'ord_amount' of 'orders' table must be greater than the average 'ord_amount' of 'orders' table with following condition an inner join.

Condition in inner query:
'ord_date' of 'orders' table must be '20-APR-08' for calculating the average 'ord_amount'.

Sample table: orders

Here is the complete SQL statement:

SQL Code:


-- Selecting specific columns from the 'orders' table
SELECT ord_num, ord_amount, ord_date, cust_code, agent_code
-- Filtering rows from the 'orders' table
FROM orders
-- Selecting only rows where the order amount is greater than
-- the average order amount for orders placed on 20th April 2008
WHERE ord_amount >
    -- Subquery to calculate the average order amount for orders placed on 20th April 2008
    (SELECT AVG(ord_amount)
    FROM orders
    -- Filtering rows to include only orders placed on 20th April 2008
    WHERE ord_date = '20-APR-08');

Explanation:

The SQL query is aimed at selecting specific columns (ord_num, ord_amount, ord_date, cust_code, agent_code) from the orders table.

It filters the rows from the orders table where the order amount (ord_amount) is greater than the average order amount for orders placed on April 20, 2008.

The subquery calculates the average order amount (AVG(ord_amount)) for orders placed on April 20, 2008.

It is nested within the main query and acts as a filter condition for selecting orders with amounts greater than this average.

The main query compares the ord_amount of each order with the calculated average using the WHERE clause.

Output:

  ORD_NUM ORD_AMOUNT ORD_DATE  CUST_CODE  AGENT_CODE
--------- ---------- --------- ---------- ----------
   200114       3500 15-AUG-08 C00002     A008
   200119       4000 16-SEP-08 C00007     A010
   200134       4200 25-SEP-08 C00004     A005
   200108       4000 15-FEB-08 C00008     A004
   200109       3500 30-JUL-08 C00011     A010
   200101       3000 15-JUL-08 C00001     A008
   200128       3500 20-JUL-08 C00009     A002
   200132       4000 15-AUG-08 C00013     A013
   200110       3000 15-APR-08 C00019     A010
   200107       4500 30-AUG-08 C00007     A010
   200113       4000 10-JUN-08 C00022     A002

Let's break the query and analyze what's going on in inner query. Here is the code of inner query :

SQL Code:


-- Calculating the average order amount for orders placed on April 20, 2008
SELECT AVG(ord_amount)
-- Selecting data from the 'orders' table
FROM orders
-- Filtering rows to include only orders placed on April 20, 2008
WHERE ord_date = '20-APR-08';

Explanation:

This SQL query aims to calculate the average order amount for orders placed on April 20, 2008.

It uses the AVG() function to compute the average of the ord_amount column.

The data is retrieved from the orders table.

It includes a condition in the WHERE clause to filter rows and only include orders with the date '20-APR-08'.

Relational Algebra Expression:

Relational Algebra Tree:

Output:

AVG(ORD_AMOUNT)
---------------
           2500

The simplified form of above code is:

SQL Code:


-- Selecting specific columns from the 'orders' table
SELECT ord_num, ord_amount, ord_date, cust_code, agent_code
-- Filtering rows from the 'orders' table
FROM orders
-- Selecting only rows where the order amount is greater than 2500
WHERE ord_amount > 2500;

Explanation:

This SQL query is designed to retrieve specific columns (ord_num, ord_amount, ord_date, cust_code, agent_code) from the orders table.

It filters the rows from the orders table based on a condition specified in the WHERE clause.

The condition specifies that only rows where the ord_amount column value is greater than 2500 will be included in the result set.

Relational Algebra Expression:

Relational Algebra Tree:

Pictorical Presentation:

SQL: Using comparison operators in Single Row subqueries

Subqueries in a HAVING clause

HAVING clause is used to filter groups of rows. You may place a subquery in HAVING clause in an outer query. This allows you to filter groups of rows based on the result returned by your subquery. The following example uses a subquery in the HAVING clause of the outer query. This example retrieves 'ord_amount, number of agent_codes and agent_code' from the table orders with following conditions:

- agent_code of orders table must come distinctly.
- an average of ord_amount of each group of agent_code in orders table must be equal to the average ord_amount of orders table.
- agent_code of orders table must be 'A008'.

Sample table: orders

Here is the complete SQL statement:

SQL Code:


-- Selecting the average order amount, count of agent codes, and agent code
-- from the 'orders' table
SELECT AVG(ord_amount), COUNT(agent_code), agent_code
-- Grouping the results by agent code
FROM orders
-- Filtering the results based on specific conditions after grouping
GROUP BY agent_code
-- Specifying additional conditions on grouped results
-- to filter only those where the average order amount matches
-- the average order amount for agent code 'A008'
HAVING AVG(ord_amount) =
    -- Subquery to calculate the average order amount for agent code 'A008'
    (SELECT AVG(ord_amount)
    FROM orders
    -- Filtering rows to include only orders handled by agent 'A008'
    WHERE agent_code = 'A008');

Explanation:

This SQL query aims to retrieve the average order amount, count of agent codes, and agent code from the orders table, grouped by agent code.

It utilizes the GROUP BY clause to group the results by the agent_code column.

After grouping, the HAVING clause is used to filter the grouped results based on specific conditions.

The condition specified in the HAVING clause ensures that only groups where the average order amount matches the average order amount for agent code 'A008' are included in the result set.

To calculate the average order amount for agent code 'A008', a subquery is used within the HAVING clause.

Output:

AVG(ORD_AMOUNT) COUNT(AGENT_CODE) AGENT_CODE
--------------- ----------------- ----------
           2500                 2 A011
           2500                 3 A008

Let's break the code and analyze what's going on in inner query. Here is the code of inner query:

SQL Code:


-- Selecting the average order amount
SELECT AVG(ord_amount)
-- Selecting data from the 'orders' table
FROM orders
-- Filtering rows to include only orders handled by agent 'A008'
WHERE agent_code = 'A008';

Explanation:

This SQL query aims to calculate the average order amount for orders handled by agent 'A008'.

It uses the AVG() function to compute the average of the ord_amount column.

The data is retrieved from the orders table.

It includes a condition in the WHERE clause to filter rows and only include orders handled by agent 'A008'.

Relational Algebra Expression:

Relational Algebra Tree:

Output:

AVG(ORD_AMOUNT)
---------------
           2500

The inner of the above query returns the average 'ord_amount' 2500.

The simplified form of the code is:

SQL Code:


-- Selecting the average order amount, count of agent codes, and agent code
-- from the 'orders' table
SELECT AVG(ord_amount), COUNT(agent_code), agent_code
-- Grouping the results by agent code
FROM orders
-- Filtering the results based on specific conditions after grouping
GROUP BY agent_code
-- Specifying additional conditions on grouped results
-- to filter only those where the average order amount is 2500
HAVING AVG(ord_amount) = 2500;

Explanation:

This SQL query aims to retrieve the average order amount, count of agent codes, and agent code from the orders table, grouped by agent code.

It utilizes the GROUP BY clause to group the results by the agent_code column.

After grouping, the HAVING clause is used to filter the grouped results based on a specific condition.

The condition specified in the HAVING clause ensures that only groups where the average order amount is exactly 2500 are included in the result set.

Relational Algebra Expression:

Relational Algebra Tree:

Pictorial Presentation:

Subqueries in a FROM clause

You may place a subquery in the FROM clause of an outer query. These types of subqueries are also known is inline views because the subquery provides data inline with the FROM clause. The following example retrieves the item_id whose item_id is less than 4.

Sample table: foods

Here is the SQL statement:

SQL Code:


-- Selecting the item_id column
SELECT item_id
-- Selecting data from a derived table (subquery)
FROM  
    -- Subquery to retrieve item_id from the FOODS table
    (SELECT item_id   
    -- Selecting item_id where it is less than 4
    FROM FOODS    
    WHERE item_id < 4)

Explanation:

This SQL query selects the item_id column from a derived table (subquery) based on a condition.

The subquery is enclosed within parentheses and acts as a temporary table.

Within the subquery:

It selects the item_id column from the FOODS table.

It includes a condition in the WHERE clause to filter rows where item_id is less than 4.

The outer query then selects the item_id column from the result of the subquery.

Output:

ITEM_ID
------
1
2
3

Pictorical Presentation :

Error in Single Row Subqueries

In this section, we will discuss some errors you might face in a 'single row subquery' operation. In our previous examples, we have seen, a single row subquery always returns a single row and if a subquery returns more than one row then an error occurs. In the following example, the subquery attempts to pass multiple rows to the equality operator (=) in the outer query.

Sample table: foods

SQL Code:


-- Selecting the item_id and item_name columns
SELECT item_id, item_name 
-- Selecting data from the 'foods' table
FROM foods
-- Filtering rows where the item_id matches the result of a subquery
WHERE item_id =   
    -- Subquery to retrieve item_id based on item_name containing 'a'
    (SELECT item_id 
    -- Selecting item_id from the 'foods' table
    FROM foods   
    -- Filtering rows where the item_name contains the letter 'a'
    WHERE item_name LIKE '%a%');

Explanation:

This SQL query selects the item_id and item_name columns from the foods table based on a condition.

The outer query filters rows where the item_id matches the result of a subquery.

The subquery:

Selects the item_id column from the foods table.

Includes a condition in the WHERE clause to filter rows where the item_name contains the letter 'a'.

The outer query compares the item_id column with the result of the subquery.

If you run the query in the Oracle Database 10g Express Edition you will get the following error :

ORA-01427: single-row subquery returns more than one row

Let's break the code and analyze what's going on in inner query. Here is the code of inner query:

SQL Code:


SELECT item_id
 FROM foods 
WHERE item_name LIKE '%a%';

Relational Algebra Expression:

Relational Algebra Tree:

Output:

ITEM_ID
------
5
7

The inner query returns two rows and the subquery attempts to pass these rows to the equality operator in the outer join. Since the equality operator can handle a single row, therefore the query is invalid and an error is returned.

Pictorical Presentation:

See our Model Database

Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.

Previous: Understanding Subqueries
Next: Multiple Row and Column Subqueries