user3662708 user3662708 - 2 months ago 9
SQL Question

Query result is empty if table expression query is empty

I need to run a query something like this

SELECT kk.first_col, ll.second_col
FROM (SELECT 2 first_col FROM dual) kk,
(SELECT 1 second_col FROM dual WHERE 1 = 2) ll


Where one of the table expressions may return values or not.
In this example
kk
returns row but
ll
don't (for obvious reason).
And as a result if I run whole query I get 0 rows returned.
My expectations is to get result like this:

|FIRST_COL|SECOND_COL|
|2 | |


Is it possible?

Answer

Your query is almost okay, you just need to return the data from first table even when no data were found on second table.

In SQL, this job is done with LEFT JOIN. Try this:

SELECT 
  kk.first_col, 
  ll.second_col
FROM (SELECT 2 first_col FROM dual) kk
LEFT JOIN (SELECT 1 second_col FROM dual WHERE 1 = 2) ll ON 1=1
;