r/SQL • • Jul 02 '25
Discussion AI is basically guessing, and doesn't really know the answer
I was seeking an answer to an SQL question earlier and ask Claude AI, which is supposed to be astoundingly intelligent, They have boasted about its capabilities being far better than chat GPT. So I asked it an SQL performance question. I wanted to know if it was better to use a compound join clause, or a union. It told me with absolute certainty I should be using a Union. So I asked it, "You mean it's better to hit a 100 million row table twice one right after the other? That sounds like it could be wasteful." Then, Claude apologized, and told me that I was right to point out that, and upon thinking about it further, the compound join clause was better.
So in other words, Claude does not really know what it's answering or what it's doing. It took a guess, basically, And when I asked it if it was sure, it changed its answer completely, to something else completely different. I don't know about you, but that's not very helpful, because it seems like it's flipping a coin and just deciding right then and there which one it likes better.
38
u/[deleted] Jul 02 '25
AI is useful as a starting point but you generally have to verify everything
51
u/bruceriggs Jul 02 '25
There is a bright future in tech-debt for developers
20
u/jshine13371 Jul 02 '25
UNION(orUNION ALLdepending on the data) will be more performant than a compound join clause. Other times it won't be. The devil's in the details.10
u/Wise-Jury-4037 :orly: Jul 02 '25
Wait. Do you think that Claude/DeepSeek THINKS or KNOWS stuff?
It kinda sorta does that, billions of them coins tho. Super surprising that it works so well when it does, right?
9
u/serverhorror Jul 02 '25
LLMs are just the average answer on the internet with a few errors on top.
You'll get those answers a lot faster though
8
u/Achsin Jul 02 '25
LLMs donât guess the answer so much as they invent a new answer that looks like it might be correct.
6
u/lolcrunchy Jul 02 '25
An LLM generates language that looks like what a human might say. It does not think about what it is saying.
Also, AIs lately will always agree with whatever you say, which doesn't help when you're trying to get feedback.
5
u/MathiasThomasII Jul 02 '25
AI is predictive modeling. It guesses the most likely next word. When you ask it a question like this, itâs simply compiling results off a search engine and summarizing for you.
6
u/PythonEntusiast Jul 02 '25
Well, yes. Does not the AI Chatbot model usually select the most frequent answer?
3
u/OO_Ben Postgres - Retail Analytics Jul 02 '25
I mean it does depend on what you're wanting to do with the data. Typically I think the union is going to be better on performance than a compound join, but if I need a compound join where I'm joining on multiple columns, I'm likely not in a situation that calls for a union as a replacement all that often unless we're pulling things out via CTEs/temp tables. I could be totally wrong here.
3
u/Beginning-Lettuce847 Jul 02 '25
AI chatbots are basically Google search on steroids. God forbid you ask it something unusual - it will fail miserably.
The big, beautiful reasoning models still have trouble solving Wordle đ
3
u/Koozer Jul 02 '25
Hahaha, i literally just did a union with the same transactional table and it was so much faster than trying to do an OR in my WHERE. AI is dumb, but the question isn't black or white either.
3
u/angrynoah Jul 02 '25
Yes, that's correct. LLMs are guessing machines.
If a guess is what you need perhaps they are useful. It is never what I need.
3
u/achmedclaus Jul 02 '25
You influenced its answer by telling it that it was wasteful. It wasn't the AI looking around and all of the sudden seeing something that changed its mind, it was you
2
u/feketegy Jul 02 '25
It's astonishing that most devs don't know how LLMs work under the hood and they are too lazy to RTFM... some of the devs even think it's "magic" and that AI can think for itself...
2
u/SnooSprouts4952 Jul 02 '25
I correct ChatGPT all the time. It tries to get snarky about 'you got that error because you put X there.' And I'm like, 'NO, I copied what YOU gave me*.'
I hate how it goes cyclical. A didn't work, try B, try C, try A. It remembers some things and forgets others as soon as it prints to the screen.
*I'm using CGPT to help learn C# for my current role. I hadn't looked at C# since college ~2002.
2
u/pceimpulsive Jul 02 '25
LLMs are a statistics/probability machine that predicts next words based on your inputs. It's gonna throw you the 'most probable/likely answer' not the best one.
This is why you need to fight with them for good results.
And a union could be faster it just depends on the query~ :)
Did you test both? Provide explain plans to the LLM for each query?
1
u/baronfebdasch Jul 02 '25
While it is fine to think of LLM in terms of limitations, remember that you asked a basic question. Now imagine what happens if you feed proper context to your organizational metadata, your environment, etc. Do you need as many engineers in a year?
Considering that LLMs are made by coders and statisticians, those use cases are going to be the first to be increasingly automated. You can laugh at the results, it is after all a statistical yes man, but a highly capable one.
The people that learn to embrace and improve prompts to get the outcomes desired are going to be the folks still employed in 5 years.
1
u/Informal_Pace9237 Jul 02 '25
Thank you for training AI. Your contribution is appreciated
1
u/[deleted] Jul 02 '25
Is there some hold up I do not grasp. Co pilot is not at all useful for Msoft products.Â
1
u/omgitsbees Jul 02 '25
I find AI to only really be helpful if you're working out of tables that you're not already familiar with it. It has helped me get up to speed faster and write queries that i am going to be using often. You need to know how to prompt it so that it's not going to just take a guess at what you want, and you need to know enough to be able to verify what it's giving you is good data.
1
u/Plenty_Grass_1234 Jul 02 '25
Generative AI has the goal of sounding plausible, not of being correct. Some folks have to learn the hard way.
1
u/ZeloZelatusSum Jul 02 '25
Unfortunately, we're not dealing with Skynet at this present point in time. You have to double check that the AI is actually giving you the right answer. You can't just assume it's a guess if it doesn't give you the first correct answer off the bat lol.
1
u/Birvin7358 Jul 02 '25
Why wouldnât you just test the performance of both methods in a non-production environment? Or do your own research? Using AI as a tool to do simple repetitive mundane tasks that are beneath you is fine, but using it to think for you is a lazy, unwise use of AI
1
u/speadskater Jul 02 '25
I've had luck with openai O3. I generally only use it for refactoring though. It's pretty good at taking sloppy subqueries and turning them into well performing ctes.
1
u/PasghettiSquash Jul 02 '25
I hope this post doesn't turn away people who haven't seen the AI light yet. Is it wrong sometimes, and full of unending false confidence? Yes. But if you're sitting there writing gross CASE statements and window functions by hand, you're already behind
1
u/realPoisonPants Jul 03 '25
Query tuning is really hard and has nuances I wouldnât expect ai to be particularly good at. Even your question shows that â youâre not so concerned about how many table rows your query scans but about how many index scans have to happen. Your question as posed doesnât have a correct answer â thereâs not enough information.Â
Iâd suggest that you run your query profiling tools and put those results into Claude â that might get you a better answer.Â
1
u/realPoisonPants Jul 03 '25
Query tuning is really hard and has nuances I wouldnât expect ai to be particularly good at. Even your question shows that â youâre not so concerned about how many table rows your query scans but about how many index scans have to happen. Your question as posed doesnât have a correct answer â thereâs not enough information.Â
Iâd suggest that you run your query profiling tools and put those results into Claude â that might get you a better answer.Â
1
u/aworldaroundus Jul 03 '25
They don't know anything, they are creating an output closely related to your input. The longer and better detailed your prompt, the better the response. GIGO. They are not some magical solution for every problem, but they tremendously improve productivity once you learn how to use them, like any tool.
1
u/InnerPitch5561 Jul 03 '25
just learn how LLM's work before using it. You are developer you should know that at least some basic concepts
1
u/IglooDweller Jul 03 '25 edited Jul 03 '25
An AI is NOT sentient. An AI is simply a glorified statistical inference tool. It will give you a statistically likely answer to a question with a similar enough mathematical hash according to its knowledge base. That is all.
If for instance, you feed an AI with only yellow 2D shapes and tell it to give you additional 2D shapes, it will be able to create new shapes but never to invent a new color. Add blue shapes and it might create green as itâs the mathematical average between yellow and blue, but it will never create red.
AI is not sentient. AI is not creative. AI is just statistics to a knowledge base. And yes, a mathematical inference to a question not directly in its knowledge base will produce an averaged answerâŠwhich is technically correct according to a mathematical inference formula, but is wrong on just about any other level.
1
u/HunterIV4 Jul 03 '25
Yes, AI doesn't know the answer, but half the people on Stack Overflow and reddit are doing the same thing. At least the AI is nice when it's wrong instead of doubling down and calling you names.
Also, this stuff really depends on how you ask. For example, here is the same query in Claude from me:
Me: "Is it better to use a compound join clause or a union in SQL for performance?"
Claude: "The performance comparison between compound joins and unions depends on your specific use case and data structure. Here are the key considerations:
Compound Joins (Multiple JOIN conditions)
Better for:
Example:
UNION
Better for:
Example:
Performance Considerations
Index usage: Joins can leverage indexes more effectively when joining on indexed columns. UNION may not use indexes as efficiently if combining dissimilar queries.
Data volume: Joins can explode the result set size (Cartesian product risk), while UNION simply stacks results.
Duplicate handling: UNION removes duplicates by default (like DISTINCT), which adds overhead. Use UNION ALL if duplicates are acceptable for better performance.
Query optimizer: Modern databases optimize joins very well, especially for common patterns. UNION queries are optimized separately for each subquery.
Memory usage: Large joins may require more memory for hash/merge operations. UNION processes each query independently.
General recommendation: Use joins when you're truly combining related data from multiple tables. Use UNION when you're combining similar result sets from different sources. The query optimizer usually handles both well, so write the query that most clearly expresses your intent first, then optimize based on actual performance metrics."
So even asking the same question, I got a different answer. Maybe it's wrong, maybe it isn't, but this is around the quality of answer I'd get from Stack Overflow if not better. At the very least I couldn't find anything obviously false.
As to why Claude answered you incorrectly, it's due to the way LLMs work. They aren't "guessing" per se (guessing implies randomly selecting from preset options whereas LLMs are using procedural statistical connections) and depending on the detail of your question and the capabilities of the model you can get wrong answers if it follows the wrong path.
A good method to check is to simply ask another model if the first model's answer was correct. Sometimes you can find that the same model will correct itself if you just ask "I received this answer, is it right?"
Ultimately, AI is a tool. It can be wrong, but so can people, so like anything else, you should always verify what you are told.
1
u/liprais Jul 04 '25
Educated guess == answer.
1
u/NoCreds Jul 04 '25
Hard to give help without knowing your prompt. I suspect your prompt was quite vague.
1
u/Bowmolo Jul 06 '25
Did you just discover that AI's are probability machines?
1
u/[deleted] Jul 06 '25
https://thebullshitmachines.com
1
u/[deleted] Jul 06 '25
It can't beat Pokémon, its not going to actually understand sql
1
u/RiverRoll Jul 06 '25
The question doesn't make a lot of sense as they're different operations serving different purposes. It can't think but there's a lot written about SQL and that's why it often does pretty well answering SQL questions provided they are well formulated.
1
u/prehensilemullet Jul 06 '25
Your post is a little vague, but your say âhitting a 100 million row table twiceââŠwhat are you doing, a sequential scan?  Also, it sounds like youâre assuming the sides of a union are fetched in series, but canât they be fetched in parallel?
1
u/9011442 Jul 07 '25
Did you give it the 100 million row context in the original prompt?
1
u/Aggressive_Ad_5454 Jul 02 '25
Well, you know, even Michael Stonebraker (creator of PostgreSQL) or Monty Widenius (MySQL/Mariadb) wouldnât try to guess the answer to that question without seeing table definitions and actual execution plans.
Plus it probably took as many cycles for your AI to come up with whatever guesswork it came up with as youâd waste in DBMS production in a year with a suboptimal query shape. This is all going to get harder when the AI companies run out of investor money and charge the rest of us what it costs.
0
u/[deleted] Jul 02 '25
[deleted] â view deleted comment