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').