5 ms·
> A classic job interview question is finding the employee with the highest salary in each department. Here’s a cheat code, in case you need to write a databas
by codeflo 3y ago
> A classic job interview question is finding the employee with the highest salary in each department.
Here’s a cheat code, in case you need to write a database query and don’t remember all the fancy join tricks: Problems like this often have a very straightforward solution with subselects. In this case, the main select gets the departments, and a subselect (with limit 1) fetches the top employee for each department.
That’s a very natural, compositional way of thinking. Granted, it’s not the most optimized way to do it, but more often then not, the resulting query plan is perfectly fine.
- andy800 3y agoSorry, how would you write this query just with LIMIT 1 and not window functions or MAX or a join?
- antifa 3y ago> Granted, it’s not the most optimized way to do it I've discovered a small number of queries actual do become faster when switching a column to a subselect.
- paol 3y ago> It’s not the most optimized way to do it, but more often then not, the query plan is perfectly fine. Depending on the details, the query plan might be exactly the same. I remember once (in SQL Server 2000, so long time ago) writing the same query in 3 completely different ways, only to see the DB generate the same plan for each one. It would surprise most people how sophisticated the query transformations databases can do.
- deleted 3y ago[deleted]
- dunham 3y agoI had one case, with the same SQL server, where rewriting to a join made the query much faster. That was a data changing statement though, and I think the semantics demanded it rerun the subselect. More recently, I've found that mysql sometimes behaves poorly with subselects, so I tend to avoid them out of habit.
- emptyfile 3y ago[dead]
- foldr 3y agoAlso easy to do this with a lateral join (and a limit 1 in the subquery).
- giraffe_lady 3y agoIf you can do a lateral join off the top of your head this isn't the kind of question you need to have a strategy for anyway.
- foldr 3y agoI think everyone tends to learn their own subset of SQL. I was unaware of some of the more 'obvious' solutions in the article (e.g. I had only a very vague idea of how window functions work), but I use lateral joins all the time. The nice thing about lateral joins is that they're very easy to understand conceptually once you get over the weird syntax.
- andrenotgiant 3y agoHow would you explain them to someone who knows the syntax but still finds them counterintuitive?
- foldr 3y agoI just think of it as performing a subquery for every row of the main query.
- dagss 3y agoTo me a lateral join is the simplest construct you could have, like a for loop in a programming language. For each row in my query so far, do this other thing. Since I learned them, everything went from being a puzzle on its own, "I know what I want to do but how to express it in SQL" -- to simply being writing things out naturally..
- magicalhippo 3y agoTIL lateral joins exist, and I struggled to understand what exactly they did because for some reasons all the blog posts and whatnot were so convoluted. Then I found this[1] SO answer, which lit my bulb. We're using such subqueries a lot, often for many columns in the same child table, and we're moving to MSSQL so will definitely change to using lateral join (or cross apply as MSSQL calls it). [1]: https://stackoverflow.com/a/28550962 https://stackoverflow.com/a/28550962
- paulddraper 3y ago> Granted, it’s not the most optimized way to do it, but more often then not, the resulting query plan is perfectly fine. It usually is the exactly same. (MySQL has a potato for a query optimizer tho.) The biggest case it is suboptimal is when you need to produce multiple fields from the sub-select. Because then you need multiple sub-selects.