~ubuntu-branches/ubuntu/wily/sqlite3/wily

onfocus="entersearch()" onblur="leavesearch()" style="width:24ex;padding:1px 1ex; border:solid white 1px; font-size:0.9em ; font-style:italic;color:#044a64;" value="Search SQLite Docs...">

112

113

</form>

114

</div>

115

</table>

116

</div></div></div></div>

117

</td></tr></table>

118

119

120

<h1 align="center">SQL As Understood By SQLite</h1><p><a href="lang.html">[Top]</a></p><h2>expression</h2><h4><a href="syntaxdiagrams.html#expr">expr:</a></h4><blockquote> <img alt="syntax diagram expr" src="images/syntax/expr.gif"></img> </blockquote>

121

<h4><a href="syntaxdiagrams.html#literal-value">literal-value:</a></h4><blockquote> <img alt="syntax diagram literal-value" src="images/syntax/literal-value.gif"></img> </blockquote>

122

<h4><a href="syntaxdiagrams.html#signed-number">signed-number:</a></h4><blockquote> <img alt="syntax diagram signed-number" src="images/syntax/signed-number.gif"></img> </blockquote>

123

<h4><a href="syntaxdiagrams.html#raise-function">raise-function:</a></h4><blockquote> <img alt="syntax diagram raise-function" src="images/syntax/raise-function.gif"></img> </blockquote>

124

125

126

<p>This section is different from the others. Most other sections of

127

this document talks about a particular SQL command. This section does

128

not talk about a standalone command but about "expressions" which are

129

subcomponents of most other commands.</p>

130

131

132

133

<h3>Operators</h3>

134

<p>SQLite understands the following binary operators, in order from

135

highest to lowest precedence:</p>

136

137

138

<font color="#2c2cf0"><big>||

139

* / %

140

+ -

141

<< >> & |

142

< <= > >=

143

= == != <> </big>IS IS NOT IN LIKE GLOB MATCH REGEXP

144

AND

145

OR</font>

146

</pre></blockquote>

147

148

<p>Supported unary prefix operators are these:</p>

149

150

151

152

</pre></blockquote>

153

154

155

156

<p>The COLLATE operator is a unary postfix

157

operator that assigns a <a href="datatype3.html#collation">collating sequence</a> to an expression.

158

The COLLATE operator has a higher precedence (binds more tightly) than any

159

prefix unary operator or any binary operator.

160

The collating sequence set by the COLLATE operator overrides the

161

collating sequence determined by the COLLATE clause in a table

162

<a href="lang_createtable.html#tablecoldef">column definition</a>.

163

See the <a href="datatype3.html#collation">detailed discussion on collating sequences</a>

164

in the <a href="datatype3.html">Datatype In SQLite3</a> document for additional information.

165

</p>

166

167

168

<p>The unary operator <font color="#2c2cf0"><big>+</big></font> is a no-op. It can be applied

169

to strings, numbers, blobs or NULL and it always returns a result

170

with the same value as the operand.</p>

171

172

<p>Note that there are two variations of the equals and not equals

173

operators. Equals can be either

174

175

<font color="#2c2cf0"><big>=</big></font> or <font color="#2c2cf0"><big>==</big></font>.

176

The non-equals operator can be either

177

<font color="#2c2cf0"><big>!=</big></font> or <font color="#2c2cf0"><big><></big></font>.

178

The <font color="#2c2cf0"><big>||</big></font> operator is "concatenate" - it joins together

179

the two strings of its operands.

180

The operator <font color="#2c2cf0"><big>%</big></font> outputs the value of its left

181

operand modulo its right operand.</p>

182

183

<p>The result of any binary operator is either a numeric value or

184

NULL, except for the <font color="#2c2cf0"><big>||</big></font> concatenation operator which always

185

evaluates to either NULL or a text value.</p><a name="isisnot"></a>

186

<p>The <font color="#2c2cf0"><big>IS</big></font> and <font color="#2c2cf0"><big>IS NOT</big></font> operators work

187

like <font color="#2c2cf0"><big>=</big></font> and <font color="#2c2cf0"><big>!=</big></font> except when one or both of the

188

operands are NULL. In this case, if both operands are NULL, then the

