I have an issue with selecting from my subqueries. If all of the subqueries return a value, everything is fine and a row with all the values I need is returned.

However, if one subquery is null or empty, I am returned an empty set even though the other six subqueries return a value.

Why is that? How can I get around it? I tried using 'isnull' and other hacks but it didn't work. I don't understand the design and why results would be returned as an empty set if just one of the subqueries fails. What are possible workarounds?

I tried using 'UNION' but that has it's own challenges because I ideally need one row returned so that Perl/DBI can slap the results into a HASH and I can match values returned to the column name, etc.

SELECT * FROM (
(select p.pesticides from pesticides p WHERE p.id=? and p.companyid=?) AS  pesticides,  
(select d.directions from directions d WHERE d.id=?) AS directions, 
( select i.intended from intended i where i.id=?) AS intended, 
(select c.chemicals from chemicals c where c.id=? and c.companyid=?) AS chemicals, 
(select a.aids from aids a where a.id=? and a.companyid=?) AS aids, 
(select ing.ingredients from ingredients ing where ing.id=? and ing.companyid=?) AS ingredients, 
(select s.solvents from solvents s WHERE s.id=? and s.companyid=?) AS solvents, 
(select aller.allergens from allergens aller where aller.id=?) AS allergens)

CodePudding user response：

It's returning an empty rowset when any one of the subqueries returns no rows because the set of subqueries is being treated as an intermediate table. Specifically, starting the SQL with SELECT * FROM ( tells the compiler that the section inside the parenthesis is an intermediate table.

You can treat each subquery as a column instead (a subselect) like this:

create table T1(ID int, STR varchar(255));
create table T2(ID int, STR varchar(255));

insert into T1(ID, STR) values (1, 'One'), (2, 'Two'), (3, 'Three');
insert into T2(ID, STR) values (4, 'Four'), (5, 'Five'), (6, 'Six');

select 
     (select STR from T1 where ID = 1) COL1
    ,(select STR from T2 where ID = 4) COL2
;

That will work the same as your approach when all subqueries return a single row:

Where it differs is what happens when one or more subselects do not return a row. The column simply becomes NULL:

select 
     (select STR from T1 where ID = 1) COL1
    ,(select STR from T2 where ID = 8) COL2 -- ID 8 does not exist
;

SQL Fiddle with this for MySQL: http://sqlfiddle.com/#!9/f1a209/1