How to check if a table is empty and if so, check in another table?

Question

I have two views: current_campaign and last_campaign:
current_campaign always has one row or none.
last_campaign always has one row.

I have another view that needs to get information from one of these: contributors.

If current_campaign has one row, I need to give it the preference.
If current_campaign is empty, then I can get information from last_campaign.
Is that possible?

What do you mean with "visions"? Are you talking about tables? Views? Materialized views? Functions? — user330315
– user330315, Commented Feb 12, 2016 at 12:28
Sample data and desired results would help. Also, the word vision isn't really a SQL term, so I assume you intend "table" or "view". — Gordon Linoff
– Gordon Linoff, Commented Feb 12, 2016 at 12:29
I took the liberty to replace the term "vision" with "view", presuming that's what you meant. — Erwin Brandstetter
– Erwin Brandstetter, Commented Feb 12, 2016 at 12:50

Community · Accepted Answer · 2017-05-23 12:07:28Z

Since at most 1 row per view seems to be given, there is a very simple and cheap solution:

-- CREATE VIEW contributors AS TABLE current_campaign UNION ALL TABLE last_campaign -- assuming matching row type LIMIT 1; -- applies to the whole query

If that was an over-simplification:

-- CREATE VIEW contributors AS SELECT * FROM current_campaign WHERE ... UNION ALL SELECT * FROM last_campaign WHERE ... LIMIT 1;

It would be a waste of time to count rows in current_campaign or run an EXISTS semi-join, since LIMIT 1 does everything you need automatically. Postgres stops executing as soon as enough rows are found to satisfy the LIMIT (1 in this case). You'll see "(never executed)" in the output of EXPLAIN ANALYZE for any later SELECT in the list. See links below for more.

This is an implementation detail that only works for UNION ALL (not UNION) and without an outer ORDER BY or other clauses that would force Postgres to consider all rows. I would expect other RDBMS to behave the same, but I only know about Postgres. It's guaranteed to work in all versions up to the current 9.5.

About the short syntax TABLE current_campaign:

Is there a shortcut for SELECT * FROM in psql?

Related, with more explanation, the same a bit more verbose:

This is a really clever solution but I'm curious... given your statement that "Postgres stops executing as soon as enough rows are found"... does Postgres guarantee the default order of a UNION ALL will always evaluate the lead table first? I ask because I've seen evidence that this order of evaluation is not necessarily guaranteed for some other RDBMS UNION statements (for instance SQL Server: stackoverflow.com/questions/421049/…)
@Erwin May be using LIMIT for this problem is better than COUNT (still not sure) but I think using LIMIT to filter row is not a good idea! Correct me if I tell something wrong.
@DanK: I added some more to clarify the scope of the solution.
@DanK: Oracle 12c will run both select statements in parallel when using union all so I guess it would not work there. I assume that it won't work if future Postgres versions either, once Postgres starts using parallel execution as well (although that is probably still some years in the future)

Gordon Linoff · Accepted Answer · 2016-02-12 12:30:27Z

I assume you mean view or table. Vision isn't really a SQL vocabulary term. Here is one way using union all and not exists:

select cc.* from current_campaign cc union all select lc.* from last_campaign lc where not exists (select 1 from current_campaign cc where cc.campaignId = lc.campaignId );

Collectives™ on Stack Overflow

How to check if a table is empty and if so, check in another table?

2 Answers 2

9 Comments

Comments

Linked

Hot Network Questions