UNION is also helpful for reducing round trips between the db client and db server.
I've run into lots of cases where SQL join is clear to write, but slow, but a simple query to get ids, plus a union of fetching data for each id is fast. Again, a union of single id fetches is often faster than a single query with in (x, y, z). We can wish the sql engine could figure it out, but my experience from a few years ago is that they usually don't.
I've run into lots of cases where SQL join is clear to write, but slow, but a simple query to get ids, plus a union of fetching data for each id is fast. Again, a union of single id fetches is often faster than a single query with in (x, y, z). We can wish the sql engine could figure it out, but my experience from a few years ago is that they usually don't.