Documentation Source Text

Check-in [07096801a3]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Improved documentation of the IN and NOT IN operators. Push back the 3.7.0 release date to 2010-07-22.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 07096801a3504ad3a0b870ac3b28c151f2094f97
User & Date: drh 2010-07-14 18:39:30.000
Context
2010-07-14
22:40
Fix a bug in the back-links of the evidence display generator. (check-in: 96334c601b user: drh tags: trunk)
18:39
Improved documentation of the IN and NOT IN operators. Push back the 3.7.0 release date to 2010-07-22. (check-in: 07096801a3 user: drh tags: trunk)
2010-07-13
23:51
Updates to the WAL documentation. (check-in: 8cdaccbb90 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to pages/changes.in.
38
39
40
41
42
43
44
45
46
47

48
49
50
51
52
53
54
      http://www.sqlite.org/src/timeline</a>.</p>
    }
    hd_close_aux
    hd_enable_main 1
  }
}

chng {2010 July 15 (3.7.0)} {
<li> Added support for [WAL | write-ahead logging].
<li> Query planner enhancements

}

chng {2010 March 30 (3.6.23.1)} {
<li> Fix a bug in the offsets() function of [FTS3]
<li> Fix a missing "sync" that when omitted could lead to database
     corruption if a power failure or OS crash occurred just as a
     ROLLBACK operation was finishing.







|

|
>







38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
      http://www.sqlite.org/src/timeline</a>.</p>
    }
    hd_close_aux
    hd_enable_main 1
  }
}

chng {2010 July 22 (3.7.0)} {
<li> Added support for [WAL | write-ahead logging].
<li> Query planner enhancements - automatic transient indices are created
     when doing so reduces the estimated query time.
}

