SQL Server join - Working clarification

Question

Can anyone explain why one of the joins is done simply comma-separated? Is there any advantage using it like this? What's the meaning of that join, how to rewrite the query by splitting that join as normal joins?

select distinct 
    aspl.AssessmentId, TestingProviderId, aspl.AssessmentSubjectId, 
    ep.providername, asplg.PerformanceLevelId as PerformanceLevelId, 
    asplg.PerformanceLevelName, asplg.PerformanceLevelColorARGB, 
    null as PerformanceLevelIdCount, null as GroupTotalCount, 
    null as PercentOfGroup, aspl.LastLoadTime 
from 
    AssessmentStudentPerformanceLevel aspl
inner join 
    AssessmentStudentImported asi on aspl.AssessmentStudentCode = asi.AssessmentStudentCode
inner join 
    EducationProvider ep on asi.testingepid=ep.providerid 
left join 
    Student stu on asi.MappedStudentId = stu.StudentId, 
    AssessmentPerformanceLevelGeneric asplg
where 
    aspl.AssessmentId = asplg.AssessmentId 
    and aspl.testingproviderid = ep.providerid 
    and ((IsNull(null, 0) = 0) or (ep.providerid = null))
    and ((IsNull(0, 0) = 0) or (ep.providerlevelid = 0))
    and aspl.AssessmentId = 239012015 
    and aspl.assessmentsubjectid = 1 
order by 
    aspl.AssessmentId, aspl.AssessmentSubjectId, 
    ep.providername, asplg.PerformanceLevelId

In the above query, I mentioned this section

 left join 
     Student stu on asi.MappedStudentId = stu.StudentId, 
     AssessmentPerformanceLevelGeneric asplg

That one is an old-join syntax, which should be avoided. Read more [here](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) — Felix Pamittan, Dec 10 '15 at 06:00
Possible duplicate of [ANSI vs. non-ANSI SQL JOIN syntax](http://stackoverflow.com/questions/1599050/ansi-vs-non-ansi-sql-join-syntax) — Nick.Mc, Dec 10 '15 at 06:24

Nguyễn Hải Triều · Accepted Answer · 2015-12-10T06:20:04.663

1

AssessmentPerformanceLevelGeneric is a table with asplg is alias name.

Comma ',' with condition is inner join, without condition is cross join.

SELECT *
FROM A
INNER JOIN B
ON <condition>
=
SELECT *
FROM A, B
WHERE <condition>


SELECT *
FROM A
CROSS JOIN B
=
SELECT *
FROM A, B

edited Dec 10 '15 at 06:20

answered Dec 10 '15 at 06:12

Nguyễn Hải Triều

1,454
1
8
14

I'm not saying about alias ,i wanted the working of that old joining style,how that works with which field joining conditions comes etc,Thank you. – Akhil Dec 10 '15 at 06:18
Will that be with the primarykey column of the from table? – Akhil Dec 10 '15 at 06:39

SQL Server join - Working clarification

1 Answers1