0

I want to concatenate _BAR to the results of a Query.

One of my first attempts is this: 

SELECT lhs.f||rhs.f as concat_bar FROM (
SELECT 'FOO' as f FROM DUAL) lhs 
LEFT JOIN 
(
SELECT '_BAR' as f FROM DUAL) rhs
ON ('' != rhs.f) 
;

but I got no results. I was expecting ON ('' != rhs.f) to evaluate to true so I expected as a result a single row: 'FOO_BAR'. Which is the result of concatenating the cartesian product of the lhs and rhs tables.

How can I JOIN on TRUE?

I know that, for the specific problem, other solutions as 

SELECT lhs.f||'_BAR' FROM (
SELECT 'FOO' as f FROM DUAL) lhs;

are possible.

My question is on an effective syntax to make a cartesian product of two tables as a LEFT JOIN ON TRUE.

jimifiki
  • 5,377
  • 2
  • 34
  • 60

2 Answers2

1

Oracle (by default) treats an empty string as NULL. This is a real pain. It is different from other databases. I suppose their argument is: "Well, we were doing it for years before ANSI defined NULL values." Great, but those years were the 1980s.

In any case, this logic:

'' <> rhs.f

(<> is the original not-equals operator in SQL.)

is exactly the same as:

NULL <> rhs.f

This always returns NULL and that is treated as not-true in WHERE conditions.

In Oracle, express this emptiness as:

rhs.f is not null
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
1

Aside from the issues with null, is a cross join what you wanted?

select lhs.f || rhs.f as concat_bar
from   ( select 'FOO' as f from dual ) lhs
       cross join
       ( select '_BAR' as f from dual ) rhs
William Robertson
  • 15,273
  • 4
  • 38
  • 44