Search another table if the first query returns 0

Question

I have this query :

select id,name,surname
from db.table
where id_date = 20201101

But I want if this query returns 0 results to run this:

select d.id, d.name, d.surname
from db.table2 as d
inner join db.table3 as g
on d.id = g.id
where d.id_date =20201101

Is this possible? My first thought was to do it with subquery but I can not figure out how to handle the result set

1

UNION ALL, with NOT EXISTS. – jarlh Feb 10 '21 at 07:57

score 1 · Answer 1 · answered Feb 10 '21 at 08:00

1

As mentioned by @jarlh, try this

select id,name,surname
from db.table
where id_date = 20201101

union all

select d.id, d.name, d.surname
from db.table2 as d
inner join db.table3 as g
on d.id = g.id
where d.id_date =20201101 and not exists (
        select 1
        from db.table
        where id_date = 20201101)

answered Feb 10 '21 at 08:00

Arun Palanisamy

5,281
6
28
53

Thank you very much. Can you please explain me the thing with not exist and select 1 ? I will be grateful – DrGenius Feb 10 '21 at 08:01
The not exists is checking that the value doesn't exist in the first table. If it does then it won't return anything from the second query. The subquery doesn't actually need to return any actual value. Many people use select 1 to indicate that the value doesn't matter. https://www.w3schools.com/sql/sql_exists.asp – Bee_Riii Feb 10 '21 at 08:05
To add to what @Bee_Riii explained, `SELECT 1` is not mandatory, you can also use `SELECT COLUMN_NAME` or even `*`. Check out this [Exists / not exists: 'select 1' vs 'select field'](https://stackoverflow.com/questions/26461868/exists-not-exists-select-1-vs-select-field) – Arun Palanisamy Feb 10 '21 at 08:05

Caius Jard · Accepted Answer · 2021-02-10T08:13:21.083

As per jarlh's comment:

--"first query"
select id,name,surname
from db.table
where id_date = 20201101

UNION ALL

--"second query"
select d.id, d.name, d.surname
from db.table2 as d
inner join db.table3 as g
on d.id = g.id
where d.id_date =20201101 AND NOT EXISTS(

  select id,name,surname
  from db.table
  where id_date = 20201101

)

If the first query returns results then the NOT EXISTS will be false so the entire where clause of the second query is false and hence that second query has no output. The overall results are "some first query results union all nothing"

Conversely if the first query outputs nothing then NOT EXISTS (nothing) is true and the output of the second query becomes dependent on the truth of d.id_date=20201101 The overall query results are "nothing union all some second query results"

It's worth pointing out that this is exactly "run this, if no output run that" - it's a "run all of this" that achieves a modified output by considering both sides of a Boolean truth. As such it might be that the db will only run the first query once, having working out its used in two places, and reuse the results it gets in both places, but it's not guaranteed - it might be that the first query is run twice so make sure it's optimally indexed etc

Search another table if the first query returns 0

2 Answers2