Use UNION instead of OR to speed up results
To contact the author click here
This article is from the book "Access 2007 Pure SQL"
To download the sample database click here
Use
UNION
instead of OR to speed up results
Create an inventory report
using
UNION
instead of OR operators
Discussion:
We can use the
UNION statement instead of multiple OR operators to speed up the
response time of the database engine.
This is a trick used in databases and the difference in response
time is enormous. The SQL
statement below with the UNION operation achieves the exact same results
as the SQL code in the previous example.
However, the response is instant using
Moreover, response times deteriorate as the number
of records or the number of OR operators increase.
Consequently, if you have statements with multiple OR operators
that you use often, you might consider replacing them with UNION
statements. The only
difference is the order in which the retrieved records appear since in
this case the results of the first SELECT statement in the UNION
operation will appear first and the results of the second SELECT will
follow. You can easily
reorder the recordset using the ORDER BY clause with a UNION statement.
For a full understanding of
Code:
SELECT productname, unitsinstock, unitsonorder
FROM products
WHERE (UnitsInStock > 10)
SELECT productname, unitsinstock, unitsonorder
FROM products
WHERE (UnitsOnOrder > 10)
Result:
