The problem is that SQL (in all its forms) has not been relational. Nor is it complete in terms of its querying ability. This means that it can do unexpected things and more so when you have nulls included in your data.
The Third Manifesto (by Date and Darwen) highlight many of the problems of SQL and SQL DBMS. Though I find TTM to be lacking in various ways, it does, at least, start heading in the right direction in terms of a database query system.
Over the course of decades, I have written much SQL and have had to modify and document many systems that were based on SQL DBMS's. The language is a PITA, but you do what you have to do to get things working.
NoSQL was, to me, a retrograde step, though it has some usefulness in some situations. What I have found is that most databases have not been designed with regard to Relational Database Theory (RDT). In general, the attitude that I have observed has been that RDT is good in theory but bad in practice.
I have built (using mickey mouse systems), fairly complex, multiuser databases based on RDT. I have also seen many databases built (using ORACLE and SQL Server as the DBMS) that have not used RDT and they have been atrocious. One such system, built by some SQL guru, took 25 hours to do a run of one process. Had it been built according to RDT, the entire process (by my estimate) would have taken less than 20 minutes to run.
When designing any kind of database using any of the various technologies that have been developed over the 60 odd years, there are various design principles that will allow a good database (fit for purpose) to be built. It appears in many cases that none of these principles are known or if know are know then not adhered to.
Of course, what others might have found can be quite different.
The Third Manifesto (by Date and Darwen) highlight many of the problems of SQL and SQL DBMS. Though I find TTM to be lacking in various ways, it does, at least, start heading in the right direction in terms of a database query system.
Over the course of decades, I have written much SQL and have had to modify and document many systems that were based on SQL DBMS's. The language is a PITA, but you do what you have to do to get things working.
NoSQL was, to me, a retrograde step, though it has some usefulness in some situations. What I have found is that most databases have not been designed with regard to Relational Database Theory (RDT). In general, the attitude that I have observed has been that RDT is good in theory but bad in practice.
I have built (using mickey mouse systems), fairly complex, multiuser databases based on RDT. I have also seen many databases built (using ORACLE and SQL Server as the DBMS) that have not used RDT and they have been atrocious. One such system, built by some SQL guru, took 25 hours to do a run of one process. Had it been built according to RDT, the entire process (by my estimate) would have taken less than 20 minutes to run.
When designing any kind of database using any of the various technologies that have been developed over the 60 odd years, there are various design principles that will allow a good database (fit for purpose) to be built. It appears in many cases that none of these principles are known or if know are know then not adhered to.
Of course, what others might have found can be quite different.