4 ms·

Why is it so important to express this as one SQL query? Why not just run the two simple queries? It feels as though two different types of things are being joi

by danvk 6y ago

Why is it so important to express this as one SQL query? Why not just run the two simple queries? It feels as though two different types of things are being joined.

Daynil 6y ago
Depends on your goals. If you're a software engineer, it won't make a difference, just send two queries to the DB and combine the results before sending them off. If you're an analyst, you'll often get repeated ad-hoc requests. If you have to run 2 queries, then stitch them together, each time you are asked for a report, you're wasting tons of (really boring) time. Even if you ultimately create some code to run and stitch the results together for you, it's just faster and easier to write 1 query wherever possible. Just let the database do the work!
- Albert_Camus 6y ago
  > If you're a software engineer, it won't make a difference, just send two queries to the DB and combine the results before sending them off. This isn't true if you have certain use cases in your application, pagination being one of them. There's no simple way to implement pagination when you have two or more queries that return and unknown number of results and you're tasked with maintaining a consistent order across page changes. If you make a single query do all the work, it's easy to implement paging in any number of ways, the most performant being to filter by the last id the client saw. If your users aren't likely to paginate too far into the data, LIMIT and OFFSET will work fine as well.
drstewart 6y ago
You're falling into the trap of thinking like the developer looking at the implementation, not the end user. The goal is: >In order to audit inventory, the logistics team at the corporate headquarters requests a tool that can generate a report containing all meal_items that left a given store’s inventory on a particular day That's one thing. The end user doesn't see these as two separate requests just because that's how it's modeled in the database.
- robertlagrant 6y ago
  The end user here being the person writing the union SQL query?

by danvk 6y ago

Why is it so important to express this as one SQL query? Why not just run the two simple queries? It feels as though two different types of things are being joined.

Daynil 6y ago

Depends on your goals. If you're a software engineer, it won't make a difference, just send two queries to the DB and combine the results before sending them off. If you're an analyst, you'll often get repeated ad-hoc requests. If you have to run 2 queries, then stitch them together, each time you are asked for a report, you're wasting tons of (really boring) time. Even if you ultimately create some code to run and stitch the results together for you, it's just faster and easier to write 1 query wherever possible. Just let the database do the work!

Albert_Camus 6y ago
> If you're a software engineer, it won't make a difference, just send two queries to the DB and combine the results before sending them off. This isn't true if you have certain use cases in your application, pagination being one of them. There's no simple way to implement pagination when you have two or more queries that return and unknown number of results and you're tasked with maintaining a consistent order across page changes. If you make a single query do all the work, it's easy to implement paging in any number of ways, the most performant being to filter by the last id the client saw. If your users aren't likely to paginate too far into the data, LIMIT and OFFSET will work fine as well.

drstewart 6y ago

You're falling into the trap of thinking like the developer looking at the implementation, not the end user. The goal is: >In order to audit inventory, the logistics team at the corporate headquarters requests a tool that can generate a report containing all meal_items that left a given store’s inventory on a particular day That's one thing. The end user doesn't see these as two separate requests just because that's how it's modeled in the database.

robertlagrant 6y ago
The end user here being the person writing the union SQL query?