SQL Join Flavors
antonz.org
antonz.org
SELECT user.user_id
FROM users
RIGHT JOIN purchases
ON purchases.user_id = user.user_id
AND user.user_id=123
1. https://docs.heimdallm.ai/en/main/attack-surface/sql.html#ou...
I am beginning to push back against the idea of LLMs authoring SQL. Determinism is really important for most realistic use cases. SQL is one of those "all or nothing" experiences. Pretty much the last thing you want to have a temperature/spiciness slider over.
Granted, if your use case is to simply memorize a handful of canonical queries and to provide a UX-friendly way for business administrators to retrieve these queries, then have at it.
But, if you are hoping to ask the LLM to write novel, sophisticated, domain-specific queries involving joins across 10+ tables, aggregations, recursion, windowing, etc., you are going to have a very bad time. In my experience, this is what everyone is dreaming about doing, and I think it is an unproductive fantasy at this point. Fine tuning will not give you what you seek, but don't let me stop you from trying. We burned over 200 hours on it with nothing to show for our time.
For me, LLMs are far more interesting for determining the user's intentions than emitting a final query. Classification seems like the actual superpower here. Classification can feed a very powerful deterministic query building engine that will actually give you what you want and you can prove it will work. It just takes a lot more tedious work than most humans are willing to endure, so here we are talking about various shortcuts.
Can you talk more about this? I'm working in this space now, but not from the "make an LLM give you the right query" perspective, but from the "make the LLM-produced query be safe" perspective[1]
1. https://docs.heimdallm.ai/en/main/blog/posts/safe-sql-execut...
> "make the LLM-produced query be safe"
I think these are the same problem.
Good luck with your project.
I could make the same argument about inner joins giving the risk of dropping records you intended to keep (very common in analytics). Just have to know what you’re doing and what data you got.
I'm going to look at an outer join FAR more critically, regardless of who wrote it, because it's easy to mess up the conditions.
The article really ought to filter everything through SQLite, for how pervasive it is.
select job_name, comp_name from jobs natural join companies;
This is a foot gun just waiting for someone to unsuspectingly add a new column to “jobs” with the same name as one already in “companies”, thereby silently breaking the query logic.
Sadly, the boss declined to rein in such shenanigans, and I left soon after.
select j.job_name, c.comp_name
from companies c
join jobs j on foreign key
If anyone out there knows why this isn't a thing, please chime in!
Natural joins however depend on already publicly exposed metadata (column names), and if that changed you’d break queries anyways.
But that’s just a sugar for still too low-level SQL. I’d better have well named relations and use these names in code. This way, there would be no situation when you add/remove a constraint but forget to add/remove a condition.
create table a (id, b_id);
create table b (id);
create relation atob from a to b on a.b_id = b.id;
select from a left join b using atob;
For the edge cases it could support taking the name of the foreign key as an optional parameter.
They think the foreign keys put some sort of link on a parent table to let it access rows of a child table as if they were an array.
There’s also a massive misunderstanding around ordering where a lot of people think that by reordering the joins you can control the order in which the db is going to execute the query.
A good planner is going to pick the best strategy however you order them, because it’s job is to give the results fastest no matter how you write the query (all things being equal).
Consider that there’s a unique index on one of the 2 columns. It’s almost certainly optimal to use that to find a single row and then execute your other filters no matter the order.
Maybe there’s a situation where you can adjust the filters so ones that remove the most rows with the least cpu cycles are first? I’m going to try to create an example to see if I can make it behave differently.
SELECT sale_dt, name, Sum(quantity) FROM sales LEFT JOIN product ON sales.product_id = product.id GROUP BY sale_dt, name;
A man can dream
Traditionally I’ve always done it as left join null but I saw a case a while back where the not exists version performed better (Postgres). I feel like I must have missed something because I thought they optimised to the same thing (maybe there were other criteria in the subquery that allowed it to use a better index).