operators

Why GENERATE_SERIES() Only Returns the First Value in the Series in SQL Server

by

If you’re using the GENERATE_SERIES() function to create a series of numbers, but you’re finding that only the first value in the series is returned, it could be something very obvious.

The obvious reason this could happen is that your step value is too big. In particular, if the step is so big that it covers the whole series, then it stands to reason that there will only be one value in the series.

If this is an issue for you, you might want to check that you’re using an appropriate step value. Using a smaller step value can help to create a series with more values.

Read more

Introduction to the GENERATE_SERIES() Function in SQL Server

by

In SQL Server, the GENERATE_SERIES() function is a relational operator that returns a series of values between a given start and stop point. These are returned in a single-column table.

Although the GENERATE_SERIES() function only works with numeric values, we can combine it with other functions to create a series of dates.

The GENERATE_SERIES() function was introduced in SQL Server 2022 (16.x) and requires the compatibility level to be at least 160.

Read more

How the SQL UNION Operator Deals with NULL Values

by

The SQL UNION operator concatenates the results of two queries into a single result set. By default it returns distinct rows (i.e. it removes any redundant duplicate rows from the result set). But we can also use UNION ALL to return non-distinct rows (i.e. retain duplicates).

When it comes to NULL values, it’s pretty straight forward. SQL treats two NULL values as non distinct values. In other words, they’re duplicates.

Read more

Understanding the ->> Operator in MySQL

by

In MySQL, we can use the ->> operator to extract a value from a JSON document, and unquote that value.

We provide the column that contains the JSON document to the left of the operator, and the path to the value that we want to extract to the right.

The ->> operator is equivalent to JSON_UNQUOTE(JSON_EXTRACT()), and it provides the same results.

Read more

SQL Server CASE Statement

by

The SQL Server CASE statement evaluates a list of conditions and returns one of multiple possible result expressions.

Although it’s commonly known as the “CASE statement”, the SQL Server documentation lists it as an expression. Plus it’s referred to as the “CASE expression” in the SQL standard. Some other DBMSs distinguish between the CASE statement and the CASE expression. Either way, here’s a quick rundown on how the “CASE statement” (or CASE expression) works in SQL Server.

Read more