Search code examples
sqljoincorrelated-subquery

Merge multiple row subquery into corelated SQL query

I have a correlated SQL sub query which returns multiple rows. For instance,

SELECT NAME AS NAME1,
(SELECT NAME FROM ...) AS NAME2 /*this inner select returns more than one rows*/ 
FROM PERSONAL_INFORMATION

Is it possible to merge the rows returned by NAME2 with rest of the main query, here NAME1, that is. What I am thinking is kind of a cross product shown below where I can group all the given instances using GROUP BY or DISTINCT?

NAME1_1  NAME2_1
NAME1_1  NAME2_2
NAME1_2  NAME2_1
NAME1_2  NAME2_2
Solution

  • Just use CROSS JOIN then.

    SELECT I1.NAME AS NAME1, I2.NAME AS NAME2 
FROM PERSONAL_INFORMATION I1
CROSS JOIN PERSONAL_INFORMATION2 I2;

    If you need more detail, you may use GROUP BY :

    SELECT I1.NAME AS NAME1, I2.NAME AS NAME2, 
       COUNT(1) cnt, SUM(COL2) sum_col1 ...
FROM PERSONAL_INFORMATION I1
CROSS JOIN PERSONAL_INFORMATION2 I2
GROUP BY I1.NAME, I2.NAME;