Or it’s simply an indicator of a schema that has not been excessively normalised (why create an addresses_cities table just to ensure no duplicate cities are ever written to the addresses table?)

That’s almost always my experience too.

Though fairly recently I learned that even with all the correct joins in place, sometimes adding a DISTINCT within a CTE can dramatically increase performance. I assume there’s some optimizations the query planner can make when it’s been guaranteed record uniqueness.

I've been told similar nasty things for adding LIMIT 1 to queries that I expect to return at most a single result, such as querying for an ID. But on large tables (at least in sqlite, mysql, and maybe postgress too) the database will continue to search the entire table after the given record was found.

It's the exact opposite in Cypher. I'm currently working with some complex data in neo4j, and wondered why my perfectly fine looking queries were so slow, until I remembered to use DISTINCT. It's very easy to get duplicate nodes in your results, especially when you use variable length relationships, and DISTINCT is the only fix I'm aware of that fixes that.

IDK, "which ZIP codes do we have customers in?" seems like a reasonable thing to want to know

Set theory...

There are self-identifying "senior software engineers" that cannot understand what even an XOR is, even after you draw out the entire truth table, all four rows.

PostgreSQL's `DISTINCT ON` extension is useful for navigating bitemporal data in which I want, for example, the latest recorded version of an entry, for each day of the year.

There are few other legitimate use cases of the regular `DISTINCT` that I have seen, other than the typical one-off `SELECT DISTINCT(foo) FROM bar`.

I'd be wary of overgeneralizing on that. I guess it depends on whose queries you're usually reading.

So how do you "know" when you can safely omit DISTINCT for your shiny new query SELECT x FROM t ?

Oh you looked the schema for t and it said x has a PRIMARY or UNIQUE constraint?

Ah well two minutes after you looked at the schema Tom removed the UNIQUE constraint. Now your scratching your head when you get duplicates.

Sql is a bag language not a set language. The contract with relation t is that if the runtime can find there rel t and attribute x it will return it. You may end up with rows or not, and you may end up with duplicates or not, and the type of x may change between subsequent execution.

So if you want a set you need to say so using DISTINCT. At runtime the query planner will check the schema and if the attribute is UNIQUE or PRIMARY it will not have to do a deduplication.

I'm not sure I understand the part about set theory. If anything, a valid use of DISTINCT is if you want the result to be (closer to) a set, as otherwise (to your point, depending on the data model) you may get a bag instead.

In fact, IIRC, using DISTINCT (usually bad for performance, btw) is an SQL advice by CJ Date in https://www.oreilly.com/library/view/sql-and-relational/9781...

In my experience, its nearly as often a problem with the design of the database as the query author.

Or maybe they’re on OLAP not OLTP.

Or believe more in Codd’s relational model than SQL’s tabulational model.

SQL is somehow "ask two people, get three different opinions" for something as basic as:

"given a BTreeMap<String, Vec<String>>, how do I do .keys() and .len()".

And that's okay. Not every developer knows every single thing there is to know about every single tech. Sometimes you just need a solution, and someone with more specific knowledge can optimize later. How many non-database related mistakes would you make if you had to build every part of a system yourself?

Or just doesn't know how to do semijoins in SQL, since they don't follow the same syntax as normal joins for whatever historical reason.

Eh, sometimes you need a quick fix and it’s just extremely concise and readable. I’ll take an INNER JOIN over EXISTS (nice but insanely verbose) or CROSS APPLY (nice but slow) almost every time. Obviously you have to know what you’re dealing with, and I’m mostly talking about reporting, not perf critical application code.

Distinct is also easily explained to users, who are probably familiar with Excel’s “remove duplicate rows”.

It can also be great for exploring unfamiliar databases. I ask applicants to find stuff in a database they would never see by scrolling, and you’d be surprised how many don’t find it.

Nice articles in there. Bookmarked.

Edit: it’s also actually a book!

"SQL database" doesn't describe anything. Variations of SQL have implementations on relational and non-relational databases. SQL and relational often get used interchangeably but given your goal you might want to use the terms more precisely.

Experts including Codd recognized the problems with SQL since that language got traction. Some alternatives got proposed, perhaps most notably Tutorial D by Chris Date and Hugh Darwen. No SQL replacement goes anywhere because of the vast quantity of SQL code and supporting tools dating back decades. Chris Date wrote the textbook on databases, and at least one book going through the problems with SQL and various implementations of the relational model.

