Difference list vs INTERSECT across multiple tables

Question

For a given database data structure:

Table      Attribute       Type                       Glossary

Species    Sp_name         C(10) P.K.                 Species name
           sp_woodtype     C(10)                      Wood Yielded by tree
           sp_maxht        I                          Max.height


Forest     Fo_name         C(10) P.K.                 Forest name
           Fo_size         I                          Forest area
           Fo loc          C(10)                      Geographical name
           Fo_comp         C(10)                      Forest owner


Tree       Tr_species      C(10) F.K. species.sp_name
           Tr_forest       C(10) F.K. forest.fo_name
           Tr_numb         I     P.K.                 Sequence number
           Tr_planted      Date                       Date of planting
           Tr_loc          C(10)                      Forest quadrant
           Tr_parent       I     F.K. tree.tr_numb    Procreating tree reference


Measure    Me_trnumb       I     F.K. tree.tr_numb
           Me_numb         I     P.K.                 Sequence number
           Me_result       I                          Test's measure
           Me_date         Date                       Measure taken on 
           Me_type         C(10)                      Type of measure

P.K. is primary key, F.K. is foreign key, C(N) Character(N) type, I Integer type

I need to select which species of trees are found in all forests, so I have tried the following, but it seems wrong:

SELECT fo_name.forest, sp_name.species
FROM forest, species;

SELECT tr_species.tree, tr_forest.tree
FROM tree;

SELECT fo_name.forest, sp_name.species
FROM forest, species
INTERSECT
SELECT tr_species.tree, tr_forest.tree
FROM tree;

Is a difference list a better solution than an INTERSECT to solve this problem?

Answer 1

It's a special case of relational division.
You can compare the count of distinct forests per tree with the total count of forests to find out:

SELECT tr_species
FROM   tree
GROUP  BY tr_species
HAVING count(DISTINCT tr_forest) = (SELECT count(*) FROM forest);

Join the result to the table species if you need more than the PK.

BTW, the data type character(10) is no good, especially not as PK column.

• Any downsides of using data type "text" for storing strings?