Error (single-row subquery returns more than one row)

0
votes

What is wrong with this query?

select en.*,
       (select sf.red, sf.blue, sf.green, sf.yellow from data2 sf )
from data1 en;

I have error

ORA-01427: single-row subquery returns more than one row
01427. 00000 - "single-row subquery returns more than one row"

1
sqloracle
What you didn't understand here? the error message is pretty clear. – Ilyes
Skip the sub-query, do a LEFT JOIN instead. – jarlh
use TOP 1: select en.*, (select TOP 1sf.red, sf.blue, sf.green, sf.yellow from data2 sf ) from data1 en; – MRsa
@MRsa - The question is tagged [oracle]. TOP is not valid syntax in Oracle – APC
How many rows do you see when you run the subquery select sf.red, sf.blue, sf.green, sf.yellow from data2 sf on its own? What is the relationship between data and data2 - you may have just forgotten to include correlation in the subquery; but a join is almost certainly more appropriate anyway, even if you expect exactly one data2 row to match each data1 row. (And if it is a strictly 1-to-1 relationship, why have two tables at all?) – Alex Poole

1 Answers

2
votes

This is your query:

select en.*,
       (select sf.red, sf.blue, sf.green, sf.yellow from data2 sf )
from data1 en;

A subquery in the select used like this is called a scalar subquery. Such a subquery can be used where a scalar value (i.e. single value such as a number or string) can be used.

Scalar subqueries must meet two conditions:

  • It returns at most one row.
  • It returns one column.

The solution in your case is simple, using a LEFT JOIN:

select en.*, sf.red, sf.blue, sf.green, sf.yellow 
from data1 en left join
     data2 sf
     on 1=1;

Or, in Oracle 12C, you can use a lateral join:

select en.*, sf.red, sf.blue, sf.green, sf.yellow 
from data1 en left join lateral
     data2 sf
     on 1=1;

The two look the same in this case, but a lateral join can also be used for a correlated subquery.