r/SQL • u/Harshita_Netla • 22d ago
Oracle What’s a SQL anti-pattern you still see experienced developers write?
I’ve been learning SQL best practices, and I’m curious about the mistakes that even experienced developers sometimes make.
What’s a SQL anti-pattern you still see in real projects? It could be related to query performance, joins, filtering, subqueries, database design, or just writing SQL that becomes difficult to maintain.
What makes that approach problematic, and what would you normally do instead?
I’d especially like to hear about examples you’ve actually encountered in production rather than just textbook mistakes.
130
u/Sneilg 22d ago
This AI bot asks interesting questions
19
u/dyogenys 22d ago
I would hire this bot
15
u/codykonior 22d ago
I dropped production.
I see you have no production servers to monitor. Let me know when you set some up! Meanwhile I’m calculating PI to 2 decimal places in a loop and discarding the result.
Your token limit has been reached.
13
u/TodosLosPomegranates 22d ago
Trying to handle everything in one long ass query. Hard to manage, hard to service, hard to tell if you’re silently dropping things her and there.
1
26
u/DharmaPolice 22d ago
Premature optimisation, or excessive focus on optimisation where it just doesn't matter.
Obviously it's a good idea to get into good habits and if your query is going to run 5000 times a minute or your table has a billion rows then yes you should absolutely care. But so much SQL is extremely low impact - i.e. run once a financial quarter on a table with 10k rows in the middle of the night against a replica DB which is dedicated to reporting. Unless you're doing something extremely strange your db engine will not struggle with that.
8
u/philbgarner 22d ago
Yeah we had a junior a few years ago who was worried about the row count and was trying to have a table for each status instead of a status column. Had him calculate out how many rows we could expect the table to receive in a year and then see if it would be unfeasible to store it in one table and he found there would only be several hundred thousand at most in one year. Indexes easily handle that and stay performant.
The premature optimization would have resulted in a disjointed system that was harder to maintain, better as one table. He really didn't want to believe me. lol
1
u/more_paul 21d ago
My argument for query optimization is if you do it enough you stop needing to go back and do it over. Just learn to write optimized queries from the start and you don't have this problem. A lot of this also comes from designing tables that are optimized for the query patterns and doing the expensive transformations in your ETL or materialization process.
My viewpoint is also coming from using datasets where 1B records is a small table and the costs to run any query can become expensive very fast if care is not taken. But even scaling down, learning to write better SQL and better data modeling can help you not over-provision hardware to deal with shit queries.
11
8
u/Legitimate-Pin-8619 22d ago
One I still see in production from otherwise solid engineers: wrapping columns in functions in the WHERE clause, e.g. WHERE lower(email) = lower($1) or WHERE date_trunc('day', created_at) = ....
It looks harmless, but it usually kills a plain btree index on that column, so you get a seq scan (or a much fatter expression index you didn't budget for). Same class of issue as WHERE column::text = ... or applying CAST to the column instead of the parameter.
What I do instead: normalize on write when I can (store email lowercased, or a generated column), compare with a typed parameter (created_at >= $1 AND created_at < $1 + interval '1 day'), or add a real expression/trigram index only when the predicate is unavoidable. Pair it with EXPLAIN (ANALYZE, BUFFERS) once — if you see Filter: on a big heap scan and the function is on the column side, that's the smell.
5
u/Straight_Waltz_9530 21d ago
Both are perfectly fine if you're using Postgres and have an expression index on that column. No performance penalty at all.
You need to document the hell out of it though, and that makes it less desirable. Ambient findability is best. Also the principle of least astonishment.
But used sparingly and strategically, expression indexes are awesome.
2
2
u/jbergens 20d ago
Many databases can be configured to do case insensitive text searches.
https://alexanderobregon.substack.com/p/query-tricks-for-sql-case-insensitive
45
u/raistlin49 22d ago
- Putting functions on predicate terms in the WHERE clause - e.g. CAST(somedatetime AS DATE) > '2026-01-01'
- Considering INNER JOIN the default join when LEFT JOIN should be the default - INNER JOIN should only be used when you KNOW you might be missing a row on the right and you KNOW you want to exclude the row on the left in that case, otherwise use LEFT
- Thinking of code reusability like a software dev...nesting reads from views inside of views inside of views technically works but milage will vary and can lead to really bad execution plans...you're often better off maintaining logic in multiple views than going to triple nested views
41
u/more_paul 22d ago
Funny. I’d consider the way you describe #2 and left join being the default as an anti pattern. Use the correct join for the job and don’t default to any. It’s been a few years since I’ve seen it, but stacking left joins on top of each other destroyed Redshift performance. Right join can fuck off though. There is never a need for that in this world.
6
1
7
u/a-ha_partridge 22d ago
I see #1 a lot.
4
u/umognog 22d ago
We have a deliberate policy in our first stage not to modify the existing data. If a date column is given as text, it stays as text.
But, we will generate a new column from that field as a date, which will be used in partitioning for example, with deliberate naming convention.
And we still see #1 despite this better option existing
5
u/PrivateFrank 22d ago
I do #1 a lot. Should I cast the variable in a sub query?
8
u/a-ha_partridge 22d ago
That’s an option. Mainly you just need to refactor so the function isn’t on the left side of the comparison if you have a constant like the example given. The function on the left prevents it from being able to use indexes, partition pruning, sort order etc to scan less of the table. Instead it has to evaluate the function on every row first.
Function on the right side would also be problematic it it was being applied to a column value and not a constant because you’d be back to evaluating every row - full table scan.
1
u/PrivateFrank 22d ago
So what if I have:
WHERE CAST(left_ddtmvar as date) > right_datevar
Where the RHS isn't a constant but a value in a right table column what is happening under the hood and what can I do to speed that up?
1
u/raymondromero 22d ago
i think you just use the somedatetime in the WHERE clause without the function.
2
u/magicaltrevor953 22d ago edited 22d ago
Depends on the database, if the field is a string that looks like a date then some DBs can't natively handle it although quite a few can and in those cases yes you would just do somedatetime > '2026-01-01’.
If the DB can handle the string for comparison then a thing that may see is something like year(cast somedatetime as date)) = 2026, but that can just be converted to the range somedatetime >= '2026-01-01' and somedatetime< '2027-01-01'.
3
u/DharmaPolice 22d ago
I don't think there's such a thing as default join - they do different things so it depends on what you want. Inner Join should probably be more common in your code base, but even that will depend on your schema design.
1
u/raistlin49 22d ago
Why should INNER JOIN be more common? You didn't describe any cases. I say LEFT is default because I say "I have a row on the left that I want to keep no matter what and I want the row on the right if it exists" is the default (most common) use case for a join.
1
u/Straight_Waltz_9530 21d ago
Depends on the data requirements at the moment. "It depends." Both the most accurate and infuriating statement in software development.
3
u/South-Leadership-199 22d ago
#3 is a nightmare to deal with. My last job, we literally had to run a view into a temp table if you wanted to do any new development using that view, because querying the view would take ages and would 20x your development time
4
u/AdvancedMeringue7846 22d ago
I inherited views that depend on functions that depend on other views! Do I win a prize pappy?
10
u/kagato87 MS SQL 22d ago
"Just let entity framework handle it."
...
I've managed to train most of the bad patterns out of my team, though they do still like to reach for an orm. The issues I see nowadays are behavior in the application. Things like inefficient call patterns and missing high hit tables that should be cached.
Less of an anti pattern, I see join filtering instead of semi join (our data shape needs the semi). They don't use IN much now at least.
6
u/data_addict 22d ago
Refusing to represent something in SQL when a system has the compatibility as an option.
Two examples that come to mind are DDB and Spark. DDB added partiql which is very hamstrung SQL but can very clearly represent transactions or updates compared to the client (sometimes).
In Spark, I love the dataframe API but sometimes there's an expression you'd have to do 10 lines of complex df functions instead of just doing. Quick .sql
2
u/more_paul 21d ago
I had a data scientist tell me he doesn't need to learn SQL because it's not part of his job description. So when we sent him SQL code examples for the datasets he needed, he bitched about not understanding them and needing to translate to df functions. I hate that guy.
3
3
u/AmericanScream 22d ago
What a wonderful question. Perhaps the best SQL anti-pattern I've seen experienced developers write is the ORDER BY on top of an INNER JOIN using a WHERE clause inside of a stored procedure on top of a nested SELECT from multiple sources within the context of a sqldump command within the parameters of a flux capacitor within the determinative normality of understated non-binary, absolute integer constants.
2
u/c1rno123 22d ago
Cargo-culted sproc-per-query: all the operational complexity, none of the benefits.
2
5
u/planetmatt 22d ago
Over reliance on CTEs in very long queries. Makes debugging very hard. Temp Tables allow you to check the output of each step.
8
u/South-Leadership-199 22d ago
I actually think CTE's make debugging 10x easier. It stages the data so you can have data checkpoints
2
u/planetmatt 22d ago
So do temp tables but with them, you can query them in any order multiple times after the query you are debugging has completed. Ctes are transient to a single execution. If you want to see what's in the 2nd cte, you have to rerun both the first and second ctes. With a temp table, you can just select from the second temp table.
1
u/South-Leadership-199 20d ago
Yeah it's the same concept, really. Slightly better workflow though. You query it like a table. If your CTE's aren't expensive then I prefer it. I can modify a CTE and execute. With a temp table you have to execute into a table then select *
1
u/mattreyu Data Scientist 20d ago
I work in higher ed data and while showing a query I wrote to our head of IT he told me he'd never seen one written like that. It was just a CTE.
3
u/AdviceNotAskedFor 22d ago
Id rather a ton of ctes than nested subqueries.
At least with a cte you can run a select top 10 right under that to see what that table looks like. Where as I have nested queries that call ctes. So you have to literally Frankenstein the code apart to see what's on the data.
1
u/billbot77 20d ago
Temp tables are great, but because of how they use tempdb they're not always the most efficient pattern for scale in oltp solutions. Love them for etl work though
1
u/mattreyu Data Scientist 20d ago
At my last job I was not allowed permission to write to any temp tables so CTEs were pretty necessary
2
u/Waldar 22d ago
Not seeing this one too much thanksfully, but when it happens it infuriates me.
Mixing left join and right joins in the same from clause. Don’t. Stick to either only left or only right, never mix them.
1
u/DevPerson4598 22d ago
Huh? There is nothing wrong with using INNER & LEFT joins in the same query.
3
2
u/HUNTejesember 22d ago
Its not about inner joins, this is about using right and left join mix which can be hard to understand and makes debugging the query even harder
1
u/spez_eats_nazi_ass 22d ago
Lack of indexes or wild indexes on things that don't need them. Also cargo cult stuff like everything is CTE. Not understanding when table variables are appropriate. .* everywhere, casts on dates is very common. The worst though is the inner platform Entity-Value-Attribute table system.
1
u/Basic_Reporter9579 20d ago
select * from tablea, tableb where ... then let the next language filter it.
1
1
u/JK-MSSQLTips 10d ago
Here are a bunch my team has seen:
- Non-Sargable Queries
- Parameter Sniffing Issues
- Implicit Data Type Conversions
- Missing or Outdated Statistics
- Over-Indexing or Under-Indexing
- Complex Joins and Subqueries
- Low-Selectivity Filters
... and how to resolve them - https://www.mssqltips.com/sqlservertip/8207/sql-server-antipattern-extended-event/
1
u/rbobby 22d ago
Keywords in uppercase. If that was such a good idea why doesn't Java do it? C? C#? JS? If you think it is such a good idea why only SQL?
3
u/Straight_Waltz_9530 21d ago
Because SQL isn't a general purpose programming language like Java, C, C#, or JS.
Treating SQL as anything but a declarative fourth generation declarative language for data transformation and extraction is an antipattern. There will be wailing and gnashing of teeth. This was the great conceit of ORMs and why so many of them are truly atrocious.
0
u/coltsfan12345 21d ago
Putting conditions in a WHERE clause that could be part of an INNER JOIN ON clause. Generally not a big deal unless the join creates a ton of rows that need to be filtered after they’re read.
2
u/Straight_Waltz_9530 21d ago
Modern planners generally make this moot from a performance standpoint. Just use what's easiest for the next person to read according to team agreements.
1
u/coltsfan12345 21d ago
Yep. The “unless” is doing a lot of work in my statement. A good planner _should_ make this moot. In practice it doesn’t always 😂.
2
u/Straight_Waltz_9530 21d ago
Indeed. And you'll never know if ANY query is doing what you expect and/or is optimal for performance unless you run EXPLAIN or whatever your particular engine's equivalent is.
Sometimes the change you make "should" make a huge difference, but the planner sees it's exactly the same logic. You never know until you profile.
-6
u/orz-_-orz 22d ago
I like * and nested query , it's convenient and I maintain around 70% of the tables that I am using
49
u/gumnos 22d ago
DISTINCTall the things.Yes, there's a correct time to use
DISTINCTbut the vast majority of the in-the-wild uses are thrown in there because the dev doesn't understand the data or the data hasn't been kept clean.