Efficient SQL syntax

Use JOIN (in the FROM clause) to include a table in a query instead of a subquery in the WHERE clause. This tip applies even if you only need a table’s data for filtering, not for the result set.

Joining multiple tables in the FROM clause performs better than using a subquery in a WHERE clause.

Avoid SQL statements that can’t take advantage of indexes. These statements include the use of aggregate functions in a subquery, a UNION statement in a subquery, or an ORDER BY clause with a UNION statement.

An index can greatly increase the speed of processing a SELECT query. However, certain SQL syntax prevents the database from using indexes, forcing it to use the actual data for searching or sorting operations.

Consider avoiding the LIKE operator, especially with a leading wildcard character as in LIKE('%XXXX%').

Because the LIKE operation supports the use of wildcard searches, it performs slower than using exact-match comparisons. In particular, if you start the search string with a wildcard character, the database can’t use indexes at all in the search. Instead, the database must search the full text of each row of the table.

Consider avoiding the IN operator. If the possible values are known beforehand, the IN operation can be written using AND or OR for faster execution.

The second of the following two statements executes faster. It is faster because it uses simple equality expressions combined with OR instead of using the IN() or NOT IN() statements:

-- Slower 
SELECT lastName, firstName, salary 
FROM main.employees 
WHERE salary IN (2000, 2500) 
     
-- Faster 
SELECT lastName, firstName, salary 
FROM main.employees 
WHERE salary = 2000 
    OR salary = 2500
Consider alternative forms of a SQL statement to improve performance.

As demonstrated by previous examples, the way a SQL statement is written can also affect database performance. There are often multiple ways to write a SQL SELECT statement to retrieve a particular result set. In some cases, one approach runs notably faster than another one. In addition to the preceding suggestions, you can learn more about different SQL statements and their performance from dedicated resources on the SQL language.