From MySQL 8.0.23 we’ve had the ability to create invisible columns. But with that comes a potential “gotcha” with our existing queries, and even with new queries we write, depending on how we write them.
The issue is that invisible columns are not returned whenever we use the asterisk wildcard (*) to select data. One of the most common ways to start a query is with SELECT *. This typically selects all columns from the table.
But it doesn’t select invisible columns.
So if we want to include invisible columns, we need to explicitly include them in our SELECT list.
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.
When 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.
When we use the VALUES statement to create a constant table, PostgreSQL automatically names the columns column1, column2, etc. This saves us from having to provide names for the columns or from getting a blank column header.
But we also have the option of naming the columns ourselves.
Continue readingIf you’ve ever used the VALUES clause as a stand alone statement, you may have noticed that SQLite provides default column names for the results. SQLite conveniently names them column1, column2, and so on.
However as convenient as this is, you might want to provide names that are more meaningful.
Fortunately there’s an easy way to do that.
Continue readingSome database management systems, including SQLite, allow us to use the VALUES keyword as a stand alone SQL statement. So we can use VALUES to return a constant table, usually with less code than would be required if we were to use the SELECT statement.
The following examples demonstrate how.
Continue readingMany of us who work with SQL databases have used the VALUES keyword with the INSERT statement when inserting data into a database.
But perhaps a lesser known fact is that some RDBMSs allow us to use VALUES as a stand alone SQL statement. PostgreSQL is one such RDBMS.
When used as a stand alone statement, we can use a VALUES statement instead of a SELECT statement.
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.
Many RDBMSs support the concept of named windows – windows that we can reference from within an OVER clause when defining a window function.
For those of us using SQL Server, we had to wait until the release of SQL Server 2022 before we could create named windows for our window functions. But with the release of SQL Server 2022, we now have the option of using named windows in our window functions.
Continue reading