TechTip: A Couple Cool Cross Joins

[MCWebsite.Staff]

** This thread discusses the article: TechTip: A Couple Cool Cross Joins **
** This thread discusses the Content article: TechTip: A Couple Cool Cross Joins0

[M.Savino]

TechTip: A Couple Cool Cross Joins

** This thread discusses the article: TechTip: A Couple Cool Cross Joins **
Kevin, The article was an interesting read and I did pick up a few pointers, but I don't think that the examples offer a very compelling reason to use Cross Join. Certainly there were other ways to accomplish both of the situations you presented: Details and Totals on the same row and Joining the File to itself. I did a few tests and the only advantage I could find was that when using Cross Join, the statements were Syntactically simpler and maybe more self-explanatory. That is a good thing in itself, but the results were the same and since, as you pointed out, the intention of Cross Join is to produce a Cartesian Join, I'd be nervous about using it unless there was a more persuasive reason. To your knowledge, are there any situations where only the Cross Join will produce the desired results. Thanks, Mike

[K.Forsythe]

TechTip: A Couple Cool Cross Joins

** This thread discusses the article: TechTip: A Couple Cool Cross Joins **
Mike, I have to say that I cannot think of any situation where the CROSS JOIN is the ONLY option. As you point out, it my be the better (simpler - cleaner) option, but in SQL there are rarely problems with only one solution. In general I would look to a CROSS JOIN when there are no columns to "JOIN ON" between the two tables. A simple JOIN requires a common column to act as a connector. But if this not available, I can make one... Here is a statement that does a cross join without calling it a CROSS JOIN: SELECT A.FLD1, B.FLD2 FROM (SELECT FLD1,'A' AS HOOK FROM FILE1) AS A JOIN (SELECT FLD2,'A' AS LATCH FROM FILE2) AS B ON A.HOOK = B.LATCH Since there is almost always more than one way to do anything, we have to choose one method over another. We each have our own priorities. Some of us optimize for performance, some for clarity, and other for coding speed. So that was the long answer, I guess the short answer is, NO.

[ukpi1b]

TechTip: A Couple Cool Cross Joins

** This thread discusses the article: TechTip: A Couple Cool Cross Joins **
Very interesting article with not traditional examples. In my view, when it comes to'...column functions--such as totals, averages, minimums, maximums, standard deviations, and more--into each detail line...' table expressions are more readable and could provide less complicated validations

---

Code

[K.Forsythe]

TechTip: A Couple Cool Cross Joins

** This thread discusses the article: TechTip: A Couple Cool Cross Joins **
You're right, the WITH table function is a great way to clean up the syntax of the main SELECT statement.