[MoronInAHurry]

What exactly makes that "one of the worst-performing SQL queries you've ever seen"?

It has two left joins on foreign/primary keys, and 4 trivially-indexed where conditions. That's all it has, and none of that is even slightly complex.

[akersten]

Maybe my SQL's a little rusty, but isn't a left (outer) join between voters+precincts and (that)+party going to create a massive table with a bunch of nulls inserted? (And, how do you know they're primary keys, and that any of it is indexed? That's not evident from the query)

Maybe "worst I've ever seen" is hyperbolic. My main point was, it's a sloppy query, could probably be done with a few WHERE clauses and an inner join instead, and is more evidence of shoddy coding practices.

[MoronInAHurry]

No, that's not how a left join works.

You really shouldn't act so condescending when you clearly don't even have a solid understanding of the basics yourself. That's a perfectly reasonable query that would run in less than a millisecond on a large database as long as it's been indexed correctly. There's nothing sloppy about it at all.

[maweki]

> No, that's not how a left join works.

It's how a naive implementation would work. Nobody actually knows "how" this left join works since we don't know what the query planner will do. Manifesting a whole outer join is not totally unreasonable in all cases.

I think you come off as very aggressive and there's no need for that tone.

But I do agree with the sentiment that the query might not be bad.

[ars]

Everybody who reads the query knows how it will work. You don't need to see the query plan.

The where clause at the end will filter to the desired rows. The left join will do absolutely nothing bad.

The purpose of the left join is to include voters who might not be assigned to a precinct.

It's a perfectly fine and performant query.

Outputting the query also doesn't really make any difference. Despite what the people on Twitter are saying showing the query does not make SQL injection easier or harder, it's irrelevant.

The big problem with the query is the lack of bound parameters. It's possible to do that safely in theory, but in practice it's not a good idea.

[maweki]

> Everybody who reads the query knows how it will work. You don't need to see the query plan.

No, we don't. Is there an index join between any of the tables? Is there an index on the name in some fashion and/or on the zip code. All we know is that this query can _probably_ not be answered by index only. What's the relative sizes of the tables (even that's not entirely clear)? Are any tables clustered by the filter criteria?

You wouldn't know and if you think you do, you should read up on possible access plans in modern database systems.

[ars]

There are only 1,681 voting precincts. And only a couple of parties.

So an index on the join is completely irrelevant.

There are also under a million voters. Even with no indexes at all this query will perform just fine.

Because of the where and the left join there's pretty much just one possible access plan.

Criticize the lack of bound parameters and other problems if you like, but the query itself is perfectly fine.

[maweki]

The query planner might rewrite it to exactly what you're proposing. Just from looking at it you usually can't identify a bad query.

If you have subqueries the story is a bit different but for this one, it's really anyone's guess.