author | Dominic Pinkman <Dominic.Pinkman@Nokia.com> |
Wed, 31 Mar 2010 11:11:55 +0100 | |
changeset 7 | 51a74ef9ed63 |
permissions | -rw-r--r-- |
7
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
1 |
<?xml version="1.0" encoding="utf-8"?> |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
2 |
<!-- Copyright (c) 2007-2010 Nokia Corporation and/or its subsidiary(-ies) All rights reserved. --> |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
3 |
<!-- This component and the accompanying materials are made available under the terms of the License |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
4 |
"Eclipse Public License v1.0" which accompanies this distribution, |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
5 |
and is available at the URL "http://www.eclipse.org/legal/epl-v10.html". --> |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
6 |
<!-- Initial Contributors: |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
7 |
Nokia Corporation - initial contribution. |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
8 |
Contributors: |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
9 |
--> |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
10 |
<!DOCTYPE concept |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
11 |
PUBLIC "-//OASIS//DTD DITA Concept//EN" "concept.dtd"> |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
12 |
<concept id="GUID-AF5A75D7-0687-546C-87B2-0B7DF7D33217" xml:lang="en"><title>SQL WHERE Clause Tips</title><shortdesc>This guide provides tips for using the WHERE clause. </shortdesc><prolog><metadata><keywords/></metadata></prolog><conbody> |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
13 |
<section id="GUID-DF59190A-38ED-4ACA-B4F2-C4D08FBE50BB"><title>Introduction</title> <p>You should use indexed column names |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
14 |
rather than expressions in WHERE clauses. </p> <p><b>Intended |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
15 |
audience:</b> </p> <p>This document is intended to be used by Symbian platform |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
16 |
licensees and third party application developers. </p> </section> |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
17 |
<section id="GUID-54EE7DC6-1E73-5A90-9442-7056C355DB63"><title>Used Indexed |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
18 |
Column Names, not Expressions, in WHERE Clauses</title> <p>Suppose you have |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
19 |
a table with an indexed column you want to search against, like this: </p> <codeblock id="GUID-0BEAF3EE-EFEC-50E2-81AD-38AFC4BA3A79" xml:space="preserve"> |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
20 |
CREATE TABLE demo312( |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
21 |
id INTEGER PRIMARY KEY, |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
22 |
name TEXT |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
23 |
); |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
24 |
</codeblock> <p>When you use the demo312.id column in a query, it is important |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
25 |
that the column name appear by itself on one side of the comparison operation |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
26 |
and that it not be part of an expression. So the following query works very |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
27 |
efficiently: </p> <codeblock id="GUID-FA12E021-9464-5F19-B192-84CF46BE6028" xml:space="preserve"> |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
28 |
SELECT name FROM demo312 WHERE id=?; |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
29 |
</codeblock> <p>But the following variations, although logically equivalent, |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
30 |
result in a full table scan: </p> <codeblock id="GUID-D37A64D9-9480-572E-86DD-D7A6C36CF8F5" xml:space="preserve"> |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
31 |
SELECT name FROM demo312 WHERE id-?=0; |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
32 |
SELECT name FROM demo312 WHERE id*1=?; |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
33 |
SELECT name FROM demo312 WHERE +id=?; |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
34 |
</codeblock> <p>In other words, you want to make sure that the indexed column |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
35 |
name appears by itself on one side or the other of the comparison operator, |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
36 |
and not inside an expression of some kind. Even a degenerate expression such |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
37 |
as a single unary “+” operator will disable the optimizer and cause a full |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
38 |
table scan. </p> <p>Some variation in terms of the WHERE clause is permitted. |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
39 |
The column name can be enclosed in parentheses, it can be qualified with the |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
40 |
name of its table, and it can occur on either side of the comparison operator. |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
41 |
So all of the following forms are efficient, and will in fact generate identical |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
42 |
bytecode: </p> <codeblock id="GUID-F400BDF3-3A5D-53EB-A94D-D2A75CB156AE" xml:space="preserve"> |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
43 |
SELECT name FROM demo312 WHERE id=?; |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
44 |
SELECT name FROM demo312 WHERE demo312.id=?; |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
45 |
SELECT name FROM demo312 WHERE ?=id; |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
46 |
SELECT name FROM demo312 WHERE (id)=?; |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
47 |
SELECT name FROM demo312 WHERE (((demo321.id))=?); |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
48 |
</codeblock> <p>The previous examples have all shown SELECT statements. But |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
49 |
the same rules apply for the WHERE clause in DELETE and UPDATE statements: </p> <codeblock id="GUID-BFF2D644-12F1-5CF4-9658-CCA9A38A258E" xml:space="preserve"> |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
50 |
UPDATE demo312 SET name=? WHERE id=?; |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
51 |
DELETE FROM demo312 WHERE id=?; |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
52 |
</codeblock> </section> |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
53 |
<section id="GUID-DA77E2E3-2F9D-5F56-A699-8BFDFE6CD3ED"><title>Use Conjunctions |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
54 |
in WHERE Clause Expressions</title> <p>SQLite works best when the expression |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
55 |
in a WHERE clause is a list of terms connected by the conjunction (AND) operator. |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
56 |
Furthermore, each term should consist of a single comparison operator against |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
57 |
an indexed column. The same rule also applies to ON clauses in a join and |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
58 |
to a HAVING clause. </p> <p>SQLite is able to optimize queries such as this: </p> <codeblock id="GUID-0B7F11E0-E50E-5C3B-B0B6-7390BDF7F201" xml:space="preserve"> |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
59 |
SELECT * FROM demo313 WHERE a=5 AND b IN ('on','off') AND c>15.5; |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
60 |
</codeblock> <p>The WHERE clause in the example above consists of three terms |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
61 |
connected by the AND operator and each term contains a single comparison operator |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
62 |
with a column as one operand. The three terms are: </p> <codeblock id="GUID-A2B87FA7-DBFC-534F-B549-9AAB0AB6A48F" xml:space="preserve"> |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
63 |
a=5 |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
64 |
b IN ('on','off') |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
65 |
c>15.5 |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
66 |
</codeblock> <p>The SQLite query optimizer is able to break the WHERE clause |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
67 |
down and analyze each of the terms separately, and possibly use one or more |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
68 |
of those terms with indexes to generate bytecode that runs faster. But consider |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
69 |
the following similar query: </p> <codeblock id="GUID-E6734A32-D04F-5594-B435-4EA15B4B03F9" xml:space="preserve"> |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
70 |
SELECT * FROM demo313 WHERE (a=5 AND b IN ('on','off') AND c>15.5) OR d=0; |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
71 |
</codeblock> <p>In this case, the WHERE clause consist of two terms connected |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
72 |
by an OR. The query optimizer is not able to break this expression up for |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
73 |
analysis. As a result, this query will be implemented as a full table scan |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
74 |
in which the complete WHERE clause expression will be evaluated for each row. </p> <p>In |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
75 |
this case, refactoring the WHERE clause does not help much: </p> <codeblock id="GUID-1B5F89D5-F21C-5F53-B427-C2EC6ACEAF21" xml:space="preserve"> |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
76 |
SELECT * FROM demo313 WHERE (a=5 OR d=0) AND (b IN ('on','off') OR d==0) |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
77 |
AND (c>15.5 OR d=0) |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
78 |
</codeblock> <p>The WHERE clause is now a conjunctive expression but its terms |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
79 |
are not simple comparison operators against table columns. The query optimizer |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
80 |
will be able to break the WHERE expression into three smaller subexpressions |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
81 |
for analysis, but because each subexpression is a disjunction, no indexes |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
82 |
will be usable and a full table scan will result. </p> <p>If you know in advance |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
83 |
that all rows in the result set are unique (or if that is what you want anyway) |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
84 |
then the following query can be used for an efficient implementation: </p> <codeblock id="GUID-D1C700BD-F69C-5C67-8EF4-CC7BED64A2BB" xml:space="preserve"> |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
85 |
SELECT * FROM demo313 WHERE a=5 AND b IN ('on','off') AND c>15.5 |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
86 |
UNION |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
87 |
SELECT * FROM demo313 WHERE d=0 |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
88 |
</codeblock> <p>In this form, the two queries are evaluated separately and |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
89 |
their results are merged to get the final result. The WHERE clause on both |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
90 |
queries is a conjunction of simple comparison operators so both queries could |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
91 |
potentially be optimized to use indexes. </p> <p>If the result set could potentially |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
92 |
contain two or more identical rows, then you can run the above query efficiently |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
93 |
as follows: </p> <codeblock id="GUID-CDCBAE0D-BE30-575B-A7A4-E3E7E8F99000" xml:space="preserve"> |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
94 |
SELECT * FROM demo313 WHERE RowID IN ( |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
95 |
SELECT RowID FROM demo313 WHERE a=5 AND b IN('on','off') AND c>15.5 |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
96 |
UNION ALL |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
97 |
SELECT RowID FROM demo313 WHERE d=0 |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
98 |
) |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
99 |
</codeblock> <p>The subquery computes a set containing the RowID of every |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
100 |
row that should be in the result set. Then the outer query retrieves the desired |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
101 |
rows. When a WHERE clause contains OR terms at the top level, most enterprise-class |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
102 |
SQL database engines such as PostgreSQL or Oracle will automatically convert |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
103 |
the query to a form similar to the above. But in order to keep minimize the |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
104 |
complexity and size of SQLite, such advanced optimizations are omitted. In |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
105 |
the rare cases where such queries are required, than can be optimized manually |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
106 |
by the programmer by recasting the query statements as shown above. </p> </section> |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
107 |
</conbody><related-links> |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
108 |
<link href="GUID-22844C28-AB5B-5A6F-8863-7269464684B4.dita"><linktext>SQL Overview</linktext> |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
109 |
</link> |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
110 |
<link href="GUID-78773BCA-ADF6-53E6-AC80-5CB2AE1F8BCC.dita"><linktext>SQL Server |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
111 |
Guide</linktext></link> |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
112 |
<link href="GUID-1F12E3F5-45B2-55EC-B021-00338277C608.dita"><linktext>SQL DB Overview</linktext> |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
113 |
</link> |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
114 |
<link href="GUID-43CA02E7-0101-5824-B91B-E15EE20C829A.dita"><linktext>Avoid Transient |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
115 |
Tables</linktext></link> |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
116 |
<link href="GUID-49A3419F-D20A-5C5D-B2FF-51724EF37704.dita"><linktext>Prevent Datafile |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
117 |
Corruption</linktext></link> |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
118 |
<link href="GUID-C2FAEBB2-4A1A-5BB0-9670-4801525CBC6A.dita"><linktext>SQL Index |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
119 |
Tips</linktext></link> |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
120 |
<link href="GUID-B994E6F7-228A-5433-B87F-91857C5D93D6.dita"><linktext>SQL Insertion |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
121 |
Tips</linktext></link> |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
122 |
<link href="GUID-4FC23DB7-4758-5DA4-81FF-0DAB169E2757.dita"><linktext>SQL Schema |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
123 |
Tips</linktext></link> |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
124 |
<link href="GUID-2A2920E0-5D40-5358-BC0C-8572CEFE078C.dita"><linktext>SQL Expressions</linktext> |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
125 |
</link> |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
126 |
<link href="GUID-126FCCCC-0E7D-59AE-959A-2F94A7319C4B.dita"><linktext>SQL Statement |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
127 |
Tips</linktext></link> |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
128 |
<link href="GUID-ACCCB148-DAF9-59EC-B585-8EF632B9BF04.dita"><linktext>SQL Joins</linktext> |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
129 |
</link> |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
130 |
<link href="GUID-B7E978C1-45CA-554C-8028-D901B97BA2E0.dita"><linktext> ANALYZE |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
131 |
Command</linktext></link> |
51a74ef9ed63
Week 12 contribution of API Specs and fix SDK submission
Dominic Pinkman <Dominic.Pinkman@Nokia.com>
parents:
diff
changeset
|
132 |
</related-links></concept> |