Eliminate unnecessary DISTINCT conditions

Before:

SELECT DISTINCT p.ID, p.Quantity
FROM Products p;

After:

SELECT p.ID, p.Quantity
FROM Products p;

The DISTINCT keyword in the first statement is unnecessary because the Products table contains the primary key p.ID, which is part of the result set.

Eliminate unnecessary DISTINCT conditions

Before:

SELECT DISTINCT *
FROM SalesOrders o JOIN Customers c
   ON o.CustomerID = c.ID
WHERE c.State = 'NY';

After:

SELECT  *
FROM SalesOrders o JOIN Customers c
   ON o.CustomerID = c.ID
WHERE c.State = 'NY';

The first query contains the primary keys of both tables, so each row in the result must be distinct.

Un-nest subqueries

Before:

SELECT s.*
FROM SalesOrderItems s
WHERE EXISTS ( SELECT *
 FROM Products p
 WHERE s.ProductID = p.ID
  AND p.ID = 300 
  AND p.Quantity > 20);

After:

SELECT s.*
FROM Products p JOIN SalesOrderItems s
   ON p.ID = s.ProductID
WHERE p.ID = 300 AND p.Quantity > 20;

Rewriting nested queries as joins often leads to more efficient execution and more effective optimization. In general, subquery un-nesting is always done for correlated subqueries with, at most, one table in the FROM clause, which are used in ANY, ALL, and EXISTS predicates. A uncorrelated subquery, or a subquery with more than one table in the FROM clause, is flattened if it can be decided, based on the query semantics, that the subquery returns at most one row.

In this example, the subquery can match at most one row for each row in the outer block. Because it can match at most one row, it can be converted to an inner join.

Un-nest subqueries

Before:

SELECT p.*
FROM Products p
WHERE EXISTS
   ( SELECT *
     FROM SalesOrderItems s
     WHERE s.ProductID = p.ID
       AND s.ID = 2001);

After:

SELECT DISTINCT p.*
FROM Products p JOIN SalesOrderItems s
   ON p.ID = s.ProductID
WHERE s.ID = 2001;

The Before query contains a conjunctive EXISTS predicate in the subquery, which can match more than one row. It can be converted to an inner join, with a DISTINCT in the SELECT list.

Un-nest subqueries

Before:

SELECT *
FROM Products p
WHERE p.ID =
    ( SELECT s.ProductID
      FROM SalesOrderItems s
      WHERE s.ID = 2001
         AND s.LineID = 1 );

After:

SELECT p.*
FROM Products p, SalesOrderItems s
WHERE p.ID = s.ProductID
   AND s.ID = 2001
   AND s.LineID = 1;

Eliminate subqueries in comparisons when the subquery matches at most one row for each row in the outer block.

Consider using an IN predicate when querying an indexed column

Before:

SELECT *
FROM SalesOrders
WHERE SalesRepresentative = 902 
   OR SalesRepresentative = 195;

After:

SELECT *
FROM SalesOrders
WHERE SalesRepresentative IN ( 195, 902 );

In the rewritten form, the IN-list predicate can be treated as a sargable predicate and exploited for indexed retrieval. Also, the optimizer can sort the IN-list to match the sort sequence of the index, leading to more efficient retrieval.

Note that the IN-list must contain only constants, or values that are constant during one execution of the query block, such as outer references.

Eliminate unnecessary joins

Before:

SELECT s.ID, s.LineID, p.ID
FROM SalesOrderItems s KEY JOIN Products p
FOR READ ONLY;

After:

SELECT s.ID, s.LineID, s.ProductID
FROM SalesOrderItems s
WHERE s.ProductID IS NOT NULL
FOR READ ONLY;

Consider eliminating joins when:

In this case, the join is a primary key to foreign key join so the primary key table, Products, can be eliminated. That is, the second query is semantically equivalent to the first because any row from the SalesOrderItems table that has a NULL foreign key to Products does not appear in the result.

Eliminate unnecessary joins

Before:

SELECT s.ID, s.LineID
FROM SalesOrderItems s 
 LEFT OUTER JOIN Products p 
   ON p.ID = s.ProductID
WHERE s.Quantity > 5 
FOR READ ONLY;

After:

SELECT s.ID, s.LineID
FROM SalesOrderItems s  
WHERE s.Quantity > 5 
FOR READ ONLY;

In the first query, the OUTER JOIN can be eliminated because the null-supplying table expression cannot produce more than one row for any row of the preserved side and none of the columns from Products is used above the LEFT OUTER JOIN.

Eliminate unnecessary case translation

Before:

SELECT *
FROM Customers
WHERE UPPER(Surname) = 'SMITH';

After:

SELECT *
FROM Customers
WHERE Surname = 'SMITH';

On a case insensitive database, the first query can be rewritten so that the optimizer can consider using an index on Customers.Surname.

By default, the database server performs case-insensitive string comparisons unless explicit text conversion instructions are given(use of UPPER, UCASE, LOWER, LCASE). Eliminating unnecessary case translations allows the predicates to be turned into sargable predicates, which can be used for index retrieval of the corresponding table.

Consider inlining functions

Before:

CREATE FUNCTION F1( arg1 INT, arg2 INT )
RETURNS INT
BEGIN
 RETURN arg1 * arg2
END;
SELECT F1( e.EmployeeID, 2.5 ) 
FROM Employees e;

After:

SELECT CAST( e.EmployeeID AS INT ) * CAST( 2.5 AS INT ) 
FROM Employees e;

You can inline user-defined functions if they take one of the following forms:

This tip is not applicable to temporary functions, recursive functions, or functions with a NOT DETERMINISTIC clause.

This tip is also not applicable if the function is called with a subquery as an argument, or when it is called from inside a temporary procedure.

Before:

CREATE PROCEDURE Test1( arg1 INT )
 BEGIN
  SELECT * FROM Employees WHERE EmployeeID=arg1
 END;
SELECT * FROM Test1( 200 );

After:

SELECT * FROM ( 
      SELECT * FROM Employees 
      WHERE EmployeeID=CAST( 200 AS INT ) ) 
   AS Test1;

You can inline a stored procedure that is defined only as a single SELECT statement when calling it in the FROM clause of a query. When a procedure is inlined, it is rewritten as a derived table. This tip does not apply to procedures that use default arguments, that contain anything other than a single SELECT statement in the body.