chng {2010 March 30 (3.6.23.1)} {
<li> Fix a bug in the offsets() function of [FTS3]
<li> Fix a missing "sync" that when omitted could lead to database
     corruption if a power failure or OS crash occurred just as a
     ROLLBACK operation was finishing.
Changes to pages/lang.in.
1475
1476
1477
1478
1479
1480
1481


































































1482
1483
1484
1485
1486
1487
1488
1489
1490
1491
1492
1493
1494
1495
1496
1497
1498
1499
1500
1501
1502
1503
1504
1505
1506
1507
1508
1509
1510
1511
1512
1513
1514
1515
1516
1517
1518
1519
1520
1521
1522
1523
1524
1525
1526
1527
1528
1529
1530
1531
1532
1533
1534
1535
1536
1537
1538
1539
1540
1541
in the second.</p>

<p>^A NULL result is considered false when evaluating WHEN terms.
^If the base expression is NULL then the result of the CASE is the
result of the ELSE expression if it exists, or NULL if the ELSE clause
is omitted.</p>




































































<h3>Table Column Names</h3>
<p>^A column name can be any of the names defined in the [CREATE TABLE]
statement or one of the following special identifiers: "<b>ROWID</b>",
"<b>OID</b>", or "<b>_ROWID_</b>".
^These special identifiers all describe the
unique integer key (the [rowid]) associated with every 
row of every table.
^The special identifiers only refer to the row key if the [CREATE TABLE]
statement does not define a real column with the same name.
^The rowid can be used anywhere a regular
column can be used.</p>

<h3>Subqueries</h3>
<p>^[SELECT] statements can appear in expressions as either the
right-hand operand of the IN operator, as a scalar quantity, or
as the operand of an EXISTS operator.
^As a scalar quantity or the operand of an IN operator,
the SELECT should have only a single column in its
result.  ^Compound SELECTs (connected with keywords like UNION or
EXCEPT) are allowed.
^With the EXISTS operator, the columns in the result set of the [SELECT] are
ignored and the expression returns TRUE if one or more rows exist
and FALSE if the result set is empty.
^If no terms in the [SELECT] expression refer to values in the containing
query, then the expression is evaluated once prior to any other
processing and the result is reused as necessary.  ^If the [SELECT] expression
does contain variables from the outer query, then the [SELECT] is reevaluated
every time it is needed.</p>

<p>^When a SELECT is the right operand of the IN operator, the IN
operator returns TRUE if the SELECT result contains no NULLs and if
the left operand matches any of the values in the SELECT result.
^The IN operator may be preceded
by the NOT keyword to invert the sense of the test.</p>

<p>^When a SELECT appears within an expression but is not the right
operand of an IN operator, then the first row of the result of the
SELECT becomes the value used in the expression.  ^If the SELECT yields
more than one result row, all rows after the first are ignored.  ^If
the SELECT yields no rows, then the value of the SELECT is NULL.</p>

<tcl>hd_fragment castexpr {CAST expression}</tcl>
<h3>CAST expressions</h3>

<p>^A CAST expression changes the [datatype] of the <expr> into the
type specified by &lt;type&gt;. 
^(&lt;type&gt; can be any non-empty type name that is valid
for the type in a column definition of a [CREATE TABLE] statement.)^
^The &lt;type&gt; name is interpreted according to the 
[rules for determining column affinity].

<p>^An explicit cast is strong than affinity; with the CAST expression
the datatype conversion is forced even if it is lossy and irrreversible.
<ul>
<li><p>
  ^A cast of a REAL value into an INTEGER will truncate the
  fractional part of the REAL.  ^If an REAL is too large to be
  represented as an INTEGER then the result of the cast is
  the largest negative integer: -9223372036854775808.







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>















|

|
|

|









<
<
<
<
<
<

|














|







1475
1476
1477
1478
1479
1480
1481
1482
1483
1484
1485
1486
1487
1488
1489
1490
1491
1492
1493
1494
1495
1496
1497
1498
1499
1500
1501
1502
1503
1504
1505
1506
1507
1508
1509
1510
1511
1512
1513
1514
1515
1516
1517
1518
1519
1520
1521
1522
1523
1524
1525
1526
1527
1528
1529
1530
1531
1532
1533
1534
1535
1536
1537
1538
1539
1540
1541
1542
1543
1544
1545
1546
1547
1548
1549
1550
1551
1552
1553
1554
1555
1556
1557
1558
1559
1560
1561
1562
1563
1564
1565
1566
1567
1568
1569
1570
1571
1572
1573
1574
1575
1576
1577






1578
1579
1580
1581
1582
1583
1584
1585
1586
1587
1588
1589
1590
1591
1592
1593
1594
1595
1596
1597
1598
1599
1600
1601
in the second.</p>

<p>^A NULL result is considered false when evaluating WHEN terms.
^If the base expression is NULL then the result of the CASE is the
result of the ELSE expression if it exists, or NULL if the ELSE clause
is omitted.</p>

<tcl>hd_fragment in_op {IN operator} {NOT IN operator}</tcl>
<h3>The IN and NOT IN operators</h3>
<p>^The IN and NOT IN operators take a single scalar operand on the
left and a vector operand on the right
formed by an explicit list of zero or more scalars or by a 
single subquery.
^When the right operand of an IN or NOT IN operator is a subquery, the
subquery must have a single result column.
^When the right operand is an empty set, the result of IN is false and the
result of NOT IN is true, regardless of the left operand and even if the
left operand is NULL.
^(The result of an IN or NOT IN operator is determined by the following
matrix:

<center>
<table border=1>
<tr>
<th>Left operand <br>is NULL
<th>Right operand <br>contains NULL
<th>Right operand <br>is an empty set
<th>Left operand found <br>within right operand
<th>Result of <br>IN operator
<th>Result of <br>NOT IN operator
<tr>
<td align="center">no
<td align="center">no
<td align="center">no
<td align="center">no
<td align="center">false
<td align="center">true
<tr>
<td align="center">does not matter
<td align="center">no
<td align="center">yes
<td align="center">no
<td align="center">false
<td align="center">true
<tr>
<td align="center">no
<td align="center">does not matter
<td align="center">no
<td align="center">yes
<td align="center">true
<td align="center">false
<tr>
<td align="center">no
<td align="center">yes
<td align="center">no
<td align="center">no
<td align="center">NULL
<td align="center">NULL
<tr>
<td align="center">yes
<td align="center">does not matter
<td align="center">no
<td align="center">does not matter
<td align="center">NULL
<td align="center">NULL
</table>
</center>)^

<p>^Note that SQLite allows the parenthesized list of scalar values on
the right-hand side of an IN or NOT IN operator to be an empty list but
most other SQL database database engines and the SQL92 standard require
the list to contain at least one element.</p>


<h3>Table Column Names</h3>
<p>^A column name can be any of the names defined in the [CREATE TABLE]
statement or one of the following special identifiers: "<b>ROWID</b>",
"<b>OID</b>", or "<b>_ROWID_</b>".
^These special identifiers all describe the
unique integer key (the [rowid]) associated with every 
row of every table.
^The special identifiers only refer to the row key if the [CREATE TABLE]
statement does not define a real column with the same name.
^The rowid can be used anywhere a regular
column can be used.</p>

<h3>Subqueries</h3>
<p>^[SELECT] statements can appear in expressions as either the
right-hand operand of the IN or NOT IN operators, as a scalar quantity, or
as the operand of an EXISTS operator.
^As a scalar quantity or the operand of an IN or NOT IN operator,
the SELECT may have only a single column in its
result.  ^Compound SELECTs (connected with keywords like UNION or
EXCEPT) are allowed in any subquery.
^With the EXISTS operator, the columns in the result set of the [SELECT] are
ignored and the expression returns TRUE if one or more rows exist
and FALSE if the result set is empty.
^If no terms in the [SELECT] expression refer to values in the containing
query, then the expression is evaluated once prior to any other
processing and the result is reused as necessary.  ^If the [SELECT] expression
does contain variables from the outer query, then the [SELECT] is reevaluated
every time it is needed.</p>







<p>^When a SELECT appears within an expression but is not the right
operand of an IN or NOT IN operator, then the first row of the result of the
SELECT becomes the value used in the expression.  ^If the SELECT yields
more than one result row, all rows after the first are ignored.  ^If
the SELECT yields no rows, then the value of the SELECT is NULL.</p>

<tcl>hd_fragment castexpr {CAST expression}</tcl>
<h3>CAST expressions</h3>

<p>^A CAST expression changes the [datatype] of the <expr> into the
type specified by &lt;type&gt;. 
^(&lt;type&gt; can be any non-empty type name that is valid
for the type in a column definition of a [CREATE TABLE] statement.)^
^The &lt;type&gt; name is interpreted according to the 
[rules for determining column affinity].

<p>^An explicit cast is stronger than affinity; with the CAST expression
the datatype conversion is forced even if it is lossy and irrreversible.
<ul>
<li><p>
  ^A cast of a REAL value into an INTEGER will truncate the
  fractional part of the REAL.  ^If an REAL is too large to be
  represented as an INTEGER then the result of the cast is
  the largest negative integer: -9223372036854775808.