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

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