Tell me, by what criterion, the query optimizer selects the connection of the tables?

I tried to join the table to itself on a clustered index and expected to get a merge join, but in fact I received nested loops.

If you believe WIKI, then a necessary condition is the sorting of the fields by which the connection is made.

  • I'm afraid your question is too complicated. Query optimizers are guided by internal algorithms and different versions of a specific DBMS can go different ways. In addition, in large DBMS optimizers collect statistics on data and indexes and can change the execution plan depending on the predicted number of result records (query selectivity). If you need a specific behavior, you should use the optimizer hints. And that doesn't always help - Mike
  • I also remembered the funny situation with the site (MS Sql is the heir of it). It dynamically changed the execution plan depending on the number of different simultaneous queries to the database and locks on the index pages - Mike

1 answer 1

In short, the criterion is one - select those types of connection that lead to the minimum cost of the request.