Press "Enter" to skip to content

Category: Syntax

Finding the First of Many Similar Rows in SQL

Published 2024-02-29 by Kevin Feasel

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.

Comments closed

A Cheat Sheet for Joins

Published 2024-02-28 by Kevin Feasel

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.

Comments closed

Row Constructors in Postgres

Published 2024-02-28 by Kevin Feasel

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.

Comments closed

Auto-Commit in Postgres vs Oracle

Published 2024-02-28 by Kevin Feasel

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.

Comments closed

Counting Path Elements in T-SQL

Published 2024-02-05 by Kevin Feasel

Steven Sanderson switches over to T-SQL for a bit:

Yesterday I was working on a project that required me to create a SQL query to generate a table of accounts receivables pathways. I thought it would be interesting to share the SQL code I wrote for this task. The code is as follows:

Click through for the code. I was playing a bit of code golf in my mind, seeing how I might modify things. One big example would be to replace the STUFF() + FOR XML PATH combo with STRING_AGG(), assuming the SQL Server instance is 2017 or later, or if the database is in Azure SQL DB or SQL MI. The count of a specific character is an interesting approach, and this is the first time I’ve had to wonder why there isn’t a helper function in T-SQL to do that. But considering that this is the first time I’ve had to ask that question, maybe that’s an answer in itself.

Comments closed

UUIDs in Postgres

Published 2024-02-01 by Kevin Feasel

Ryan Lambert is not a fan:

This month’s PgSQLPhriday #015 topic is about UUIDs. Lætitia Avrot is this month’s host, see her post for the full challenge text. The topic is described as a debate between the Database People and Developers. I’m not sure there’s such a clean divide on people’s opinions on the topic, as I know plenty of Database People that have settled on using UUIDs as their default. Similarly, I know even more developer types that have followed the arguably more conventional choice of using an auto-incrementing ID.

Click through for the debate. Most of this is similar to the argument on the SQL Server side, though without the specter of clustered indexes added to the mix.

Comments closed

Replacing DISTINCT with EXISTS

Published 2024-01-30 by Kevin Feasel

Andy Brownsword makes a switch:

The DISTINCT clause in a query can help us quickly remove duplicates from our results. Sometimes it can be beneficial to stop and ask why. Why do we need to use the clause, why are we receiving duplicates from our data?

I see this typically due to a JOIN being used where we don’t really want all of those results. This could be a ‘does something exist’ check such as if a customer has ever ordered before. The issue comes when there are multiple rows returned like a frequent customer in this example.

As an alternative to this, Andy shows how you can use the EXISTS clause to find records matching some criterion.

Comments closed

What ANSI_NULLS Does

Published 2024-01-17 by Kevin Feasel

Chad Callihan answers one of the mysteries of the universe:

Have you ever seen ANSI_NULLS being set to ON or OFF in a script or stored procedure? I’ve seen or heard it mentioned a few times recently and thought it would be worth demonstrating an example of what this setting does.

Read on for the answer, including a truth table. But definitely keep ANSI_NULLS set to ON, as several important bits of functionality in SQL Server expect it that way.

Comments closed