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 usingWITH
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, advancedWITH 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 toGROUP 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 toGROUP 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 likeSUM()
orAVG()
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 byJOIN
ing tables and filtering rows. Now you can use more readableINTERSECT
andEXCEPT
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 anyALTER TABLE
query. Columns usingJSON
data type and can be manipulated as string or using specialJSON_*()
functions. Database server will check validity of submitted JSON string, will be able to read its content usingJSON_*()
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 columnIS NULL
). -
+
/-
operators for date/datetime manipulation: Simply put, you can usedate_column + interval 1 DAY
instead ofDATE_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 likeINSERT
except the case when a row with the same primary key value already exists in the database. In such situationREPLACE
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.
Top comments (0)