Strange JOIN ON clause in T-SQL

Question

I am untangling some legacy code and never in all my years have I seen this:

Select * 

FROM GLAccounts
INNER JOIN GLCharts
    ON glaGLChartID = glcGLChartID
LEFT JOIN GLCategories
    ON glcGLCategoryID = gltGLCategoryID
INNER JOIN GLDepartments
    ON glaGLDepartmentID = gldGLDepartmentID
INNER JOIN GLDivisions
    ON glaGLDivisionID = glvGLDivisionID
        ,GLFiscalYearPeriods                --<this comma>
INNER JOIN GLFiscalYears
    ON glfGLFiscalYearID = glzGLFiscalYearID


ORDER BY glcGLCategoryID
    ,glcParentGLChartID
    ,glaGLChartID

the join works, but I don't find this use of a comma in any T-SQL syntax reference for the ON clause.

What's happening here? TIA

Answer 1

GLFiscalYearPeriods is a table and this comma implies a cross join. (Cartesian product)

It seems that the query returns some values for each Fiscal Year.

Given this tables:

create table a (id int, foo int);
create table c (id int);
insert into a values (1,1),(2,2),(3,3);
insert into c values (10),(20);

select * from a, c;

select * from a cross join c;

id | foo | id
-: | --: | -:
 1 |   1 | 10
 2 |   2 | 10
 3 |   3 | 10
 1 |   1 | 20
 2 |   2 | 20
 3 |   3 | 20

db<>fiddle here

Just another example:

create table a (id int, foo int);
create table b (id int, foo int);
create table c (id int);
create table d (id int);
insert into a values (1,1),(2,2),(3,3);
insert into b values (1,1),(2,2),(3,3);
insert into c values (10),(20);
insert into d values (1),(2);

select * from a join b on a.id = b.id       ,     c join d on d.id = a.id ;

Msg 4104 Level 16 State 1 Line 1
The multi-part identifier "a.id" could not be bound.

but if you cross-join it:

select * from a join b on a.id = b.id  cross join  c join d on d.id = a.id ;

id | foo | id | foo | id | id
-: | --: | -: | --: | -: | -:
 1 |   1 |  1 |   1 | 10 |  1
 1 |   1 |  1 |   1 | 20 |  1
 2 |   2 |  2 |   2 | 10 |  2
 2 |   2 |  2 |   2 | 20 |  2

db<>fiddle here

Answer 2

You know this join approach?

SELECT *
FROM table1, table2
WHERE table1.pk = table2.fk

If you leave the WHERE clause out of that query, you get the Cartesian product, which is the same as a CROSS JOIN:

SELECT *
FROM table1
CROSS JOIN table2

Your query looks like it combines that join approach with INNER JOIN syntax. It's logically the same as this:

SELECT *     
FROM GLAccounts
INNER JOIN GLCharts
    ON glaGLChartID = glcGLChartID
LEFT JOIN GLCategories
    ON glcGLCategoryID = gltGLCategoryID
INNER JOIN GLDepartments
    ON glaGLDepartmentID = gldGLDepartmentID
INNER JOIN GLDivisions
    ON glaGLDivisionID = glvGLDivisionID
CROSS JOIN GLFiscalYearPeriods                
INNER JOIN GLFiscalYears
    ON glfGLFiscalYearID = glzGLFiscalYearID

Here's an example:

DECLARE @t1 TABLE (t1id INT)
DECLARE @t2 TABLE (t2id INT, t1id INT)
DECLARE @t3 TABLE (t3id INT)

INSERT INTO @t1 VALUES (1), (2)
INSERT INTO @t2 VALUES (1, 1), (2, 1), (3, 2), (4, 2)
INSERT INTO @t3 VALUES (5), (6)

SELECT * 
FROM @t1 
INNER JOIN @t2 ON [@t2].t1id = [@t1].t1id
, @t3   

Returns:

t1id    t2id    t1id    t3id
1       1       1       5
1       1       1       6
1       2       1       5
1       2       1       6
2       3       2       5
2       3       2       6
2       4       2       5
2       4       2       6

Answer 3

The comma syntax for the join is an older syntax in the ANSI SQL standard (SQL-89 I think but I could be wrong) that was later updated (SQL-92 I think, again could be wrong) to use a more explicit syntax that was also more readable. It is known as the 'implicit join notation', contrasted with actual JOIN clauses that are 'explicit join notation'.

The comma syntax implies an implicit join rather than an explicit join, for example in your sample code without a WHERE clause, the comma syntax implies a CROSS JOIN to produce a cartesian product of all rows in both sides of the JOIN.

In the preferred SQL standard, you would use an explicit notation CROSS JOIN clause to produce the JOIN.

Links:

Join (SQL)

How do I do a JOIN in ANSI SQL 89 syntax?