Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
Conditional Cross Join - Why doesn't this work on the 'ON' clause?
Language:
Ada
Assembly
Bash
C#
C++ (gcc)
C++ (clang)
C++ (vc++)
C (gcc)
C (clang)
C (vc)
Client Side
Clojure
Common Lisp
D
Elixir
Erlang
F#
Fortran
Go
Haskell
Java
Javascript
Kotlin
Lua
MySql
Node.js
Ocaml
Octave
Objective-C
Oracle
Pascal
Perl
Php
PostgreSQL
Prolog
Python
Python 3
R
Rust
Ruby
Scala
Scheme
Sql Server
Swift
Tcl
Visual Basic
Layout:
Vertical
Horizontal
CREATE TABLE user_tbl AS SELECT * FROM ( VALUES ('u9876',201504,201610) ,('u5564',201602,201612) ,('u4435',201606,NULL) ) AS t(user_id, start_yearmonth, end_yearmonth); CREATE TABLE date_range AS SELECT * FROM ( VALUES (201601),(201602),(201603),(201604),(201605),(201606),(201607),(201608),(201609),(201610),(201611),(201612),(201701),(201702) ) AS t2(yearmonth); -- Check user_tbl output SELECT * FROM user_tbl ; -- Check date_range output SELECT * FROM date_range ; -- SUCCESSFUL CROSS JOIN SELECT * FROM user_tbl CROSS JOIN date_range WHERE user_tbl.start_yearmonth <= date_range.yearmonth -- EXAMPLE: FOR u5564, 201601 IS NOT <= 201602, SO IT IS NOT RETURNED AND (user_tbl.end_yearmonth >= date_range.yearmonth -- EXAMPLE: FOR u5564, 201701 IS NOT >= 201702, SO IT IS NOT RETURNED OR user_tbl.end_yearmonth IS NULL) -- EXAMPLE: FOR u4435, NO ROWS RETURNED UNLESS NULL VALUES ARE ALLOWED TO BE CROSS JOINED ORDER BY user_tbl.user_id, date_range.yearmonth ; -- UNSUCCESSFUL CROSS JOIN - WHY WON'T THIS WORK IN THE 'ON' CONDITION? SELECT * FROM user_tbl CROSS JOIN date_range ON user_tbl.start_yearmonth <= date_range.yearmonth AND (user_tbl.end_yearmonth >= date_range.yearmonth OR user_tbl.end_yearmonth IS NULL) ORDER BY user_tbl.user_id, date_range.yearmonth
Absolute service time: 0,5 sec, absolute service time: 0,51 sec
edit mode
|
history
|
discussion