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