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
SQL Code:
SELECT agent_name, agent_code, phone_no
FROM agents
WHERE agent_code =
(SELECT agent_code
FROM agents
WHERE agent_name = 'Alex');
Output:
AGENT_NAME AGENT_CODE PHONE_NO ---------------------------------------- ---------- --------------- Alex A003 075-12458969
The above example retrieves the ageent_name, agent_code, phone_no from the agents table whose agent_name is 'Alex'. Let's break the query in two parts and analyze what's going on.
SQL Code:
SELECT agent_code
FROM agents
WHERE agent_name = 'Alex';
Relational Algebra Expression:

Relational Algebra Tree:

Output:
AGENT_CODE ---------- A003
The above subquery is executed first and returns the agent_code for the row whose agent_name is 'Alex'. The agent_code for this row is 'A003', which is passed to the where clause of the outer (main) query.
The simplified form of above code is :
SQL Code:
SELECT agent_name, agent_code, phone_no
FROM agents
WHERE agent_code = 'A003';
Relational Algebra Expression:

Relational Algebra Tree:

Pictorical 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'.
Sample table: orders
Here is the complete SQL statement:
SQL Code:
SELECT ord_num,ord_amount,ord_date,cust_code, agent_code
FROM orders
WHERE ord_amount>
(SELECT AVG(ord_amount)
FROM orders
WHERE ord_date='20-APR-08');
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:
SELECT AVG(ord_amount)
FROM orders
WHERE ord_date='20-APR-08';
Relational Algebra Expression:

Relational Algebra Tree:

Output:
AVG(ORD_AMOUNT) --------------- 2500
The above query returns the average 'ord_amount' 2500, is used in the WHERE clause of the outer query shown earlier.
The simplified form of above code is:
SQL Code:
SELECT ord_num,ord_amount,ord_date,cust_code, agent_code
FROM orders
WHERE ord_amount>2500;
Relational Algebra Expression:

Relational Algebra Tree:

Pictorical 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'.
Sample table: orders
Here is the complete SQL statement:
SQL Code:
SELECT AVG(ord_amount),COUNT(agent_code),agent_code
FROM orders
GROUP BY agent_code
HAVING AVG(ord_amount)=
(SELECT AVG(ord_amount)
FROM orders
WHERE agent_code='A008');
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:
SELECT AVG(ord_amount)
FROM orders
WHERE agent_code='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:
SELECT AVG(ord_amount),COUNT(agent_code),agent_code
FROM orders
GROUP BY agent_code
HAVING AVG(ord_amount)=2500;
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:
SELECT item_id
FROM
(SELECT item_id
FROM FOODS
WHERE item_id<4)
Output:
ITEM_ID ------ 1 2 3
The subquery returns the rows from the foods table whose item_id is less than 4 to the outer query, which then retrieves and display those item_id values. As far is the FROM clause of the outer query is concerned the output from the subquery is just another source of data.
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:
SELECT item_id, item_name
FROM foods
WHERE item_id =
(SELECT item_id
FROM foods
WHERE item_name LIKE '%a%');
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:

Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.
Previous: Understanding Subqueries
Next: Multiple Row and Column Subqueries
SQL: Tips of the Day
What is the most efficient/elegant way to parse a flat table into a tree?
WITH RECURSIVE MyTree AS ( SELECT * FROM MyTable WHERE ParentId IS NULL UNION ALL SELECT m.* FROM MyTABLE AS m JOIN MyTree AS t ON m.ParentId = t.Id ) SELECT * FROM MyTree;
Ref: https://bit.ly/3FPYFFF
- Weekly Trends
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
- JavaScript functions Exercises
- Python Tutorial
- Python Array Exercises
- SQL Cross Join
- C# Sharp Array Exercises
We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook