Join Optimization

Join optimization is the process of optimizing the joining, or combining, of two or more tables in a database.

A join is the combination of contents from two or more tables. The tables are usually joined using data that the two tables have in common, and the result incorporates rows and columns from each table.

Joins are required to answer queries that require information from multiple tables.  There are a number of different types of joins, and a query optimizer typically determines which type of join to use based on the query and the types of tables the data are stored in.

A join path is series of joins illustrating how two tables are related.

A query optimizer is the internal process that parses and analyzes a query, then checks the procedure cache to see if it’s already stored. The optimizer is designed to formulate the most efficient method of executing a query and undertake the most efficient execution plan.  This process includes selecting the most efficient method for producing joins.  Most optimizers are well-designed and usually choose a suitable join method, but join selection can be optimized by understanding the different types of joins and utilizing some specific indexing practices.