Why is this left join being evaluated on a cross join?
Let's say I have table A with columns (Alpha, Beta) that is linked to table B with columns (beta, delta, gamma). I cannot explain why the first request is converted to cross join. (A.Alpha, A.Beta and B.Delta are unique keys. B.Beta looks at A.Beta).
If I make a selection 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 is always equal to one selected row and B_Alias.Gamma has every gamma. If I choose A.Alpha = 1
, it is a full cross connection. The attempted author of the query was to get the last column B (if it exists) associated with A. I fixed it to work using the following. I'm just wondering if someone can explain why it works like this.
-- This is the correct query
SELECT A.Alpha, B_Alias.Gamma FROM A
-- Actually join the A and B tables
LEFT JOIN B on B.Beta = A.Beta and B.Delta = (
-- Only get the Most Recent B for any given A
SELECT TOP 1 B.Delta FROM B
WHERE B.Beta = A.Beta
ORDER BY B.Gamma desc)
where A.Alpha = 1
source to share
The top query does not have a clause in the clause ON
that links A
to B
. What you are doing is to take each row from B
, which has the top Delta
, sorted by Gamma
for row Beta
, and concatenate that result into each row from A
. You are essentially taking a subset B
(which will be equal B
if Beta
unique) and cross-join it to A
, because you have not specified any direct relationship between A
and B
.
In a little more detail, if you take any table and join it to any other table where you don't have TableA.SomeColumn = TableB.SomeColumn
, you just get the full result set from TableB
, which might be limited, and then join that full result to every row in TableA
, because it doesn't It has the ability to limit the result set is connected to the line in TableA
. Hope it helps.
source to share