Re: Computing count of intersection of two queries (Relational Algebra --> SQL)

Robert James wrote
> In relational algebra, I have relation R and relation S, and want to
> find the cardinality of R, of S, and of R-intersect-S.
>
> I know the SQL for R and S.  What's the best way to compute the
> cardinality of each relation (query) and of their intersection?

WITH r (id) AS (  VALUES (1),(2),(2),(3),(3),(3) )
, s (id) AS ( VALUES (1), (2), (3), (3) )
SELECT id FROM r
INTERSECT ALL
SELECT id FROM s;

Note that contrary to a mathematical set duplicate values are allowed in an
SQL set (note "3" repeated twice in the final result - if you use the "ALL"
form).

Combine various incantation of WITH/CTE expressions to compile whatever
final result you require.

David J.





--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Computing-count-of-intersection-of-two-queries-Relational-Algebra-SQL-tp5762935p5762936.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.