MySQL has the handy TABLE statement that allows us to get the contents of a table with less code than what we’d need if we’d used the SELECT statement.
When we use the TABLE statement, it returns the columns using the actual column names. In most cases this is fine, and there’s no need to change anything. But sometimes we might want to rename one or more columns. In such cases, we can use the following techniques to achieve that outcome.
When combining multiple query blocks in MySQL, we can use the EXCEPT clause to return just those rows from the first block that are not present in the second block.
If you’re getting an error that reads “The used SELECT statements have a different number of columns” in MySQL, it’s probably because you’re using the EXCEPT, INTERSECT, or UNION clause, but with a different number of columns for each query.
When we use these clauses, both queries must select the same number of columns. For example, if the first query has two columns in its SELECT list, then the second query must also have two columns in its SELECT list.
To fix, make sure both queries select the same number of columns.
Continue readingWhen combining multiple query blocks in MySQL, we can use the INTERSECT clause to return just those rows that are common to both query blocks.
It’s a bit like the UNION clause, except that it excludes rows that aren’t present in both queries.
MySQL 8.0.19 introduced the VALUES DML statement, which is a table constructor that can be used as a stand alone SQL statement.
This is not to be confused with the VALUES keyword that’s used with the INSERT or REPLACE statements when inserting data (although it can be used for that purpose too). It also shouldn’t be confused with the VALUES() function that’s used with INSERT … ON DUPLICATE KEY UPDATE statements.
MySQL has a LAG() function that we can use to return the value of a previous row of our choosing, based on how many rows back it is from the current row.
Armed with that value, we can then calculate the difference between it and the current row’s value.
Continue readingMySQL has a bunch of functions that enable us to get values from another row in the same column. This makes it easy for us to do stuff like calculate the difference between a value in the current row and one in another row, even if it’s in the same column.
Here are five options for calculating the difference between a value in the current row and a value in a different row but in the same column.
Continue readingMySQL includes some nonaggregate window functions that allow us to get a value from a specific row. We can use such functions to do things like, compare the value in the specified row with the value in the current row, even if both values are in the same column.
Below are five functions that we can use to do this.
Continue readingNormally if we try to insert data that violates a CHECK constraint in MySQL, we get an error, and the whole INSERT operation fails (including any conforming rows).
But it’s possible to change this, so that any conforming rows are inserted, and only the non-conforming rows are skipped.
We can do this by using the IGNORE clause.
In MySQL, we can use the sys.ps_is_consumer_enabled() function to check whether a given Performance Schema consumer is enabled.
The function returns YES or NO, depending on whether or not the specified Performance Schema consumer is enabled. It returns NULL if the argument is NULL.