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

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?

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.

Robert James <srobertjames@gmail.com> 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?

If R and S have identical columns:

select count(*) from (select * from r intersect select * from s) w;

Assuming that R and S are sets (without duplicate rows) and can be
matched on all like-named columns and are also without duplicates
within each relation on the set of columns used for matching, this
faster construct also works:

select count(*) from r natural join s;

If these relations are produced by queries (as you might be
suggesting; it's hard to tell), you might want to use common table
expressions (CTEs) like this:

with r as (select ...),
     s as (select ...),
     rn as (select count(*) as n from r),
     sn as (select count(*) as n from s),
     xn as (select count(*) as n from
             (select * from r intersect select * from s) x)
select rn.n as count_r, sn.n as count_x, xn.n as count_intersection
  from rn, sn, xn;

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company