SQL perfectly illustrates what Strostrup meant by "There are only two kinds of languages: the ones people complain about and the ones nobody uses." In some sense I would welcome a better query language. On the other hand I attribute decades of job security and steady income to knowing SQL and dealing with its problems.

Hey, this looks really cool! Best wishes and I’ll try to watch out for when this is more ready

> Also, understand how your DB handles nulls.

Also in regards to indexing. The DBs I've used have not indexed nulls, so a "WHERE col IS NULL" is inefficient even though "col" is indexed.

If that is the case and you really need it, have a computed column with a char(1) or bit indicating if "col" is NULL or not, and index that.

> Using != or NOT IN (...) is almost always going to be inefficient.

Why do you say that?

My understanding is that as long as the RHS of NOT IN is constant (in the sense that it doesn't depend on the row) the condition is basically a hash table lookup, which is typically efficient if the lookup table is not massive.

What's the more efficient alternative?

Style opinions are borderline irrelevant without appropriate linters.

Looking up the etymology of "sargeable", I found this StackOverflow answer: https://dba.stackexchange.com/a/217983

And Google explains "The term 'sargable' is a portmanteau of "Search ARGument ABLE," formed by combining the words from a SQL database context."

I'm sceptical of that article, it's making guesses about the limitations of SQL query optimisers.

Consider the Simple example it presents. The article is in effect implying that no query optimiser would be able to figure out the equivalence of the two predicates.

(Let's ignore that the two predicates aren't actually equivalent; the first version may raise an exception if myIntColumn is negative, depending on the DBMS.)

I'm really curious, what communities use that word?

I've been working with SQL for 20+ years, and have literally never come across that word a single time in any documentation, tutorial, Stack Overflow answer, or here on HN. Working in Postgres, MySQL and SQLite.

Is it used at some particular company, or open source community, or with a particular database, or something?

Non sargability easy to solve with expression indexes. At least in sqlite.

Some well known docs on the topic- https://use-the-index-luke.com/sql/where-clause/obfuscation

The given solution (create an indexed UPPER(name) column) is not the best way to solve this, at least not on MS SQL Server. Not sure if this is equally supported in other databases, but the better solution is to create a case-insensitive computed column:

  ALTER TABLE example ADD name_ci AS name COLLATE SQL_Latin1_General_CI_AS;

"Unfortunately I learned this the hard way!" ... Seems to be the motto of SQL developers.

Otoh, it seems a fairly stable language (family of dialects?) so finding the pitfalls has long leverage

The blog has a typo. The first line needs to have the text in uppercase:

> query WHERE name = ‘ABC’

> create an indexed UPPER(name) column

The point is that the index itself is already on the data with the function applied. So it's not a full scan, the way the original query was.

Of course, in this particular example you just want to use a case-insensitive collation to begin with. But the general concept is valid.

[and a third] OR use a case-insensitive collation for the name column.

I think they reversed the 2 expressions. You should use “WHERE UPPER(name) = ‘ABC’” if you want to use the index.

I don't see anything wrong with what the article is saying. If you have a view over a join of A and B, and the view uses "select *", then what is gonna happen when A adds a column with the same name as a column in B?

In sqlite, the view definition will be automatically expanded and one of the columns in the output will automatically be distinguished with an alias. Which column name changes is dependent on the order of tables in the join. This can absolutely break code.

In postgres, the view columns are qualified at definition time so nothing changes immediately. But when the view definition gets updated you will get a failure in the DDL.

In any system, a large column can be added to one of the constituent tables and cause a performance problem. The best advice is to avoid these problems and never use "select *" in production code.

The reasoning is in the article, and true.

> Schema evolution can break your view, which can have downstream effects

Select * is the problem itself in the face of schema evolution and things like name collision.

`select *` is bad for many reasons, but the biggest is that the "contract" your code has with the remote data store isn't immutable. The database can change, for many different reasons, independent of your code. If you want to write reliable code, you need to make as few assumptions as possible. One of those assumptions is what the remote schema is.

If you have select * in your code, there already is something wrong with your code, whether it breaks or not: the performance and possibly output of your code is now dependent on the table definition. I'm pretty sure Rich Hickey has also talked about the importance of avoiding non-local dependencies and effects in your code.

This is not a pipeline in the control flow sense; the full query is compiled into a single processing statement, and the query compiler is free to remove and/or reorder any of the subqueries as it sees fit. The intermediate results during query execution (e.g. temp table spools) do not follow the structure of the original query, as CTEs and subqueries are not execution boundaries. It's more accurate to compare this to a C compiler that performs aggressive link-time optimization, including new rounds of copy elision, loop unrolling and dead code elimination.

If you want to build a pipeline and store each intermediate result, most tooling will make that easy for you. E.g. in dbt, just put each subquery in its separate file, and the processing engine will correctly schedule each subresult after the other. Just make sure you have enough storage available, it's not uncommon for intermediate results to be hundreds of times larger than the end result (e.g. when you perform a full table join in the first CTE, and do target filtering in another).

I wrote some tooling to help debug sql queries with many CTEs. It parses the sql, finds all the CTEs, and prints the result of each CTE formatted as csv. If the .sql file changes on disk, it reruns the query and tells you which CTEs’ output changed. Saved me hours in debugging.

> Are there any debuggers that let you look at intermediate results of pipelines without modifying the code?

F# in the visual studio debugger does a pretty good job of this in recent versions.

I usually use generated columns for this. It still uses CASE WHEN but it is obvious to all consumers of the table that it exists.

I don't really understand the problem in 1

in 2, looking at your article, from your first query it looks like person_relationship contains both (A,B) and (B,A) for all related people A and B; otherwise the left join won't work. If you also make people related to themselves and store (A,A) and (B,B) there your query becomes much simpler:

    SELECT other.id, other.name
    FROM person p
      JOIN person_relationship r ON r.from_person_id = p.id
      JOIN person other ON r.to_person_id = other.id
    WHERE p.family_id = @familyId;

Your own article points out that exists handles the first case. Exists is not actually implemented as a subquery, it is merely syntactically a subquery.

Why do you need a warm standby for production? Do you need >= 3 nines?

Our staging environment has its own instance that is rebuilt from prod, with pii removed, every day outside working hours (this normally takes about 15 minutes). It’s fantastic for testing migrations, and is easy to support compared with a warm standby.

I'd like to call views, triggers, and integrity constraints antipatterns.

Your code should handle the data model and never allow bad states to enter the database.

There's too much performance loss and too many footguns from these "features".

I guess things are DB dependent. Spanner for instance not only recommends using uuidv4 as a PK, it also stores it as string(36). Uuidv4 as a PK works fine on Postgres as well.

Can you share any details? A teammate wants to change primary identifiers to a GUID, but I'm not sure it's a good idea.

"Dimension table" is the name for lookup tables in a star or snowflake schema.

but sometimes large case statements cant be turned into a simple dimension table/lookup table because it's not a simple key-value transformation.

if your case statement is just a series of straighahead "WHEN x=this THEN that", you're very lucky.

the nasty case statements are the ones were the when expression sometimes uses different pieces of data and/or the ordering of the statements is important.

> what I like to do is putting 1=1 after each WHERE to align ANDs nicely

Frankly, that sounds like one of those things that totally makes sense in the author’s head, but inconsiderately creates terrible code ergonomics and needless cognitive load for anyone reading it. You know to just ignore those expressions when you’re reading it because you wrote it and know they have no effect, but to a busy code reviewer, it’s annoying functionless clutter making their job more annoying. “Wait, that should do nothing… but does it actually do something hackish and ‘clever’ that they didn’t comment? Let’s think about this for a minute.” Use an editor with proper formatting capability, and don’t use executable expressions for formatting in code that other people look at.

It's always perfectly aligned for me, because enter prefixes 2 whitespace in my ide in SQL files, ending with

    where a=1
      And k=2
      And v=3

Are we on bizarro HN?

No, you ask the DB to EXPLAIN itself to you.

I agree. Modern code models tend to do a great job advising in SQL, especially if you include the table definition and EXPLAIN output in the context. Alternatively, I've found that an EXPLAIN MCP tool works well.

https://pragprog.com/titles/bksqla/sql-antipatterns/ There's an actual book on them that had me nodding along the entire time.

Still waiting for the definitive article on how using the term anti-pattern is an anti-pattern.

I'm waiting for the anti-patterns we shouldn't avoid.