As Markus Winand states on his website https://modern-sql.com, Modern SQL can be defined as "an internationally standardized, widely available and Turing complete data processing language supporting relational and non-relational data models." This definition refers to a set of standards that were promoted by the ISO and ANSI organizations over the years and that added new features to the SQL programming language. Since SQL-92, many new versions of the SQL standard were adopted and these standards introduced many new features based on relational and non-relational models. Here is a short list of these features with the corresponding standard that confirmed their adoption into the SQL language:
- WITH and WITH RECURSIVE (SQL:1999)
- CASE (SQL:1999 and SQL:2003)
- OVER AND PARTITION BY (SQL:2003 and SQL:2011)
- OVER AND ORDER BY (SQL:2003 and SQL:2011)
- GROUPING SETS (SQL:2011)
- JSON clauses and functions (SQL:2016)
- FILTER (SQL:2003)
- LATERAL queries (SQL:1999)
This being said, it should be noted that most of these features were not implemented by most relational database management systems (RDBMSs) until fairly recently. Most RDBMSs were only offering to their users, a more traditional SQL language based solely on the relational model promoted by the aging SQL-92 standard. It has only been in the most recent years that many, if not most, of RDBMSs have started implementing Modern SQL features.
Moreover, let's give this word of warning: using these features will not immediately yield great performance hikes for your database server. So, what is the point of using these features in your code base? The point is to make your code base compatible with future database engine optimizations and to avoid most problems related to slow query execution.
But, before looking further into the new SQL features, we will install phpMyAdmin inside our Linux for PHP container in order to see the results of our queries in a user-friendly fashion. To do so, please enter the following commands on the container's CLI:
# rm /srv/www
# ln -s /srv/fasterweb/chapter_6 /srv/www
# cd /srv
# wget -O phpMyAdmin-4.7.7-all-languages.zip https://files.phpmyadmin.net/phpMyAdmin/4.7.7/phpMyAdmin-4.7.7-all-languages.zip
# unzip phpMyAdmin-4.7.7-all-languages.zip
# cp phpMyAdmin-4.7.7-all-languages/config.sample.inc.php phpMyAdmin-4.7.7-all-languages/config.inc.php
# sed -i "s/AllowNoPassword'] = false/AllowNoPassword'] = true/" phpMyAdmin-4.7.7-all-languages/config.inc.php
# cd fasterweb/chapter_6
# ln -s ../../phpMyAdmin-4.7.7-all-languages ./phpmyadmin
These commands should make it possible to access the database server from a web interface at http://localhost:8181/phpmyadmin. When visiting this address via your favorite browser, you should see the following screen:
Once phpMyAdmin is installed, you can log in to the database server with the Username root and an empty Password.
Now, let's have a look at each one of these new SQL features in more detail.