7 modern and practical MySQL/MariaDB features you should know and use

When programming we always tend to look up new interesting syntax features of our main programming language. With SQL database systems systems however we are not so eager, although we use use SQL almost every day. MySQL and MariaDB are constantly evolving and some of the newly added features can help you a lot. Unfortunately the most useful things in changelogs are often hidden among other not so important changes, like storage engines optimizations, adding specific new data types or changing the behaviour of certain configuration variables.

This short list include 7 important new functionalities which make your life simpler. For practical examples, please see provided links. Since MySQL and MariaDB have diverted from each other, I will link each of them individually. Some functionalities are available in MariaDB only.

• WITH clause (common table expressions) (since MySQL 8.0, MariaDB 10.2.1): By using WITH you can define derived tables at the beginning of your query. This way subqueries are not nested in the main query, which makes the whole query much more readable. Moreover, advanced WITH RECURSIVE clause lets you define recursive algorithms for processing table values (can be used for sequences generation, hierarchical data traversal etc.). See MySQL ref for detailed explanation and examples.
• OVER clause and window functions (since MySQL 8.0, MariaDB 10.2): Similarly to GROUP BY, OVER clause enables you to define a groups of rows (window) and work with column values in each group using window functions. In constrast to GROUP BY, the resulting set of rows remains the same, i.e. rows are not grouped in the result. This way you can use classical aggregate functions like SUM() or AVG() on groups of rows (e.g. groups of comments belonging to the same blog post) and display the agregate function's return value in a column (e.g. sum of comment's likes numbers), but without actually grouping the results. Using specialized window functions (MySQL ref, MariaDB ref) you can do even more, like obtain first/last column value in a group, obtain number of row in a group or obtain previous/following value in a group. The latter gives you exceptional ability to compare values of adjacent rows.
• INTERSECT/EXCEPT (since MariaDB 10.3 - INTERSECT, EXCEPT): In the past intersect and except (difference) operations needed to be done manually by JOINing tables and filtering rows. Now you can use more readable INTERSECT and EXCEPT operators.
• VALUES statement (table values constructor) (since MySQL 8.0, MariaDB 10.3.3): This practical feature lets you create a table on the fly using literal row values, e.g. SELECT * FROM table UNION VALUES (10, 20), (30, 40).
• CREATE SEQUENCE statement (since MariaDB 10.3): While being standard for a long time in many other database systems, MariaDB has adopted sequences recently. They allow you to define a custom way of auto incrementing column values (e.g. incrementing it by addition of 2 instead of 1 etc.).
• JSON data type (since MySQL 5.7, MariaDB 10.2.7): In an effort to bring SQL databases closer to noSQL world, RDBMS servers are trying to facilitate JSON data handling. This enables you to combine solid relation database schema with schemaless JSON fields, which are similar to document-oriented databases. You can add or remove attributes from your JSON data without any ALTER TABLE query. Columns using JSON data type and can be manipulated as string or using special JSON_*() functions. Database server will check validity of submitted JSON string, will be able to read its content using JSON_*() functions with JSONPath syntax and it is even possible to put indexes on individual JSON attributes. MySQL in addition offers operators -> and ->> to access JSON column content. Implementations of JSON support in MySQL and MariaDB differ. For MySQL see JSON Data Type overview, for MariaDB see JSON With MariaDB platform article.
• Temporal tables (since MariaDB 10.3.4/10.4.3): Several functionalities which help you to read and write data within versioned tables (i.e. tables with records storing date and time ranges). You can easily query rows data valid in certain time point or time span. When updating or deleting data for specified date range, rows are automatically split, shrunk or deleted. This part would be very hard to implement on the application side, so why not to use native database server functionality?

In the end, I will mention some older but useful features which are not commonly known:

• Null-safe equal operator <=> (MySQL ref, MariaDB ref): Allows to easily compare nullable columns (without the need to test if column IS NULL).
• +/- operators for date/datetime manipulation: Simply put, you can use date_column + interval 1 DAY instead of DATE_ADD(date_column, INTERVAL 1 DAY).
• Row subqueries (MySQL ref, MariaDB ref): When using subqueries in the WHERE clause, more then 1 value can be returned from the subquery and compared, e.g. WHERE (name,age) IN (SELECT name, age FROM customer).
• REPLACE statement (MySQL ref, MariaDB ref): Acts like INSERT except the case when a row with the same primary key value already exists in the database. In such situation REPLACE will delete the existing row before inserting new one.
• #/-- comments syntax: Putting a hash or two dashes and a whitespace into SQL will mark as comment everything to the end of the current line. It is more practical than classical pair /* ... */ comments syntax.

I hope these tips will help you to write more readable and powerful SQL queries.