Deprecated form of CROSS JOIN

Question

Post reply

Deprecated form of CROSS JOIN

Jeff Moden

SSC Guru

Points: 1004439
More actions
August 20, 2010 at 12:37 pm

#222769

Yes... I know what the preferred syntax of doing a CROSS JOIN is but can anyone show me a link to a Microsoft document that says the following form of a CROSS JOIN is being deprecated?
SELECT whatever
FROM sometableA AS a1,
sometableA AS a2
The reason why I'm asking is that I've seen a large number of posts that suggest that the syntax above has been deprecated and I just can't find it on an official deprecation list anywhere... heh... especially since it IS one of my favorite joins.
Thanks for the help, folks.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

jeff.mason SSCrazy Eights Points: 9585 More actions · Answer 1

Sure don't see that in R2 BOL, and you'd think that would be the most official list.

WayneS SSC Guru Points: 95461 More actions · Answer 2

In BOL (From Clause):

CROSS JOIN
Specifies the cross-product of two tables. Returns the same rows as if no WHERE clause was specified in an old-style, non-SQL-92-style join.

In Deprecated Database Engine Features, it does list the old-style outer join *=/=* as deprecated. Nothing in there about the old-style cross-join syntax being deprecated or going away in a future version.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

I don't think that the above syntax can be deprecated. Not unless they also deprecate any form of predicates comparing two tables in the where clause (and that would be near-impossible)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004439 More actions · Answer 4

GilaMonster (8/20/2010)
I don't think that the above syntax can be deprecated. Not unless they also deprecate any form of predicates comparing two tables in the where clause (and that would be near-impossible)

Exactly part of what I was thinking. If that form of syntax when away, there'd be a whole lot of trouble with correlated subqueries CROSS APPLY.

Thanks, Gail.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004439 More actions · Answer 5

jeff.mason (8/20/2010)
Sure don't see that in R2 BOL, and you'd think that would be the most official list.

I've heard that the old "equi-join" format of using commas for Inner and Cross joins is no longer documented in BOL but that it's never been deprecated... at least not in the 3 (2k5, 2k8, 2k8R2) deprecation lists I've seen from MS. And from what I'm thinking and Gail came right out and said, I don't believe they'll ever be able to deprecate it.

Heh... just think... they've created an undocumented set of features that they probably won't ever be able to get rid of. 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004439 More actions · Answer 6

WayneS (8/20/2010)
In BOL (From Clause):
CROSS JOIN
Specifies the cross-product of two tables. Returns the same rows as if no WHERE clause was specified in an old-style, non-SQL-92-style join.
In Deprecated Database Engine Features, it does list the old-style outer join *=/=* as deprecated. Nothing in there about the old-style cross-join syntax being deprecated or going away in a future version.

Thanks, Wayne. Yeah... those are the very lists (and their links) I've been reading letter by letter. The old-style outer join was first deprecated way back in 2k5 (IIRC), but I've never seen anything on tyhe old equi-join in a WHERE clause. Like Gail said, I don't think such a deprecation will be possible without changing a whole lot of other stuff.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004439 More actions · Answer 7

Thanks for the help, folks. I just wanted to make sure I wasn't missing something in the deprecation lists. I appreciate your time.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)