SQL: Single Row Subqueries
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
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+------------+----------------------+--------------------+------------+-----------------+---------+ | AGENT_CODE | AGENT_NAME | WORKING_AREA | COMMISSION | PHONE_NO | COUNTRY | +------------+----------------------+--------------------+------------+-----------------+---------+ | A007 | Ramasundar | Bangalore | 0.15 | 077-25814763 | | | A003 | Alex | London | 0.13 | 075-12458969 | | | A008 | Alford | New York | 0.12 | 044-25874365 | | | A011 | Ravi Kumar | Bangalore | 0.15 | 077-45625874 | | | A010 | Santakumar | Chennai | 0.14 | 007-22388644 | | | A012 | Lucida | San Jose | 0.12 | 044-52981425 | | | A005 | Anderson | Brisban | 0.13 | 045-21447739 | | | A001 | Subbarao | Bangalore | 0.14 | 077-12346674 | | | A002 | Mukesh | Mumbai | 0.11 | 029-12358964 | | | A006 | McDen | London | 0.15 | 078-22255588 | | | A004 | Ivan | Torento | 0.15 | 008-22544166 | | | A009 | Benjamin | Hampshair | 0.11 | 008-22536178 | | +------------+----------------------+--------------------+------------+-----------------+---------+
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:
Visual Presentation:
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'.
ORD_NUM ORD_AMOUNT ADVANCE_AMOUNT ORD_DATE CUST_CODE AGENT_CODE ORD_DESCRIPTION
---------- ---------- -------------- --------- --------------- --------------- -----------------
200114 3500 2000 15-AUG-08 C00002 A008
200122 2500 400 16-SEP-08 C00003 A004
200118 500 100 20-JUL-08 C00023 A006
200119 4000 700 16-SEP-08 C00007 A010
200121 1500 600 23-SEP-08 C00008 A004
200130 2500 400 30-JUL-08 C00025 A011
200134 4200 1800 25-SEP-08 C00004 A005
200108 4000 600 15-FEB-08 C00008 A004
200103 1500 700 15-MAY-08 C00021 A005
200105 2500 500 18-JUL-08 C00025 A011
200109 3500 800 30-JUL-08 C00011 A010
200101 3000 1000 15-JUL-08 C00001 A008
200111 1000 300 10-JUL-08 C00020 A008
200104 1500 500 13-MAR-08 C00006 A004
200106 2500 700 20-APR-08 C00005 A002
200125 2000 600 10-OCT-08 C00018 A005
200117 800 200 20-OCT-08 C00014 A001
200123 500 100 16-SEP-08 C00022 A002
200120 500 100 20-JUL-08 C00009 A002
200116 500 100 13-JUL-08 C00010 A009
200124 500 100 20-JUN-08 C00017 A007
200126 500 100 24-JUN-08 C00022 A002
200129 2500 500 20-JUL-08 C00024 A006
200127 2500 400 20-JUL-08 C00015 A003
200128 3500 1500 20-JUL-08 C00009 A002
200135 2000 800 16-SEP-08 C00007 A010
200131 900 150 26-AUG-08 C00012 A012
200133 1200 400 29-JUN-08 C00009 A002
200100 1000 600 08-JAN-08 C00015 A003
200110 3000 500 15-APR-08 C00019 A010
200107 4500 900 30-AUG-08 C00007 A010
200112 2000 400 30-MAY-08 C00016 A007
200113 4000 600 10-JUN-08 C00022 A002
200102 2000 300 25-MAY-08 C00012 A012
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:
Visual Presentation:
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'.
ORD_NUM ORD_AMOUNT ADVANCE_AMOUNT ORD_DATE CUST_CODE AGENT_CODE ORD_DESCRIPTION
---------- ---------- -------------- --------- --------------- --------------- -----------------
200114 3500 2000 15-AUG-08 C00002 A008
200122 2500 400 16-SEP-08 C00003 A004
200118 500 100 20-JUL-08 C00023 A006
200119 4000 700 16-SEP-08 C00007 A010
200121 1500 600 23-SEP-08 C00008 A004
200130 2500 400 30-JUL-08 C00025 A011
200134 4200 1800 25-SEP-08 C00004 A005
200108 4000 600 15-FEB-08 C00008 A004
200103 1500 700 15-MAY-08 C00021 A005
200105 2500 500 18-JUL-08 C00025 A011
200109 3500 800 30-JUL-08 C00011 A010
200101 3000 1000 15-JUL-08 C00001 A008
200111 1000 300 10-JUL-08 C00020 A008
200104 1500 500 13-MAR-08 C00006 A004
200106 2500 700 20-APR-08 C00005 A002
200125 2000 600 10-OCT-08 C00018 A005
200117 800 200 20-OCT-08 C00014 A001
200123 500 100 16-SEP-08 C00022 A002
200120 500 100 20-JUL-08 C00009 A002
200116 500 100 13-JUL-08 C00010 A009
200124 500 100 20-JUN-08 C00017 A007
200126 500 100 24-JUN-08 C00022 A002
200129 2500 500 20-JUL-08 C00024 A006
200127 2500 400 20-JUL-08 C00015 A003
200128 3500 1500 20-JUL-08 C00009 A002
200135 2000 800 16-SEP-08 C00007 A010
200131 900 150 26-AUG-08 C00012 A012
200133 1200 400 29-JUN-08 C00009 A002
200100 1000 600 08-JAN-08 C00015 A003
200110 3000 500 15-APR-08 C00019 A010
200107 4500 900 30-AUG-08 C00007 A010
200112 2000 400 30-MAY-08 C00016 A007
200113 4000 600 10-JUN-08 C00022 A002
200102 2000 300 25-MAY-08 C00012 A012
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+---------+--------------+-----------+------------+ | ITEM_ID | ITEM_NAME | ITEM_UNIT | COMPANY_ID | +---------+--------------+-----------+------------+ | 1 | Chex Mix | Pcs | 16 | | 6 | Cheez-It | Pcs | 15 | | 2 | BN Biscuit | Pcs | 15 | | 3 | Mighty Munch | Pcs | 17 | | 4 | Pot Rice | Pcs | 15 | | 5 | Jaffa Cakes | Pcs | 18 | | 7 | Salt n Shake | Pcs | | +---------+--------------+-----------+------------+
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
Visual 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+---------+--------------+-----------+------------+ | ITEM_ID | ITEM_NAME | ITEM_UNIT | COMPANY_ID | +---------+--------------+-----------+------------+ | 1 | Chex Mix | Pcs | 16 | | 6 | Cheez-It | Pcs | 15 | | 2 | BN Biscuit | Pcs | 15 | | 3 | Mighty Munch | Pcs | 17 | | 4 | Pot Rice | Pcs | 15 | | 5 | Jaffa Cakes | Pcs | 18 | | 7 | Salt n Shake | Pcs | | +---------+--------------+-----------+------------+
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.
Visual Presentation:
Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.
Previous: Understanding Subqueries
Next: Multiple Row and Column Subqueries