Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Add support for synthetic set operations on SELECT statements #3790

Open
lukaseder opened this issue Nov 21, 2014 · 0 comments
Open

Add support for synthetic set operations on SELECT statements #3790

lukaseder opened this issue Nov 21, 2014 · 0 comments
Labels
C: Functionality E: All Editions P: Medium T: Enhancement
Milestone
Version X: Long-t...

Comments

@lukaseder
Copy link
Member

SQL is really missing a couple of useful set operations, such as =, <>. For example

-- Yields true
SELECT 
  (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 2)
  EQUAL
  (SELECT 1 UNION ALL SELECT 2)

-- Yields false
SELECT 
  (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 2)
  EQUAL ALL
  (SELECT 1 UNION ALL SELECT 2)

An emulation could be done via FULL OUTER JOIN. Example:

with a as (select * from unnest(array[1, 2, 3, 4, 6]) a(a)),
     b as (select * from unnest(array[1, 2, 2, 3, 4, 5]) b(b))
select *
from a full outer join b on a.a = b.b 
where a.a is null or b.b is null

The above would yield:

a  b
-----
6  
   5

So, in other words, the EQUAL operation could be emulated like this:

with a as (select * from unnest(array[1, 2, 3, 4, 6]) a(a)),
     b as (select * from unnest(array[1, 2, 2, 3, 4, 5]) b(b))
select count(*) = 0
from a full outer join b on a.a = b.b 
where a.a is null or b.b is null
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
C: Functionality E: All Editions P: Medium T: Enhancement
Projects
None yet
Milestone
Version X: Long-term goals and ideas
Development

No branches or pull requests
1 participant
@lukaseder