SQL Join Flavors

by nalgeon • 3 years ago

94 37 3 years ago

Supply54115• 3 years ago

Having a deeper understanding of the different JOIN flavors is critical. For example, I don't think many people realize how deceptively dangerous OUTER joins are:

  SELECT user.user_id
  FROM users
  RIGHT JOIN purchases
    ON purchases.user_id = user.user_id
    AND user.user_id=123

By leaving the user_id=123 constraint in the JOIN instead of putting it in the WHERE, you've just exposed everyone's purchase data to the user. Easy to miss too if your tests don't fully create fixtures for multiple users. This becomes even more dangerous as we're entering the world of LLMs generating SQL[1]

1. https://docs.heimdallm.ai/en/main/attack-surface/sql.html#ou...

bob10293• 3 years ago

This becomes even more dangerous as we're entering the world of LLMs generating SQL[1]

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.

Supply54115• 3 years ago

We burned over 200 hours on it with nothing to show for our time.

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

totalhack• 3 years ago

How about an LLM talking to a semantic layer that then writes the SQL? I added experimental support for that in a project of mine, but using a semantic layer makes most querying so simple that natural language based approaches just seem kinda silly unless you have interface constraints (like on a mobile device).

https://github.com/totalhack/zillion

jumpman500• 3 years ago

I mean yes writing the wrong query will give you the wrong results. But there’s nothing inherently risky with a left/right/full join.

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.

Supply54115• 3 years ago

Outer joins are inherently risky because they make it very easy to put conditions in a place that looks correct (read: passes review) but isn't, and the results are disastrous (information leak).

chasil• 3 years ago

SQLite doesn't support right joins, which is a virtue in this case?

The article really ought to filter everything through SQLite, for how pervasive it is.

bewaretheirs• 3 years ago

Not any more. SQLite received support for RIGHT and FULL OUTER joins in 3.39.0, released over a year ago.

jletienne2• 3 years ago

why do people use right joins. i'll never understand it. especially in this case you when you can just query purchases directly and joining to users is pointless.

wodenokoto• 3 years ago

Why doesn’t that work?

wruza3• 3 years ago

It takes all purchases records and joins a corresponding user to each record, but only if it has a specific id. Otherwise it’s still every purchases record. But only one user gets joined. Join condition is not a filter, it is a join condition. Left join will always yield all left-table rows. Right join will always yield all right-table rows. “Where” works on a final result of select+joins (but before group by), so if you want to filter by user id, the condition should go in where.

paulddraper• 3 years ago

Maybe someday SQL will let us do ANTI JOIN

A man can dream

aidos3• 3 years ago

I always enjoy how the planner explicitly calls it an anti join but for those unfamiliar with the tricks the sql looks pretty weird on first glance.

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

hatthew• 3 years ago

This is a nice overview, but am I the only one who found the graphic more confusing than helpful? I can decipher its meaning by using my knowledge of joins, but it in no way improves my understanding of joins.

aidos3• 3 years ago

I’ve done a lot of interviewing and one thing I’ve noticed is that many people are pretty confused about how joins work.

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.

jletienne2• 3 years ago

just for clarity ordering your where clauses can matter right? I've tested it and it made a difference, i'm not hallucinating right

whoiscroberts• 3 years ago

Indeed it can, at least with sqlserver.

aidos3• 3 years ago

You’re probably hallucinating!

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.

x-shadowban• 3 years ago

Love me some lateral join / cross apply. Correlated subqueries and visibility in the where clause?! Whoo.

blendo• 3 years ago

I worked with a programmer who decided, one day, that all his joins were from now on going to be “natural joins”, and code like this started appearing:

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.

magicalhippo2• 3 years ago

I've been longing for our db to add support for something ala

    select j.job_name, c.comp_name 
    from companies c
    join jobs j on foreign key

Could take optional name in case there's more than one foreign key linking the two.

ccakes• 3 years ago

I do feel like this is an obvious thing for SQL to allow and databases to support. Given that it's not part of the standard (and NATURAL JOIN is) I can only assume that there's a compelling reason that I haven't thought about.

If anyone out there knows why this isn't a thing, please chime in!

jaakl• 3 years ago

Natural join is actually nice mental test of db semantics - if it does not just work, you have probably mixup of column names (same name meaning different things). If you have really clean and good patterns (I know you dont and you dont discover it before this major incident) it would work fine.

sandpaper26• 3 years ago

On the section about partitioned joins, how is that different from

SELECT sale_dt, name, Sum(quantity) FROM sales LEFT JOIN product ON sales.product_id = product.id GROUP BY sale_dt, name;

klysm• 3 years ago

I just want first class ASOF join. The lateral queries with limit 1 are so long to type out

hans_castorp• 3 years ago

LATERAL is part of the SQL standard (since 1999 if I'm not mistaken)

pwdisswordfishc• 3 years ago

I could get a job at https://hoogle.haskell.org/ ? Where do I apply?

SQL Join Flavors

Related Stories