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