t-sql 2012 with implicit join - SQL Server Forums

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..

scamper
Yak Posting Veteran

52 Posts

Posted - 2014-04-25 : 10:32:06

Thank you very much! Your query is written a lot better

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources