Why does correlated query in a select statement returns only single row-CodePudding

There are two tables:

pp_job - PK, FK -> (jobset_id, job_id)
pp_odate - PK, FK -> (jobset_id, job_id)

While performing normal select operation on below query on pp_odate as is returns multiple rows.

SELECT automic_param 
 FROM pp_odate 
WHERE jobset_id = 'ABC' 
 AND job_id = 'cba1';

But if this is included in another select statement as a subquery this will return first row only.

SELECT (
 SELECT automic_param 
  FROM pp_odate 
 WHERE jobset_id = pp_job.jobset_id 
  AND job_id = pp_job.job_id
)
 FROM pp_job 
WHERE pp_job.jobset_id = 'ABC' 
 AND pp_job.job_id = 'cba1';

I don't understand why is this happening. Is this a feature of correlated queries? And Is there any way to return all rows in 2nd select statement.

CodePudding user response：

The more normal way to do a correlated subquery is to start by saying what you want out of pp_odate and then reference pp_job in the Where clause, like this:

SELECT automic_param 
FROM pp_odate od
WHERE (jobset_id, job_id) = (
    SELECT j.jobset_id, j.job_id
    FROM pp_job j
    WHERE j.jobset_id = 'ABC' 
      AND j.job_id = 'cba1'
    );

Except in this case it's kind of dumb because you are referencing the very same variables in pp_job that are already in pp_odate. Let's say the pp_job query returned two records, both would be ('ABC','cba1'); that would be the equivalent of writing Where (jobset_id, job_id) = (('ABC','cba1'),('ABC','cba1')).

This is just like your first query, only slower!

Also note that this is not even a correlated subquery - it does not reference values in the pp_odate query at all.

Like Shawn says in the comments, 'I think you really want a join here'.

CodePudding user response：

Your subquery is used in a scalar context, so it can only ever return a single row. Any others are discarded (Well, never calculated at all).

You need something like

SELECT pp_odate.automic_param 
FROM pp_odate 
JOIN pp_job ON pp_odate=jobset_id = pp_job.jobset_id 
           AND pp_odate.job_id = pp_job.job_id
WHERE pp_job.jobset_id = 'ABC' AND pp_job.job_id = 'cba1';

to get multiple rows out of it.