0

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?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Matheus Henrique Prates
  • 1
  • 1
  • 1
    What do you mean with "*visions*"? Are you talking about tables? Views? Materialized views? Functions? –  Feb 12 '16 at 12:28
  • 1
    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 Feb 12 '16 at 12:29
  • Hint: use `EXISTS()` and/or `NOT EXISTS()` – joop Feb 12 '16 at 12:29
  • 1
    Paste the script here ,what you did so far? – Anwar Ul-haq Feb 12 '16 at 12:29
  • 1
    I took the liberty to replace the term "vision" with "view", presuming that's what you meant. – Erwin Brandstetter Feb 12 '16 at 12:50
  • Yes, I meant view ( i didn't even knew that "vision" existed ). What I did was that ( and it seems to solve the problem ). SELECT bla bla bla FROM current_campaign WHERE EXISTS ( SELECT * FROM current_campaign ) AND bla bla bla UNION SELECT bla bla bla FROM last_campaign WHERE NOT EXISTS (SELECT * FROM current_campaign) AND bla bla bla Seems good? – Matheus Henrique Prates Feb 14 '16 at 14:14

2 Answers2

3

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:

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

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 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: http://stackoverflow.com/questions/421049/sql-server-union-what-is-the-default-order-by-behaviour) –  Feb 12 '16 at 13:43
  • @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. – mmuzahid Feb 12 '16 at 13:51
  • 1
    @DanK: I added some more to clarify the scope of the solution. – Erwin Brandstetter Feb 12 '16 at 21:02
  • 1
    @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) –  Feb 12 '16 at 21:04
  • @mmuzahid: Consider the additions. – Erwin Brandstetter Feb 12 '16 at 21:12
  • @a_horse_with_no_name: Postgres is in the process of implementing parallel execution. [Robert Haas is leading the work on it.](http://rhaas.blogspot.co.at/2015/11/parallel-sequential-scan-is-committed.html) But I am pretty sure this will *never* stop working. Performance characteristics may change ... – Erwin Brandstetter Feb 12 '16 at 21:17
  • @ErwinBrandstetter: I know there will be parallel execution in 9.6 that's why I added that comment (btw: we would lose the performance benefit, not turn it loose) –  Feb 12 '16 at 21:17
  • @ErwinBrandstetter: Thank you for the additional information.. it is a very interesting topic. –  Feb 14 '16 at 01:17
  • @a_horse_with_no_name: Thank you for the comment... I work mostly with Oracle 11G currently so your point is well taken. –  Feb 14 '16 at 01:18
0

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
                 );
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786