Press "Enter" to skip to content

Category: Syntax

Using IN and NOT IN in SQL Server

Erik Darling shares some advice:

I’ll be brief here, and let you know exactly when I’ll use IN and NOT IN rather than anything else:

  • When I have a list of literal values

That’s it. That’s all. If I have to go looking in another table for anything, I use either EXISTS or NOT EXISTS. The syntax just feels better to me, and I don’t have to worry about getting stupid errors about subqueries returning more than one value.

I’m typically a lot more flexible about using IN, though I do agree with NOT IN: that clause is usually more trouble than it’s worth.

Leave a Comment

Postgres Data Extraction with LATERAL joins and More

Ryan Booz extracts some data:

In our data hungry world, knowing how to effectively load and transform data from various sources is a highly valued skill. Over the last couple of years, I’ve learned how useful many of the data manipulation functions in PostgreSQL can supercharge your data transformation and analysis process, using just PostgreSQL and SQL.

For the last couple of decades, “Extract Transform Load” (ETL) has been the primary method for manipulating and analyzing the results. In most cases, ETL relies on an external toolset to help acquire different forms of data, slicing and dicing it into a form suitable for relational databases, and then inserting the results into your database of choice. Once it’s in the destination table with a relational schema, querying and analyzing it is much easier.

I call out CROSS JOIN LATERAL (or any kind of lateral join) here because it’s the ANSI equivalent of T-SQL’s APPLY operator, and I’ve already pointed out once today that I’m a huge fan of APPLY.

Leave a Comment

Using the APPLY Operator

Erik Darling gets an auto-link for talking about my favorite operator:

I end up converting a lot of derived joins, particularly those that use windowing functions, to use the apply syntax. Sometimes good indexes are in place to support that, other times they need to be created to avoid an Eager Index Spool.

One of the most common questions I get is when developers should consider using apply over other join syntax.

The short answer is that I start mentally picturing the apply syntax being useful when:

To learn when, you’re going to have to read the whole thing. And, if you want to learn even more about it, I have a talk on the topic that might be of interest.

Leave a Comment

Comparing TOP(1) + ORDER BY vs MAX() Performance in SQL Server

Andy Brownsword breaks out the stopwatch:

The TOP clause limits the number of results which are returned from a query, in this instance we’re focussing on a single result. In contrast, when using MAX we’re applying a function to our data to select the largest value from our data.

Let’s dive into some examples with the StackOverflow data, specifically the Votes table.

Read on for several scenarios and how the two perform. Things get a bit more complicated as you introduce other tables in joins and similar additional factors, but this gives you a good foundation for comparison.

Leave a Comment

EXISTS and NOT EXISTS in T-SQL

Erik Darling plays Descartes in his spare time:

Let’s talk about two of the most overlooked and undervalued facilities in the SQL language: EXISTS and NOT EXISTS. Perhaps they’d get more traction is they were called THERE and NOT THERE, but but it would be perilously easy to start confusing your WHERE and THERE clause.

Often besmirched by someone who read a blog post about MySQL in 1998 as “subqueries, which are slower than joins”, they’re two of the most useful things you can grasp and use regularly.

This post does a great job of explaining when you’d want to use EXISTS and NOT EXISTS.

Leave a Comment

Find and Replace Text Strings in SQL

Ed Pollack deals with the lack of regular expressions in T-SQL:

This article is focused on how to write, simplify, and get the most out of string data using the least amount of code to do so. It’s a reminder that code complexity is equivalent to technical debt and that in these scenarios, less is always more.

Text-manipulating functions will be introduced and reviewed throughout this article, so feel free to perform searches against it to find what you are looking for.

Read on for mechanisms to split strings into multiple rows, build strings from multiple rows, and search+replace within strings.

Leave a Comment

Finding the First of Many Similar Rows in SQL

Tobias Petry shows off a feature in Postgres:

SQL is a straightforward and expressive language, but it sometimes lacks constructs for writing queries in a simple way. It’s more complicated than it should be to write a query to get, e.g., only the most expensive order for every customer of the current fiscal year. You can either use PostgreSQL’s vendor-specific DISTINCT ON feature or window functions for every other database.

Click through for the normal method (which works for MySQL, Oracle, SQL Server, etc.) and the Postgres way.

Leave a Comment

A Cheat Sheet for Joins

Erik Darling does the academically correct thing and makes all of his joins CROSS JOINS and then performs all filters in the WHERE clause:

Think of your standard CRUD operations. Most don’t even require a join; they’re very straightforward. And hey, if you completely denormalize all your data to one huge table, you’ll never have to think about a lot of this stuff anyway.

It’s only when developers are forced to think about things that things start to go wrong. I don’t mean to pick on developers specifically. It’s the human condition. Thinking often leads to poor choices.

In this post, I’m going to give you some basic guidance on when to use various T-SQL facilities, based on years of finding, fixing, and writing queries.

You get a free second quotation because I love this line:

I’m not going to talk about right outer joins, because that’s the foolish domain of characterless buffoons who use Venn diagrams to explain join results.

If I were emotionally capable of crying, that would bring a tear to my eye because it’s savagery against two things I hate.

Leave a Comment

Row Constructors in Postgres

Deepak Mahto builds some rows:

A ROW expression allows you to construct ROW values, which can represent anonymous records, specific table row types, or custom composite types. Its uses include processing records within multiple expressions (using operators like =, <>, <, <=, >, or >=), evaluation with subqueries, and combining ROW values to build composite types. You can even export these to JSON format (using functions like rowtojson). Some key operations you can perform with a ROW constructor in Conversion or migration are :

Click through for that list and several examples.

Leave a Comment

Auto-Commit in Postgres vs Oracle

Akhil Reddy Banappagari explains one difference between Oracle and Postgres:

Oracle and PostgreSQL differ significantly in their transaction models, and AUTOCOMMIT is one of those differences. We see many of our customers successfully migrate code, but still encounter variations in behavior and even runtime errors related to transaction control. Many issues happen because AUTOCOMMIT settings differ between Oracle and PostgreSQL. It is definitely necessary to understand the distinctions in AUTOCOMMIT between Oracle and PostgreSQL to ensure a successful migration.

SQL Server also has auto-commit on by default for implicit transactions like in these examples.

Leave a Comment