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.
SQL inserting records using subqueries with any operator
To insert records into 'agent3' table from 'agents' table with the following conditions -
1. 'agent_code' of 'agents' table must be any 'agent_code' from 'customer' table which satisfies the condition bellow :
2. 'cust_country' of customer table must be 'UK',
INSERT INTO agents3
SELECT * FROM agents WHERE agent_code=ANY (
SELECT agent_code FROM customer WHERE cust_country='UK'
);
Another Example
SELECT agent_name, agent_code, phone_no FROM agents
WHERE agent_code = (
SELECT agent_code FROM agents WHERE agent_name = 'Alex'
);
- Alex A003 075-12458969
Using comparison operators in Single Row subqueries
Example:
select agent_name, working_area from agents where agent_code in (
select agent_code from orders where ord_amount>1000);
Subqueries in a HAVING clause
Example:
- 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'.
SELECT AVG(ord_amount),COUNT(agent_code),agent_code FROM orders
GROUP BY agent_code
HAVING AVG(ord_amount)=(
SELECT AVG(ord_amount) FROM ordersWHERE agent_code='A008');
- 1000 1 A003
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.
SELECT item_id FROM (
SELECT item_id FROM FOODS WHERE item_id<4);
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.
SELECT item_id, item_name FROM foods WHERE item_id = (
SELECT item_id FROM foods WHERE item_name LIKE '%a%');
Error
- 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:
