Why does this left join evaluate to a cross join?

Question

Lets say I have a table A with columns (Alpha, Beta) which is linked to table B with columns (Beta, Delta, Gamma). I can't explain why the first query is transformed to a cross join. (A.Alpha, A.Beta and B.Delta are unique keys. B.Beta looks up to A.Beta). If I do a select like this: ``` SELECT A.Alpha, B_Alias.Gamma FROM A LEFT JOIN B as B_Alias ON B_Alias.Delta = ( SELECT TOP 1 B_Alias.Delta FROM B WHERE B.Beta = B_Alias.Beta ORDER BY B.Gamma desc) where A.Alpha = 1 ``` The result is many rows, A.Alpha always is equal to the single row selected and B_Alias.Gamma has every Gamma. If I take out the `A.Alpha = 1`, then it is a full cross join. The attempt by the writer of the query was to get the most recent B column (if any exists) associated with A. I fixed it to work by using the following. I was just wondering if someone can explain why the above works that way. ``` -- This is the correct query SELECT A.Alpha, B_Alias.Gamma FROM A -- Actually join the A and B tables LEFT JOIN B on…

Accepted Answer

The top query has no condition in the `ON` clause that relates `A` to `B`. What you're doing is taking every row from `B` that has the top `Delta` ordered by `Gamma` for it's `Beta` row, and joining that result set to each row from `A`. You're essentially taking a subset of `B` (Which will be equal to `B` if `Beta` is unique) and cross joining it to `A` because you haven't specified any direct relationship between `A` and `B`.

Just as a bit more detail, if you take any table and join it to any other table where you have no `TableA.SomeColumn = TableB.SomeColumn`, you'll essentially just get the full result set from `TableB` that can be limited, and then join that full result set to every row in `TableA`, because it has no way to limit the result set joined to a row in `TableA`. I hope that helps.

Why does this left join evaluate to a cross join?

Solution

Problem