Inheritance in sql. How to create a select statement in Table Per Type Inheritance?

Question

The accepted answer for this question is super useful: How can you represent inheritance in a database?

I really like this solution which he calls Table Per Type Inheritance:

CREATE TABLE policies (
   policy_id          int,
   date_issued        datetime,

   -- // other common attributes ...
);

CREATE TABLE policy_motor (
    policy_id         int,
    vehicle_reg_no    varchar(20),

   -- // other attributes specific to motor insurance ...

   FOREIGN KEY (policy_id) REFERENCES policies (policy_id)
);

CREATE TABLE policy_property (
    policy_id         int,
    property_address  varchar(20),

   -- // other attributes specific to property insurance ...

   FOREIGN KEY (policy_id) REFERENCES policies (policy_id)
);

However, I don't understand how to do a select statement. At the end of the answer he says:

Searching for all the policies regardless of the subtype now becomes very easy: No UNIONs needed - just a SELECT * FROM policies.

That will return all the data from policies, but what about the subtypes? Should the statement be this: SELECT * FROM policies INNER JOIN policy_motor ON policies.policy_id=policy_motor.id INNER JOIN policy_property ON policy_property.id=policies.id WHERE date_issued = yesterday

For my particular case, I have 70 subtypes. Will I need to inner join all 70 tables or am I missing some easy way to do this?

Answer 1

First of all, it shouldn't be SELECT * FROM policies INNER JOIN but rather SELECT * FROM policies LEFT JOIN Since in the "policy" table you have all the policies, and a reference to them in the sub type table. I think that if you need the subtype you have to add it as a column to the policies table. This way you also know where to look up for it. The purpose of the sub table was to avoid very long tables which were half "NULL" in values. This way you have only the data you need in the "Parent" table. You need to have as many of the base type data in the parent table (every policy has a date inception) and only the data you need which is specific to the sub type in the sub table.