we who use these databases for real problems need LEFT OUTER JOIN and to use LEFT OUTER JOIN you need NULL.
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: []
But yes, SQL doesn't work well with this solution.
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 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: []
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').
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?