There are other ways, like having a null with more regular semantics and better comparability (like Rust's option type).

Or imagine this data:

Students:

Name: Bob

Name: Alice

StudentsClasses

Student: Bob, class: cryptography.

Student: Bob, class: databases

If you can have arrays (or relations) as a field in your result set, then

Students left outer join StudentsClasses

Could return

Student: Bob, classes: [ cryptography, databases]

Student: Alice, classes: []

This violates first normal form. So if you want to query this result further you need a more complex language than relational algebra because you need to be able to query nested structures.

The normal forms are about how data is stored, rather than how it looks after a query. After all, most joins will violate second normal form.

But yes, SQL doesn't work well with this solution.

1nf is surprisingly tricky to define.

If you consider it as a single atomic list then it is still in 1nf.

It's only not in 1nf with respect to future queries IF you want to subselect from within the list values.

1NF is defined as eliminating nested relations. I assume "[ cryptography, databases]" in the example represent a nested relation.

1NF is not defined in terms of what you want to do with the the data, it is defined in terms of domains, i.e data types. If a column allows nested tables, it violates 1NF.

"Atomic" in the context of 1NF means atomic wrt. the relational algebra.

Eg. if we have:

   Student: Bob, classes: [ cryptography, databases]
   Student: Alice, classes: [] 

Is it possible to select from this all students which has cryptography as a class, using the standard relational operators (project, filter, join)?

I think we're mostly in agreement!

You're right, it's not possible to select from this all students which has cryptography as a class. No more than if we did concat('cryptography', ',', 'databases').

what if I gave you a nice little language where you could easily define transforms on tables and explicitly specify 'a where a.key != b.key'. and a type system that would let you define null and all its various arithmetics.

sure, you'd say, but SQL already does all that

but you've have well thought out type construction, and a standard way of defining abstractions and transformations on data. maybe it would be general enough that you would be tempted to go full-on stored procedure and forget about having to juggle two languages and their interface

still no?