189

IS operator evaluates to 1 (true) and the IS NOT operator evaluates

190

to 0 (false). If one operand is NULL and the other is not, then the

191

IS operator evaluates to 0 (false) and the IS NOT operator is 1 (true).

192

It is not possible for an IS or IS NOT expression to evaluate to NULL.

193

Operators <font color="#2c2cf0"><big>IS</big></font> and <font color="#2c2cf0"><big>IS NOT</big></font> have the same

194

precedence as <font color="#2c2cf0"><big>=</big></font>.<a name="litvalue"></a>

195

196

<h3>Literal Values</h3>

197

<p>

198

A literal value is a constant of some kind.

199

Literal values may be integers, floating point numbers, strings,

200

BLOBs, or NULLs.</p>

201

202

<p>The syntax for integer and floating point literals (collectively

203

"numeric literals") is shown by the following diagram:</p>

204

205

<h4><a href="syntaxdiagrams.html#numeric-literal">numeric-literal:</a></h4><blockquote> <img alt="syntax diagram numeric-literal" src="images/syntax/numeric-literal.gif"></img> </blockquote>

206

207

208

<p>

209

If a numeric literal has a decimal point or an exponentiation

210

clause, then it is a floating point literal. Otherwise is it is an

211

integer literal. The "E" character that begins the exponentiation

212

clause of a floating point literal can be either upper or lower case.

213

The "." character is always used

214

as the decimal point even if the locale setting specifies "," for

215

this role - the use of "," for the decimal point would result in

216

syntactic ambiguity.</p>

217

218

<p> A string constant is formed by enclosing the

219

