2020-09-24

jbanana: Badly drawn banana (Default)
2020-09-24 01:37 pm
Entry tags:

Cross outer join

I don't know what it should really be called, but I had to do a "cross outer join" for the first time in an SQL query.
select
    coalesce(preferredValue, fallbackValue) as combined
from
    (
        select fallbackValue
        from someTable
        where someCondition
    ) a
    left join (
        select preferredValue
        from otherTable
        where otherCondition
    ) b on 1=1
This works if the second subquery returns either what you want or nothing at all. The coalesce only returns fallbackValue if the second subquery returns nothing.

The 1=1 part makes this work, but it also makes me queasy because I came across it reading about SQL injection attacks.