Doesn't T-SQL support correlated CROSS JOINs?

Question

This query works

SELECT      QMC.HAUSKEY, t1.STRNAME, t2.HAUS_NR
FROM        SWOPS.MIGR.QMCAddresses QMC
CROSS JOIN  (SELECT STRNAME FROM SWOPS.MIGR.EB_DICT_STREET_QMC  WHERE FK=8055909) t1
CROSS JOIN  (SELECT HAUS_NR FROM SWOPS.MIGR.EB_DICT_HAUSNR_QMC  WHERE FK=8055909) t2
WHERE       1=1
        AND QMC.HAUSKEY=8055909
ORDER BY    HAUSKEY, t1.STRNAME, t2.HAUS_NR

However, when I replace the constant 8055909 in the CROSS JOIN subqueries with the column reference QMC.HAUSKEY the query reports an error

Msg 4104, Level 16, State 1, Line 56 The multi-part identifier "QMC.HAUSKEY" could not be bound.

Why is that?

BACKGROUND:

QMCAddresses contains the buildings and their addresses

EB_DICT_STREET_QMC contains the possible variations of the street-names of the addresses in QMCAddresses

EB_DICT_STREET_HAUSNR contains the possible variations of the house-numbers of the addresses in QMCAddresses

What I am trying to do is to create a cartesian product for each building address consisting of all possible variations of its street-name and all possible variations of its house-number.

e.g.

Answer 1

To answer the main question:

Doesn't T-SQL support correlated CROSS JOINs?

No. CROSS joins are not correlated in SQL (any variant, not only T-SQL).

Is there a workaround?

Yes. Correlated joins are by definition not CROSS joins and can be done with correlated subqueries or lateral joins. Lateral joins are implemented in SQL Server with (CROSS/OUTER) APPLY.

Let's simplify the query a bit:

SELECT      QMC.HAUSKEY, t1.STRNAME, t2.HAUS_NR
FROM        SWOPS.MIGR.QMCAddresses QMC
        CROSS JOIN  
            ( SELECT STRNAME 
              FROM SWOPS.MIGR.EB_DICT_STREET_QMC 
              WHERE FK = 8055909
            ) t1
WHERE       QMC.HAUSKEY = 8055909 ;

This is fine, ^^, a simple CROSS join. Now let's go to the offending one:

SELECT      QMC.HAUSKEY, t1.STRNAME, t2.HAUS_NR
FROM        SWOPS.MIGR.QMCAddresses QMC
        CROSS JOIN  
            ( SELECT STRNAME 
              FROM SWOPS.MIGR.EB_DICT_STREET_QMC
              WHERE FK = QMC.HAUSKEY
            ) t1
WHERE       QMC.HAUSKEY = 8055909 ;

However, when I replace the constant 8055909 in the CROSS JOIN subqueries with the column reference QMC.HAUSKEY the query reports an error

Msg 4104, Level 16, State 1, Line 56 The multi-part identifier "QMC.HAUSKEY" could not be bound.

That is correct, the above is not valid syntax. The identifier QMC.HAUSKEY is not visible inside the derived table t1.

The simplest way to solve this is to convert the CROSS join to a CROSS (or OUTER) APPLY:

SELECT      QMC.HAUSKEY, t1.STRNAME, t2.HAUS_NR
FROM        SWOPS.MIGR.QMCAddresses QMC
        CROSS APPLY  
            ( SELECT ca.STRNAME 
              FROM SWOPS.MIGR.EB_DICT_STREET_QMC  AS ca
              WHERE ca.FK = QMC.HAUSKEY
            ) t1
WHERE       QMC.HAUSKEY = 8055909 ;

And that is a valid query.