How to query parent & child table in one query?

Question

First, I'll address concerns about duplicates:

• How to query a parent table and inherited child table together in one query
  • This question is similar but it doesn't provide a concrete example
• How can you represent inheritance in a database? suggests "Class Table Inheritance", which is the pattern I'm using, but does not explain how to query it effectively.

Here's a example of the problem I'm facing:

table Document {
    id: Id
    name: string
    type: ??
}

table FooDoc {
   id: Id
   // Foreign key to Document
   docId: Id
   qux: string
}

table BarDoc {
   id: Id
   // Foreign key to document
   docId: Id
   baz: number
}

Ideally, I'd like to make it so that in 1 query, I can

1. grab a document based on its id
2. grab the relevant data from the correct child table

Is this possible?

Answer 1

There are six ways (afaik) to model table inheritance in relational databases. You chose the Permissive Class Table Inheritance option.

Now, you can use two left joins to retrieve information for child tables. The resulting columns from the non-matching type will be null.

For example:

select d.*, f.qux, b.baz
from document d
left join foodoc f on f.id = d.id
left join bardoc b on b.id = d.id

Result:

 id  name  type  qux      baz  
 --- ----- ----- -------- ---- 
 20  baz1  2     null     1240 
 10  foo1  1     content  null 

See running example at DB Fiddle. As you can see, column qux is null for type 2 and column baz is null for type 1.

The sample structure for this example is shown below:

create table document (
  id int primary key not null, 
  name varchar(10), 
  type int not null check (type in (1, 2))
);

insert into document (id, name, type) values
  (10, 'foo1', 1),
  (20, 'baz1', 2);

create table foodoc (
  id int primary key not null references document(id),
  qux varchar(10)
);

insert into foodoc (id, qux) values (1, 'content');

create table bardoc (
  id int primary key not null references document(id),
  baz int
);

insert into bardoc (id, baz) values (2, 1240);

Note: Also please consider that to fully implement integrity you would need to include the type column in both foreign keys.