September 14, 2012 at 5:16 am
Using multiple tables sepparated by a comma, is an old syntax which should not be used anymore. (Although it is still supported).
I used to say that the comma should be replaced by a CROSS JOIN and that although the syntax is different, the effect 'should' be the same.
Can anybody explain to me why this is True or not True?
Thanks for your time and attention.
I am working on an example, which triggered this question from me.
Ben Brugman
September 14, 2012 at 5:26 am
yes it's true; there are two ways to cross join tables.
SQL server supports the old syntax of FROM TABLE1,TABLE2 as well as the more modern, explicit FROM TABLE1 CROSS JOIN TABLE2;
I feel the explicit CROSS JOIN makes for better code review.
with the old syntax, you have to review the WHERE statement to figure out the join type...WHERE TABLE1.Id = TABLE2.ID to figure if it's really a cross join, an accidental cross join that is missing a WHERE predicate, or a for sure INNER JOIN.
Lowell
September 14, 2012 at 5:45 am
Lowell (9/14/2012)
yes it's true; there are two ways to cross join tables.
I do realize that there a two ways for the cross join. And I know that the comma syntax should not be used. My question was are these two equivalent?
I thought they were equivalent.
But I ran into code similar to the code below.
There the comma and the 'CROSS JOIN' do not have the same effect. So now I am curious about the difference.
(And I feel a little bit stupid, because I used to tell people that the function of the COMMA is the same as a CROSS JOIN. In this case I am trying to learn from my errors.).
Thanks for your anwser,
Ben Brugman
CREATE TABLE Role
(
code varchar(300) NULL,
name varchar(300) NULL
) ON [PRIMARY]
CREATE TABLE Worker
(
nr int NULL,
name varchar(300) NULL,
town varchar(300) NULL,
superiour varchar(300) NULL,
Role varchar(300) NULL,
hoursalary float NULL
) ON [PRIMARY]
insert into Role values ('PL','projectmanager')
insert into Role values ('PR','Programmeur')
insert into Role values ('SA','Systeem analyst')
insert into Role values ('SO','Systeem ontwerper')
insert into Worker values (11, 'Andre', 'Amsterdam', 15, 'PR', 100)
insert into Worker values (12, 'Jane', 'Amsterdam', 15, 'PR', 110)
insert into Worker values (13, 'Jan', 'Rotterdam', 15, 'SA', 120)
insert into Worker values (14, 'Peter', 'Haarlem',NULL, 'PL', 130)
insert into Worker values (15, 'Chris', 'Amsterdam', 14, 'PL', 140)
insert into Worker values (16, 'Kees', NULL, 15, NULL, 100)
/*
--
--
Select M.name, F.name, B.name
From Worker M, Role F
LEFT JOIN Worker B ON M.superiour=B.nr
Where M.Role = code
--
--
*/
-- Comma replaced by a CROSS JOIN
Select M.name, F.name, B.name
From Worker M CROSS JOIN Role F
LEFT JOIN Worker B ON M.superiour=B.nr
Where M.Role = code
DROP TABLE Role
DROP TABLE Worker
September 14, 2012 at 6:43 am
Interesting. Under most circumstances, the two are semantically and functionally equivalent. But in this case, it can't parse the query correctly. I get a "Can't bind M.superiour" error. That the same thing you're seeing?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 14, 2012 at 6:51 am
They are equivalent, but I don't think you can mix the two types of join in the same query.
It's the where clause that's causing the difference in the results, not the join operator.
September 14, 2012 at 6:53 am
GSquared (9/14/2012)
Interesting. Under most circumstances, the two are semantically and functionally equivalent. But in this case, it can't parse the query correctly. I get a "Can't bind M.superiour" error. That the same thing you're seeing?
Yeah, me too. Tried to rewrite the commented-out query using only "," joins but can't work out the where clause that'll equate to the left join!
September 14, 2012 at 6:55 am
that's because the old comma syntax didn't support left/right joins without that old =* or *= syntax (or the oracle the old styles pretty muich only inner joins without them
=columnname(+) syntax)
Lowell
September 14, 2012 at 6:59 am
Because of the left join you need to change the order of the tables in the from clause.
Select M.name, F.name, B.name
From ROLE F, Worker M
LEFT JOIN Worker B ON M.superiour=B.nr
Where M.Role = code
September 14, 2012 at 7:00 am
Gazareth (9/14/2012)
They are equivalent, but I don't think you can mix the two types of join in the same query.It's the where clause that's causing the difference in the results, not the join operator.
Except I'm not getting different results from the two. Copy-and-paste from the posted code generates a binding error on the query with a comma. SQL 2008 R2 can't even parse the query, much less run it and get different results.
The Cross Join version works just fine. The comma version can't even run. That's what I found interesting.
I tried replacing the comma with "cross join", just in case I was missing something else in it, and the error goes away when I do.
That means the two are not semantically identical in the query parsing engine. I always thought they were. I don't use commas in From clauses, I explicitly declare all joins (makes future debugging much easier). "INNER JOIN" not "JOIN", "LEFT OUTER JOIN" not "LEFT JOIN", and "CROSS JOIN" not ",", in all my code. So I've never run into a difference before. Especially a semantic one that allows one to parse and the other not to.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 14, 2012 at 7:00 am
The comma form parses correctly if you list Worker second:
Select M.name, F.name, B.name
From Role F, Worker M
LEFT JOIN Worker B ON M.superiour=B.nr
Where M.Role = code
It might be a limitation of the parser, or perhaps there is a good logical reason for the order sensitivity, I'm not sure. In any case, it seems sensible not to mix the two styles. Since there is no sensible syntax for outer joins using the older style (and all syntax removed from SQL Server 2012) I would write this as:
SELECT
M.name,
F.name,
B.name
FROM dbo.[Role] AS F
JOIN dbo.Worker AS M ON
M.[Role] = F.code
LEFT JOIN Worker AS B ON
B.nr = M.superiour;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 14, 2012 at 7:53 am
The execution plans on both of them are identical. Same objects, same percentages. (Once I swap the tables in the FROM clause, that is). Same results, same everything.
But wouldn't something like this be a better case for a CTE than a CROSS JOIN (comma or not)?
September 14, 2012 at 7:55 am
Gazareth (9/14/2012)
They are equivalent, but I don't think you can mix the two types of join in the same query.It's the where clause that's causing the difference in the results, not the join operator.
I'm curious as to which version of SQL Server you got it to work in. Different results, instead of a parsing error.
Edit: Or, based on your next post, did you get the error too?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 14, 2012 at 7:56 am
SQL Kiwi (9/14/2012)
The comma form parses correctly if you list Worker second:
Select M.name, F.name, B.nameFrom Role F, Worker M
LEFT JOIN Worker B ON M.superiour=B.nr
Where M.Role = code
It might be a limitation of the parser, or perhaps there is a good logical reason for the order sensitivity, I'm not sure. In any case, it seems sensible not to mix the two styles.
Interesting, wonder if it's similar to trying to use a table in a join predicate that is joined later in the query?
September 14, 2012 at 7:58 am
Brandie Tarvin (9/14/2012)
The execution plans on both of them are identical. Same objects, same percentages. (Once I swap the tables in the FROM clause, that is). Same results, same everything.But wouldn't something like this be a better case for a CTE than a CROSS JOIN (comma or not)?
Actually, it should simply be an Inner Join between a worker and a role, with an outer join to the supervisor/manager. No need for a CTE at all. Could be done that way, but no reason to.
I just found the parsing difference interesting, from an SQL-internals viewpoint.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 14, 2012 at 8:00 am
GSquared (9/14/2012)
Gazareth (9/14/2012)
They are equivalent, but I don't think you can mix the two types of join in the same query.It's the where clause that's causing the difference in the results, not the join operator.
I'm curious as to which version of SQL Server you got it to work in. Different results, instead of a parsing error.
Edit: Or, based on your next post, did you get the error too?
Yep, same error - SQL 2008R2. I assumed the OP had seen different results with a valid query at some point, and just meant that logically, "," and CROSS JOIN are the same.
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply