What design works faster than “in” or “exists”

If in knowingly selects one row, then the time must be the same. In general, seeing the exists optimizer itself can guess that the subquery executed inside the exists must be performed with the FIRST_ROW optimization, i.e. to get the first record quickly and after receiving this record it will stop itself. In the case of in optimizer can go another way, to get all the lines faster. And this path may not be the same as for getting one line.

In addition, theoretically, in the case of in optimizer can start with a subquery and then try to select records from the main table by condition. In the case of exists optimizer can decide to go through the main table, and for each of its records check the subquery. But in practice, the optimizer will not forget to see which indices are available, to look at the statistics on tables and indices and will make a decision based on this information.

But which path will actually be faster to predict in the general case is impossible. There are many factors to consider. If you are interested in the speed of the request, then for each request you need to look at the execution plan and if the plan is not satisfied with trying different options.

A single recipe that some kind of design can not be faster or slower. It is also necessary to pay attention to the fact that different releases of the DBMS (even not large versions) can optimize one or another query in different ways.