0

I came across an odd bit of legacy code while going back through old code for cleanup, and I'm trying to pin down exactly what it does...

CROSS JOIN (SELECT 0 as a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) as b

It's a technique I haven't seen before, including multiple union selects of numbers inside a cross join, and I'm honestly really confused about what it's doing and why someone would do it.

lilHar
  • 1,735
  • 3
  • 21
  • 35
  • Possible duplicate of [SQL Server: What is the difference between CROSS JOIN and FULL OUTER JOIN?](https://stackoverflow.com/questions/3228871/sql-server-what-is-the-difference-between-cross-join-and-full-outer-join) – Alessandro Da Rugna Jun 30 '18 at 23:31

1 Answers1

2

Cross join creates a Cartesian product.

Assuming the from clause looks like:

FROM t CROSS JOIN
     ( . . . )

This creates four rows for every one row in t. The value of b.a in these rows varies from 0 to 3.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • So, basically, it turns [column1:foo, column2:bar] into [column1:foo, column2:bar, b:0], [column1:foo, column2:bar, b:1], [column1:foo, column2:bar, b:1], [column1:foo, column2:bar, b:3]? – lilHar Jun 30 '18 at 22:23
  • Just tested on a different server... yea, that's exactly what it does... weird, not sure the point of it though. Thanks! – lilHar Jun 30 '18 at 22:41