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 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 readingMySQL 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.
In SQL Server, the BIT_COUNT()
function returns the number of bits set to 1 in the given argument, based on the binary equivalent of that argument.
Different values will have a different number of 1s in their binary value. Also, the same value can have a different number of 1s, depending on the data type.
Continue readingSQL Server 2022 introduced the SET_BIT()
function that sets a bit in an integer or binary value (other than a large object).
The function allows us to specify an offset for which to set a bit to 1
or 0
.
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 readingIn SQL Server, RIGHT_SHIFT()
is a bit manipulation function that returns the first argument bit-shifted right by the number of bits specified in the second argument.
The RIGHT_SHIFT()
function was introduced in SQL Server 2022.