Most of the major RDBMSs provide us with the ability to perform set operations.
A set operation is one that combines the results of multiple query blocks into one. For example, we could combine the result of two separate SELECT statements. The results are displayed in a single result set as though we’d run a single query.
In MySQL we can use the DESCRIBE statement to get information about table structure in the database, or to obtain a query execution plan for a query. For example, we can get information about column names, their data types, default values, etc.
The DESC and EXPLAIN statements are synonyms of DESCRIBE, so we can alternatively use those to do the same thing.
In MySQL we can use the SHOW COLUMNS statement to get information about the columns in a table or view.
We can narrow the columns down to just a certain table and database. We can also filter by column name using a pattern.
The SHOW COLUMNS statement only shows information about columns that we have some privilege.
In MySQL 8.0.23 and later, we have the ability to create invisible columns when creating or altering a table.
An invisible column is one that is hidden to certain SQL queries, but can be accessed if explicitly referenced.
Basically, invisible columns are hidden to queries that use the asterisk wildcard (*) in their SELECT list. But they can be accessed by explicitly referencing the column by its name.
Invisible tables are hidden from the TABLE statement, as this statement provides no way of selecting individual columns.
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.
If you’re getting error 10753 when using a window function in SQL Server, it’s probably because you’re calling the function without an OVER clause.
When using the DENSE_RANK() function, the error message reads “The function ‘DENSE_RANK’ must have an OVER clause”.
The DENSE_RANK() function requires an OVER clause (and that clause must have an ORDER BY clause).
To fix this issue, add an OVER clause to the DENSE_RANK() function.
Many 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.
SQL Server 2022 introduced a bunch of new bit manipulation functions and operators. Amongst these are the right shift and left shift operators.
The right shift and left shift operators can be used to shift bits to the left or right. Given an integer or binary expression, these operators can shift bits by the amount that we specify.
Continue readingIn SQL Server, we can use the GET_BIT() function to return the bit from the specified location in a value. We pass the value as either an integer or binary expression.
The function accepts two arguments; the expression, and the offset. The function then returns the bit from the given offset in the expression.
Continue reading