What types of JOINs could you see in the execution plan? In what situations are they used?

In terms of executing SQL queries with JOIN, the following types can be encountered:

• Nested Loop Join — iterates over the rows of one table and searches for matches in the second for each. It is efficient with small datasets or when one table is very small.

• Hash Join — builds a hash table on one of the tables (usually the smaller one), then scans the other and searches for matches by hash. Works well with large volumes of data and uniform key distribution.

• Merge Join — requires both tables to be sorted by the join key. Then, a sequential merge of the sorted sets occurs. It is efficient when indexes are present and data is sorted.

The use of a specific JOIN type depends on data volume, index availability, statistics, and query conditions. For example, Merge Join is often chosen for large tables with indexes, while Hash Join is used for large, unsorted tables.