You have users and orders in two tables. You want each name next to its
product. One query, or many?
THE N+1 PATTERN
SELECT * FROM users; -- 1 query
SELECT * FROM orders WHERE user_id = ?; -- once per user
Three users means four queries. A thousand users means a thousand and one.
Each one is a separate round trip to the database, and that cost scales with
your rows rather than your data.
THE JOIN
SELECT users.name, orders.product
FROM users
JOIN orders
ON users.id = orders.user_id;
The database matches `users.id` against `orders.user_id` and returns both
sides as one result set, in one statement, over one round trip.
name | product
John | Laptop
Sarah | Phone
Omar | Monitor
TWO THINGS THIS VIDEO IS NOT SAYING
1. Multiple queries aren't bad. The problem is N+1 — a query per row —
not plural queries in general. Two queries is a perfectly good fix:
fetch the users, then `SELECT * FROM orders WHERE user_id IN (1,2,3)`.
That's one round trip per table instead of one per row, and it's exactly
what ActiveRecord's `preload` does. Sometimes it beats a JOIN, because
a JOIN repeats the user columns on every matching order row.
2. A JOIN isn't automatically faster. Performance depends on your indexes,
data volume, how selective the query is, and what your planner decides to
do with it. A JOIN against an unindexed foreign key can be slower than
two well-targeted queries. Run `EXPLAIN ANALYZE` instead of guessing.
The rule worth keeping: never query inside a loop. Beyond that, measure.
—
#Astmation — #software #systems, #automation, and #AI-powered #applications .
Smart Technology. Smarter Solutions.
https://astmation.com/
#Shorts #SQL #SQLJoin #Database #NPlusOne #PostgreSQL #BackendDevelopment #QueryOptimization #SoftwareEngineering #LearnToCode
Watch on YouTube