Using Old Join Syntax
The old join syntax still functions in SQL Server 2014, however it may be one of the most argued against formats.
For instance the following implies an inner join, even though it is not explicitly called out:
SELECT sc1.name, sc2.name FROM sys.columns sc1, sys.columns sc2 WHERE sc1.object_id = sc2.object_id;
Which can be replaced with the following which specifically calls out an inner join, not using the old format.
SELECT sc1.name, sc2.name FROM sys.columns sc1 INNER JOIN sys.columns sc2 ON sc1.object_id = sc2.object_id;
Do you agree?
If you don’t agree the old style join is considered as technical debt, that is just fine, in the Database Health Reports settings dialog you can turn the check for old style joins off. As long as you have coding standards that state one opinion or another on the old style joins being your standard, then you are doing better than most. I would strongly suggest not using the old format.
A good coding standard can over-ride many of the items that may be considered as technical debt.
Enroll Today!
Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!
Download this page in PDF format
The following view got the “Old Join Syntax” error and I can’t figure out why.
SELECT
qs.[SERVICE]
,qs.VARNAME
,qs.VALUE
,qs.SURVEY_ID
,map.QUESTION_TEXT
,map.SECTION
,map.[STANDARD]
,map.SCREENING
,map.TOP_BOX_SCALE
,map.TOP_BOX_ANSWER
FROM
(
SELECT pt.SURVEY_ID
,pt.SERVICE
,sa.VARNAME
,sa.VALUE
FROM pg.PG_survey_patient pt
LEFT JOIN pg.PG_survey_analysis sa ON sa.SURVEY_ID = pt.SURVEY_ID
UNION ALL
SELECT pt.SURVEY_ID
,pt.SERVICE
,d.VARNAME
,d.VALUE
FROM pg.PG_survey_patient pt
LEFT JOIN pg.PG_survey_demographics d ON d.SURVEY_ID = pt.SURVEY_ID
UNION ALL
SELECT pt.SURVEY_ID
,pt.SERVICE
,c.VARNAME
,c.VALUE
FROM pg.PG_survey_patient pt
LEFT JOIN pg.PG_survey_CAHPS c ON c.SURVEY_ID = pt.SURVEY_ID
UNION ALL
SELECT pt.SURVEY_ID
,pt.SERVICE
,cm.VARNAME
,cm.VALUE
FROM pg.PG_survey_patient pt
LEFT JOIN pg.PG_survey_comments cm ON cm.SURVEY_ID = pt.SURVEY_ID
) AS qs
INNER JOIN pg.PG_survey_question_map map ON map.[SERVICE] = qs.[SERVICE] AND map.VARNAME = qs.VARNAME