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()".

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

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

Or maybe they’re on OLAP not OLTP.

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

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

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`.

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.

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?

Nice articles in there. Bookmarked.

Edit: it’s also actually a book!

> 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?

> 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.

Style opinions are borderline irrelevant without appropriate linters.

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

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

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;

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

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.

"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

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 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.

[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.

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 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 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.

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

> 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.

"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.

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

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

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

Are we on bizarro HN?

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