3 ms·
I’ve done a lot of interviewing and one thing I’ve noticed is that many people are pretty confused about how joins work. They think the foreign keys put some s
by aidos 3y ago
I’ve done a lot of interviewing and one thing I’ve noticed is that many people are pretty confused about how joins work.
They think the foreign keys put some sort of link on a parent table to let it access rows of a child table as if they were an array.
There’s also a massive misunderstanding around ordering where a lot of people think that by reordering the joins you can control the order in which the db is going to execute the query.
- jletienne 3y agojust for clarity ordering your where clauses can matter right? I've tested it and it made a difference, i'm not hallucinating right
- deleted 3y ago[deleted]
- whoiscroberts 3y agoIndeed it can, at least with sqlserver.
- aidos 3y agoYou’re probably hallucinating! A good planner is going to pick the best strategy however you order them, because it’s job is to give the results fastest no matter how you write the query (all things being equal). Consider that there’s a unique index on one of the 2 columns. It’s almost certainly optimal to use that to find a single row and then execute your other filters no matter the order. Maybe there’s a situation where you can adjust the filters so ones that remove the most rows with the least cpu cycles are first? I’m going to try to create an example to see if I can make it behave differently.