I have a question concerning the cross join in the following sql:
SELECT a.SCHOOLYEAR, CASE when SUM(c.UNECNT) > 0 then 9 else max(a.SEMESTER) end as SEMESTER, a.MNUM, a.STUNUNM, a.ABSDT,
'ZZZ' AS UCNT,
SUM(c.UNECNT) as ABSENCES
FROM S.dbo.LtrDetails a WITH (NOLOCK)
CROSS JOIN (
SELECT *
FROM S.dbo.LtrDetails d WITH (NOLOCK)
WHERE d.SCHOOLYEAR = (
SELECT endYear
FROM S.dbo.SchoolYear WITH (NOLOCK)
WHERE active = 1)
)
The person who wrote the sql above, said the query above returned all the rows in the table that applied versus. The inner join did not return all the needed rows.
I do not understand the benefit of using a 'cross join' versus an 'inner join' on the sql above pointing to the same table?
You've been around long enough to know to use the Insert/edit code sample when entering code to preserve the formatting.
You're missing part of your code. You're using an alias that isn't defined anywhere: c and you're using a mix of aggregated and unaggregated fields with no GROUP BY.
My working theory is that he really should be using a windowed function rather than an aggregate.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 4, 2019 at 11:36 pm
I do not understand the benefit of using a 'cross join' versus an 'inner join' on the sql above pointing to the same table?
There is no "benefit", it's a design choice. Under the hood there is no difference between a CROSS JOIN with a WHERE filter and an INNER JOIN with the filter on the ON clause.
As Drew mentioned, the query you posted is incomplete so its impossible to determine why one query returned different results from another.
-- Itzik Ben-Gan 2001
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply