gotchasqlMajor
Strange JOIN ON clause in T-SQL
Viewed 0 times
sqlclausejoinstrange
Problem
I am untangling some legacy code and never in all my years have I seen this:
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
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 --
INNER JOIN GLFiscalYears
ON glfGLFiscalYearID = glzGLFiscalYearID
ORDER BY glcGLCategoryID
,glcParentGLChartID
,glaGLChartIDthe 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
Solution
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:
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:
Msg 4104 Level 16 State 1 Line 1
The multi-part identifier "a.id" could not be bound.
but if you cross-join it:
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
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
Code Snippets
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;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 ;select * from a join b on a.id = b.id cross join c join d on d.id = a.id ;Context
StackExchange Database Administrators Q#260768, answer score: 21
Revisions (0)
No revisions yet.