If you want a list of employees each of which have different roles, and depending on the role you have different fields guaranteed to be available (not null), this is not possible to express directly in SQL.

You can express this with sum types in ML, Haskell or Rust.

In SQL you would have to split it out into separate relations, like:

create table employee(id it)

And then employee_boss, employee_dev, employee_sales which each have different sets of non-null fields.

You can express a foreign key constraint that these tables must link to an employee, but not the other way around. You can’t guarantee in the limited type system of SQL that every employee row has a corresponding employee_X table, and exactly one, not more.

This is trivial in languages with sum types but not in SQL. In this sense they are strictly more powerful.

Various triggers can be added to try to enforce this constraint, but that’s a runtime check: better languages tell you when you type check the code.

True. (You can in some databases using check constraints, but it is not necessarily simple) The solution is to improve the constraint model to make it much simpler to define such constraints. For example it is trivial (in most databases) to add primary key and foreign key constraints, but more complex constraints are difficult or impossible.

I think the desire for tagged unions are really an XY problem, just like the desire for array types and other composite types. The relation type itself is powerful enough. But the desire for such features show that certain patterns are too complex and un-intuitive to apply in SQL.

in this case instead of an employee table you would have an employee view that’s the union of the separate tables - engines vary dramatically in what sort of constraints they’ll enforce on the view now but the relational model is fine