Robert James <srobertjames@xxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
