operators

PIVOT vs GROUP BY in SQL Server

by

If you’re new to SQL or just starting to explore data transformation techniques, you might be confused about when to use PIVOT versus GROUP BY. They may seem similar at first. After all, both aggregate data, both can summarize information, and both produce condensed result sets. But they serve fundamentally different purposes and produce very different output structures.

Understanding the distinction between these two operations will help you write more effective queries. GROUP BY aggregates data vertically, keeping your results in a row-based format. PIVOT transforms data horizontally, turning row values into column headers. Let’s break down exactly what each does and when you’d use one over the other.

Read more

What is a UNION ALL in SQL?

by

UNION ALL is SQL’s way of combining result sets from multiple queries without worrying about duplicates. If you’ve used UNION before, UNION ALL is its faster, less fussy sibling that keeps every single row from all your queries, even if some rows are identical.

So UNION removes duplicate rows automatically, while UNION ALL keeps everything.

Read more

What is a UNION in SQL?

by

If you ever find yourself needing to combine the results from multiple SELECT statements into a single result set, UNION is probably going to be the tool for the job. By “UNION“, I mean the UNION operator.

The UNION operator takes the output from two SELECT queries and stacks them on top of each other. It basically merges two lists into one, removing any duplicates along the way.

Read more

Detecting and Filtering Special Characters Using PATINDEX() and LIKE in SQL Server

by

Working with real-world data often means dealing with messy strings. It’s common to find values that contain unexpected special characters. Sometimes this is due to user input, sometimes it’s from imports or third-party sources.

Either way, when we need to find and filter these special characters, SQL Server gives us some handy tools to work with. For starters, there’s the LIKE operator, which anyone who’s used SQL would be familiar with. But there’s also the PATINDEX() function, which performs a slightly different task.

Read more

7 Ways to Extract Data from JSON in DuckDB

by

Most DuckDB distributions come with the JSON extension, and this extension is loaded upon first use. That means we can go ahead and run queries against JSON data right out of the box. One common task we’ll face when working with JSON is extracting data from within the JSON documents. This can include extracting scalar values, or extracting nested JSON from within the outer document.

DuckDB provides us with multiple ways to extract such data. The option we use will largely depend on our use case. Either way, here are seven options for extracting data from JSON documents in DuckDB.

Read more

Quick Overview of the List Operators in DuckDB

by

DuckDB provides us with a bunch of functions and operators that we can use when working with lists. These include operators for doing things like concatenating lists to performing calculations on the lists. Most of these operators are aliases for a corresponding function, so we can choose which one to use in those cases.

This article provides an overview of the six list operators available in DuckDB, along with basic examples to demonstrate their usage.

Read more

Using Shorthand to Perform Data Conversions in DuckDB

by

When it comes to converting between data types, DuckDB performs implicit conversions when required, while also enabling us to perform explicit conversions. Implicit conversions are performed automatically by DuckDB when we do some other operation, such as use a function that requires its argument/s in a different data type than the one we’re providing. Regarding explicit conversions, we have the option of using a function like cast() or try_cast(), or using the shorthand method.

In this article we’ll take a quick look at how to convert between data types using the shorthand method.

Read more

An Important Consideration When Choosing Between LIST_CONCAT() and || For Concatenating Lists in DuckDB

by

When it comes to concatenating lists in DuckDB, we can use the list_concat() function (and its aliases), or the list concatenation operator (||). Often when we have a choice like this, we can use either option interchangeably. But that isn’t quite true in this case.

There’s an important difference between list_concat() and the || operator in DuckDB, and it all comes down to how it handles NULL values.

Read more

Understanding SQLite’s CAST() Expression

by

SQLite is a widely-used lightweight database engine that powers many mobile, embedded, and desktop applications. One of its key features is its dynamic type system, which allows flexibility in handling data. While this is advantageous in many scenarios, there are cases where developers need precise control over how data is treated or converted between types. This is where SQLite’s CAST() expression comes in.

Read more

Using the WHERE Clause Effectively: Common SQL Operators and Their Usage

by

The WHERE clause is a fundamental part of SQL queries that allows us to filter data based on specific conditions. Understanding how to use various operators within the WHERE clause can significantly enhance our ability to retrieve precisely the data we need.

This article explores some of the operators that are most commonly used with the WHERE clause.

Read more