string in single quotes ('). A single quote within the string can

220

be encoded by putting two single quotes in a row - as in Pascal.

221

C-style escapes using the backslash character are not supported because

222

they are not standard SQL.

223

BLOB literals are string literals containing hexadecimal data and

224

preceded by a single "x" or "X" character. For example:</p>

225

226

227

X'53514C697465'

228

</pre></blockquote>

229

230

<p>

231

A literal value can also be the token "NULL".

232

</p>

233

234

235

236

<h3>Parameters</h3>

237

<p>

238

A "variable" or "parameter" token

239

specifies a placeholder in the expression for a

240

value that is filled in at runtime using the

241

<a href="c3ref/bind_blob.html">sqlite3_bind()</a> family of C/C++ interfaces.

242

Parameters can take several forms:

243

</p>

244

245

246

<table>

247

<tr>

248

249

<td>A question mark followed by a number <i>NNN</i> holds a spot for the

250

NNN-th parameter. NNN must be between 1 and <a href="limits.html#max_variable_number">SQLITE_MAX_VARIABLE_NUMBER</a>.

251

</td>

252

</tr>

253

<tr>

254

255

<td>A question mark that is not followed by a number creates a parameter

256

with a number one greater than the largest parameter number already assigned.

257

If this means the parameter number is greater than

258

<a href="limits.html#max_variable_number">SQLITE_MAX_VARIABLE_NUMBER</a>, it is an error.

259

</td>

260

</tr>

261

<tr>

262

263

<td>A colon followed by an identifier name holds a spot for a

264

<a href="c3ref/bind_parameter_name.html">named parameter</a> with the name :AAAA.

265

Named parameters are also numbered. The number assigned is one greater than

266

the largest parameter number already assigned. If this means the parameter

267

would be assigned a number greater than <a href="limits.html#max_variable_number">SQLITE_MAX_VARIABLE_NUMBER</a>, it is

268

an error. To avoid confusion, it is best to avoid mixing named and numbered

269

parameters.</td>

270

</tr>

271

<tr>

272

273

<td>An "at" sign works exactly like a colon, except that the name of

274

the parameter created is @AAAA.</td>

275

</tr>

276

<tr>

277

278

<td>A dollar-sign followed by an identifier name also holds a spot for a named

279

parameter with the name $AAAA. The identifier name in this case can include

280

one or more occurrences of "::" and a suffix enclosed in "(...)" containing

281

any text at all. This syntax is the form of a variable name in the

282

<a href="http://www.tcl.tk/">Tcl programming language</a>. The presence

283

of this syntax results from the fact that SQLite is really a

284

<a href="tclsqlite.html">Tcl extension</a> that has escaped into the wild.</td>

285

</tr>

286

</table>

287

</blockquote>

288

289

<p>Parameters that are not assigned values using

290

<a href="c3ref/bind_blob.html">sqlite3_bind()</a> are treated

291

as NULL.</p>

292

293

<p>The maximum parameter number is set at compile-time by

294

the <a href="limits.html#max_variable_number">SQLITE_MAX_VARIABLE_NUMBER</a> macro. An individual <a href="c3ref/sqlite3.html">database connections</a>

295

D can reduce its maximum parameter number below the compile-time maximum

296

using the <a href="c3ref/limit.html">sqlite3_limit</a>(D, <a href="c3ref/c_limit_attached.html#sqlitelimitvariablenumber">SQLITE_LIMIT_VARIABLE_NUMBER</a>,...) interface.

297

</p>

298

299

300

301

<h3>The LIKE and GLOB operators</h3>

302

<p>The LIKE operator does a pattern matching comparison. The operand

303

to the right of the LIKE operator contains the pattern and the left hand

304

operand contains the string to match against the pattern.

305

306

A percent symbol ("%") in the LIKE pattern matches any

307

sequence of zero or more characters in the string. An underscore

308

("_") in the LIKE pattern matches any single character in the

309

string. Any other character matches itself or its lower/upper case

310

equivalent (i.e. case-insensitive matching). (A bug: SQLite only

311

understands upper/lower case for ASCII characters by default. The

312

LIKE operator is case sensitive by default for unicode characters that are

313

beyond the ASCII range. For example,

314

the expression <b>'a' LIKE 'A'</b>

315

is TRUE but <b>'æ' LIKE 'Æ'</b> is FALSE.)<p>

316

317

<p>If the optional ESCAPE clause is present, then the expression

318

following the ESCAPE keyword must evaluate to a string consisting of

319

a single character. This character may be used in the LIKE pattern

320

to include literal percent or underscore characters. The escape

321

character followed by a percent symbol (%), underscore (_), or a second

322

instance of the escape character itself matches a

323

literal percent symbol, underscore, or a single escape character,

324

respectively.

325

326

<p>The infix LIKE operator is implemented by calling the

327

application-defined SQL functions <a href="lang_corefunc.html#like">like(<i>Y</i>,<i>X</i>)</a> or

328

329

330

<p>The LIKE operator can be made case sensitive using the

331

<a href="pragma.html#pragma_case_sensitive_like">case_sensitive_like pragma</a>.</p>

332

333

334

335

<p>The GLOB operator is similar to LIKE but uses the Unix

336

file globbing syntax for its wildcards. Also, GLOB is case

337

sensitive, unlike LIKE. Both GLOB and LIKE may be preceded by

338

the NOT keyword to invert the sense of the test. The infix GLOB

339

operator is implemented by calling the function

340

<a href="lang_corefunc.html#glob">glob(<i>Y</i>,<i>X</i>)</a> and can be modified by overriding

341

that function.</p>

342

343

344

345

<p>The REGEXP operator is a special syntax for the regexp()

346

user function. No regexp() user function is defined by default

347

and so use of the REGEXP operator will normally result in an

348

error message. If a <a href="c3ref/create_function.html">application-defined SQL function</a> named "regexp"

349

is added at run-time, that function will be called in order

350

to implement the REGEXP operator.</p>

351

352

353

354

<p>The MATCH operator is a special syntax for the match()

355

application-defined function. The default match() function implementation

356

raises an exception and is not really useful for anything.

357

But extensions can override the match() function with more

358

helpful logic.</p>

359

360

361

362

<h3>The BETWEEN operator</h3>

363

<p>The BETWEEN operator is logically equivalent to a pair of comparisons.

364

"<i>x</i> <b>BETWEEN</b> <i>y</i> <b>AND</b> <i>z</i>" is

365

equivalent to

366

"<i>x</i><b>>=</b><i>y</i> <b>AND</b> <i>x</i><b><=</b><i>z</i>" except

367

that with BETWEEN, the <i>x</i> expression is only evaluated once.

368

The precedence of the BETWEEN operator is the same as the precedence

369

as operators <b>==</b> and <b>!=</b> and <b>LIKE</b> and groups left to right.

370

371

372

373

<h3>The CASE expression</h3>

374

<p>A CASE expression serves a role similar to IF-THEN-ELSE in other

375

programming languages.

376

377

<p>The optional expression that occurs in between the CASE keyword and the

378

first WHEN keyword is called the "base" expression. There are two basic forms

379

of the CASE expression: those with a base expression and those without.

380

381

<p>In a CASE without a base expression, each WHEN expression is evaluated

382

and the result treated as a boolean, starting with the leftmost and continuing

383

to the right. The result of the CASE expression is the evaluation of the THEN

384

expression that corresponds to the first WHEN expression that evaluates to

385

true. Or, if none of the WHEN expressions evaluate to true, the result of

386

evaluating the ELSE expression, if any. If there is no ELSE expression and

387

none of the WHEN expressions are true, then the overall result is NULL.

388

389

<p>A NULL result is considered untrue when evaluating WHEN terms.

390

391

<p>In a CASE with a base expression, the base expression is evaluated just

392

once and the result is compared against the evaluation of each WHEN

393

expression from left to right. The result of the CASE expression is the

394

evaluation of the THEN expression that corresponds to the first WHEN

395

expression for which the comparison is true. Or, if none of the WHEN

396

expressions evaluate to a value equal to the base expression, the result

397

of evaluating the ELSE expression, if any. If there is no ELSE expression and

398

none of the WHEN expressions produce a result equal to the base expression,

399

the overall result is NULL.

400

401

<p>When comparing a base expression against a WHEN expression, the same

402

collating sequence, affinity, and NULL-handling rules apply as if the

403

base expression and WHEN expression are respectively the left- and

404

right-hand operands of an <big><b>=</b></big> operator.</p> If the base

405

expression is NULL then the result of the CASE is always the result

406

of evaluating the ELSE expression if it exists, or NULL if it does not.

407

408

<p>Both forms of the CASE expression use lazy, or short-circuit,

409

evaluation.

410

411

<p>The only difference between the following two CASE expressions is that

412

the <i>x</i> expression is evaluated exactly once in the first example but

413

might be evaluated multiple times in the second:

414

415

416

<li>CASE x WHEN w1 THEN r1 WHEN w2 THEN r2 ELSE r3 END

417

<li>CASE WHEN x=w1 THEN r1 WHEN x=w2 THEN r2 ELSE r3 END

418

</pre></ul>

419

420

421

422

423

<h3>The IN and NOT IN operators</h3>

424

<p>The IN and NOT IN operators take a single scalar operand on the

425

left and a vector operand on the right

426

formed by an explicit list of zero or more scalars or by a

427

single subquery.

428

When the right operand of an IN or NOT IN operator is a subquery, the

429

subquery must have a single result column.

430

When the right operand is an empty set, the result of IN is false and the

431

result of NOT IN is true, regardless of the left operand and even if the

432

left operand is NULL.

433

The result of an IN or NOT IN operator is determined by the following

434

matrix:

435

436

437

438

<tr>

439

<th>Left operand <br>is NULL

440

<th>Right operand <br>contains NULL

441

<th>Right operand <br>is an empty set

442

<th>Left operand found <br>within right operand

443

<th>Result of <br>IN operator

444

<th>Result of <br>NOT IN operator

445

<tr>

446

<td align="center">no

447

<td align="center">no

448

<td align="center">no

449

<td align="center">no

450

<td align="center">false

451

<td align="center">true

452

<tr>

453

<td align="center">does not matter

454

<td align="center">no

455

<td align="center">yes

456

<td align="center">no

457

<td align="center">false

458

<td align="center">true

459

<tr>

460

<td align="center">no

461

<td align="center">does not matter

462

<td align="center">no

463

<td align="center">yes

464

<td align="center">true

465

<td align="center">false

466

<tr>

467

<td align="center">no

468

<td align="center">yes

469

<td align="center">no

470

<td align="center">no

471

<td align="center">NULL

472

<td align="center">NULL

473

<tr>

474

<td align="center">yes

475

<td align="center">does not matter

476

<td align="center">no

477

<td align="center">does not matter

478

<td align="center">NULL

479

<td align="center">NULL

480

</table>

481

</center>

482

483

<p>Note that SQLite allows the parenthesized list of scalar values on

484

the right-hand side of an IN or NOT IN operator to be an empty list but

485

most other SQL database database engines and the SQL92 standard require

486

the list to contain at least one element.</p>

487

488

489

490

<h3>The EXISTS operator</h3>

491

492

<p>The EXISTS operator always evaluates to one of the integer values 0

493

and 1. If executing the SELECT statement specified as the right-hand

494

operand of the EXISTS operator would return one or more rows, then the

495

EXISTS operator evaluates to 1. If executing the SELECT would return

496

no rows at all, then the EXISTS operator evaluates to 0.

497

498

<p>The number of columns in each row returned by the SELECT statement

499

(if any) and the specific values returned have no effect on the results

500

of the EXISTS operator. In particular, rows containing NULL values are

501

not handled any differently from rows without NULL values.

502

503

<h3>Scalar Subqueries</h3>

504

505

<p>A <a href="lang_select.html">SELECT</a> statement enclosed in parentheses may appear as a scalar

506

quantity. A <a href="lang_select.html">SELECT</a> used as a scalar quantity must return a result set

507

with a single column. The result of the expression is the value of the

508

only column in the first row returned by the SELECT statement. If the SELECT

509

yields more than one result row, all rows after the first are ignored. If

510

the SELECT yields no rows, then the value of the expression is NULL.

511

The LIMIT of a scalar subquery is always 1.

512

Any other LIMIT value given in the SQL text is ignored.

513

514

<p>All types of <a href="lang_select.html">SELECT</a> statement, including aggregate and compound SELECT

515

queries (queries with keywords like UNION or EXCEPT) are allowed as scalar

516

subqueries.

517

518

<h3>Table Column Names</h3>

519

520

<p>A column name can be any of the names defined in the <a href="lang_createtable.html">CREATE TABLE</a>

521

statement or one of the following special identifiers: "<b>ROWID</b>",

522

"<b>OID</b>", or "<b>_ROWID_</b>".

523

These special identifiers all describe the

524

unique integer key (the <a href="lang_createtable.html#rowid">rowid</a>) associated with every

525

row of every table.

526

The special identifiers only refer to the row key if the <a href="lang_createtable.html">CREATE TABLE</a>

527

statement does not define a real column with the same name.

528

The rowid can be used anywhere a regular

529

column can be used.</p>

530

531

<p>A <a href="lang_select.html">SELECT</a> statement used as either a scalar subquery or as the

532

right-hand operand of an IN, NOT IN or EXISTS expression may contain

533

references to columns in the outer query. Such a subquery is known as

534

a correlated subquery. A correlated subquery is reevaluated each time

535

its result is required. An uncorrelated subquery is evaluated only once

536

and the result reused as necessary.

537

538

539

540

<h3>CAST expressions</h3>

541

542

<p>A CAST expression is used to convert the value of <expr> to

543

a different <a href="datatype3.html#storageclasses">storage class</a> in a similar way to the conversion that takes

544

place when a <a href="datatype3.html#affinity">column affinity</a> is applied to a value. Application of a CAST

545

expression is different to application of a column affinity, as

546

with a CAST expression the storage class conversion is forced even

547

if it is lossy and irrreversible.

548

549

<p>If the value of <expr> is NULL, then the result of the CAST

550

expression is also NULL. Otherwise, the storage class of the result value

551

is determined by applying the <a href="datatype3.html#affname">rules for determining column affinity</a> to

552

the <type-name> specified as part of the CAST expression.

553

554

555

<tr>

556

<th> Affinity of <type-name>

557

<th> Conversion Processing

558

<tr>

559

<td> NONE

560

<td> Casting a value to a <type-name> with no affinity causes the value to

561

be converted into a BLOB. Casting to a BLOB consists of first casting

562

the value to TEXT in the <a href="pragma.html#pragma_encoding">encoding</a> of the database connection, then

563

interpreting the resulting byte sequence as a BLOB instead of as TEXT.

564

565

<tr>

566

<td> TEXT

567

<td> To cast a BLOB value to TEXT, the sequence of bytes that make up the

568

BLOB is interpreted as text encoded using the database encoding.

569

<p>

570

Casting an INTEGER or REAL value into TEXT renders the value as if via

571

<a href="c3ref/mprintf.html">sqlite3_snprintf()</a> except that the resulting TEXT uses the <a href="pragma.html#pragma_encoding">encoding</a> of

572

the database connection.

573

574

<tr>

575

<td> REAL

576

<td> When casting a BLOB value to a REAL, the value is first converted to

577

TEXT.

578

<p>When casting a TEXT value to REAL, the longest possible prefix of

579

the value that can be interpreted as a real number is extracted from

580

the TEXT value and the remainder ignored. Any leading spaces in the

581

TEXT value are ignored when converging from TEXT to REAL. If there is

582

no prefix that can be interpreted as a real number, the result of the

583

conversion is 0.0.

584

585

<tr>

586

<td> INTEGER

587

<td> When casting a BLOB value to INTEGER, the value is first converted to

588

TEXT.

589

<p>When casting a TEXT value to INTEGER, the longest possible prefix of

590

the value that can be interpreted as an integer number is extracted from

591

the TEXT value and the remainder ignored. Any leading spaces in the

592

TEXT value when converting from TEXT to INTEGER are ignored. If there

593

is no prefix that can be interpreted as an integer number, the result

594

of the conversion is 0.

595

596

<p>A cast of a REAL value into an INTEGER will truncate the fractional

597

part of the REAL. If a REAL is too large to be represented as an

598

INTEGER then the result of the cast is the largest negative integer:

599

-9223372036854775808.

600

601

<tr>

602

<td> NUMERIC

603

<td> Casting a TEXT or BLOB value into NUMERIC first does a forced

604

conversion into REAL but then further converts the result into INTEGER if

605

and only if the conversion from REAL to INTEGER is lossless and reversible.

606

This is the only context in SQLite where the NUMERIC and INTEGER <a href="datatype3.html#affinity">affinities</a>

607

behave differently.

608

<p> Casting a REAL or INTEGER value to NUMERIC is a no-op, even if a real

609

value could be losslessly converted to an integer.

610

611

</tr>

612

613

</table>

614

615

<p>Note that the result from casting any non-BLOB value into a

616

BLOB and the result from casting any BLOB value into a non-BLOB value

617

may be different depending on whether the database <a href="pragma.html#pragma_encoding">encoding</a> is UTF-8,

618

UTF-16be, or UTF-16le.

619

620

621

622

623

<h3>Boolean Expressions</h3>

624

625

<p>The SQL language features several contexts where an expression is

626

evaluated and the result converted to a boolean (true or false) value. These

627

contexts are:

628

629

<ul>

630

<li> the WHERE clause of a SELECT, UPDATE or DELETE statement,

631

<li> the ON or USING clause of a join in a SELECT statement,

632

<li> the HAVING clause of a SELECT statement,

633

<li> the WHEN clause of an SQL trigger, and

634

<li> the WHEN clause or clauses of some CASE expressions.

635

</ul>

636

637

<p>To convert the results of an SQL expression to a boolean value, SQLite

638

first casts the result to a NUMERIC value in the same way as a

639

<a href="lang_expr.html#castexpr">CAST expression</a>. A NULL or zero value (integer value 0 or real value 0.0) is

640

considered to be false. All other values are considered true.

641

642

<p>For example, the values NULL, 0.0, 0, 'english' and '0' are all considered

643

to be false. Values 1, 1.0, 0.1, -0.1 and '1english' are considered to

644

be true.

645

646

<h3>Functions</h3>

647

<p>Both <a href="lang_corefunc.html">simple</a> and <a href="lang_aggfunc.html">aggregate</a> functions are supported.

648

(For presentation purposes, simple functions are further subdivided into

649

<a href="lang_corefunc.html">core functions</a> and <a href="lang_datefunc.html">date-time functions</a>.)

650

A simple function can be used in any expression. Simple functions return

651

a result immediately based on their inputs. Aggregate functions

652

may only be used in a SELECT statement. Aggregate functions compute

653

their result across all rows of the result set.</p>

654

655