Q:

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

A:

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;

