Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 t-sql 2012 with implicit join

Author  Topic 

scamper
Yak Posting Veteran

52 Posts

Posted - 2014-04-24 : 17:56:38
I am trying to understand how the following t-sql 2012 with an implicit join is currently executed:


SELECT distinct custyear
FROM dbo.Individual INNER JOIN
dbo.Course INNER JOIN
dbo.CalendarYear ON dbo.Course.CalendarYearID = dbo.CalendarYear.CalendarYearID
AND CalendarYear.endYear >= (SELECT DISTINCT A.endYear FROM CalendarYear A WHERE A.active = 1)
INNER JOIN
dbo.Cust ON dbo.CalendarYear.CustID = dbo.Cust.CustID INNER JOIN
dbo.BridgeCust ON dbo.Course.CourseID = dbo.BridgeCust.CourseID ON
dbo.Individual.StudID = dbo.BridgeCust.StudID INNER JOIN
dbo.User INNER JOIN
dbo.BridgeUser ON dbo.User.UserID = dbo.BridgeUser.UserID
ON dbo.BridgeCust.CourseID = dbo.BridgeUser.CourseID

There is no direct join for the dbo.Individual with the dbo.Course table. The first time the dbo.Individual
is being joined is with the BridgeCust table. The bridgeCust table is only setup for key to the of this
table plus the key to the dbo.Individual and the dbo.Course table.

Thus would you explain to me how implicit join is setup?

Would you write the sql to show me the sql is setup to really be executed explicitly?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-04-24 : 19:03:05
It's not implicit, it's just horribly formatted. There are many ways to re-arrange the code, but I think this will still produce the same results:
SELECT 
distinct custyear
FROM
dbo.Course
INNER JOIN
dbo.CalendarYear
ON dbo.Course.CalendarYearID = dbo.CalendarYear.CalendarYearID
AND CalendarYear.endYear >= (SELECT DISTINCT A.endYear FROM CalendarYear A WHERE A.active = 1)
INNER JOIN
dbo.Cust
ON dbo.CalendarYear.CustID = dbo.Cust.CustID
INNER JOIN
dbo.BridgeCust
ON dbo.Course.CourseID = dbo.BridgeCust.CourseID
INNER JOIN
dbo.Individual
ON dbo.Individual.StudID = dbo.BridgeCust.StudID
INNER JOIN
dbo.BridgeUser
ON dbo.BridgeCust.CourseID = dbo.BridgeUser.CourseID
INNER JOIN
dbo.User
ON dbo.User.UserID = dbo.BridgeUser.UserID
Also, depending on my mood, I might move the Endyear predicate to a WHERE clause.. but meh..
Go to Top of Page

scamper
Yak Posting Veteran

52 Posts

Posted - 2014-04-25 : 10:32:06
Thank you very much! Your query is written a lot better
Go to Top of Page
   

- Advertisement -