SQL Queries on Subtyped Data Models using Case Expressions and Coalesce

CoalesceVsCase.md

Disjoint subtyping is a scenario that is often encountered in data modeling. In one frequently used modeling approach, an entity of a certain type is represented by a database table, and each subtype of this entity is represented by another table. Subtyping is disjoint if an instance of a type corresponds to at most one instance of a subtype.

When querying on a data model with subtypes, a common (verbose?!) way of doing this is using case expressions. The idea of this post is to introduce an alternative to this approach using coalesce. I will be illustrating this across multiple examples.

Example #1

In the toy schema depicted below, list all orders with their corresponding vendors and customers

Order Table

Id	Status	Quantity	Type
1	0	70000	1
2	1	80000	2
3	0	60000	2
4	0	90000	1
5	2	69000	1
6	1	85000	1

Sale Table

Id	OrderId	SourceStore	LinkedStore
1	1	S1	S2
2	4	S3	S5
3	5	S3	S6
4	6	S3	S1

Purchase Table

Id	OrderId	SourceStore	LinkedStore
1	2	S2	S1
2	3	S3	S4

In this scheme of things, the columns SourceStore and LinkedStore are either vendors or customers in a particular Order depending on the Type of the Order (Purchase or Sale). Fiddle Link

Using CASE Expression

SELECT 
 O.Id, Status, Quantity, Type,
 (CASE
   WHEN O.Type = 1 THEN S.SourceStore
   ELSE P.LinkedStore
   END
  ) AS Vendor,
  (CASE
   WHEN O.Type = 1 THEN S.LinkedStore
   ELSE P.SourceStore
   END
  ) AS Customer
FROM `ORDER` O 
 LEFT JOIN `PURCHASE` P ON O.Id = P.OrderId 
 LEFT JOIN `SALE` S ON O.Id = S.OrderId

Using Coalesce Expression

SELECT 
 O.Id, Status, Quantity, Type,
 COALESCE(P.LinkedStore, S.SourceStore) AS Vendor,
 COALESCE(P.SourceStore, S.LinkedStore) AS Customer
FROM `ORDER` O 
 LEFT JOIN `PURCHASE` P ON (O.Id = P.OrderId)
 LEFT JOIN `SALE` S ON (O.Id = S.OrderId);

Example #2

Let us look at a more complex, contrived example now. Suppose, for each Order object shown above, there exist Approvals. A Purchase would have a Purchase Approval and a Sale Approval, while a Sale would have a Sale Approval alone. Order_Approval_Mapping maps Orders to Approvals. A Store table has metadata pertaining to the stores.