18

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

20

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

| improve this answer | |
  • Thank you so much. It's funny, in 1990, using ORACLE, "FROM table1, table2... WHERE" was the normal way of doing things. But since 97 I have always worked with the SQL 92 way, with explicit JOIN ... ON ... clauses. It's not that I had forgotten about the old way, I just had never seen the two styles mixed like this, so it seemed mysterious. Do you think this mixing of explicit and implicit joins is considered good practice? – Joseph Shirk Feb 28 at 12:59
  • 1
    It is recommended to use the new syntax, but i terms of performance it's probably the same. – McNets Feb 28 at 13:01
7

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
| improve this answer | |
5

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?

| improve this answer | |
  • 2
    Both syntaxes (the comma or implicit join) and (the JOIN ON operator) are valid in the current SQL standard and in all versions between 92 and now. – ypercubeᵀᴹ Feb 27 at 22:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.