Performance best practices for SQL queries | Avoid using sub-selects and redundant expressions


Reduce the result set as early as possible

Assume the following expressions in a Where clause:

  1. table1.col1 = table2.col3

    Join all rows of both tables

  2. table1.col5 > value1

    Return 60% of table1's rows

  3. table2.col3 = value2

    Return 30% of table's rows

  4. table1.col2 = value3

    Return 2% of table2's rows

Following the above order will be expensive, especially if table1 and table2 are large tables. A more efficient order is 4, 3, 2, 1.