You are previewing Using SQLite.

Using SQLite

Cover of Using SQLite by Jay A. Kreibich Published by O'Reilly Media, Inc.
  1. Using SQLite
  2. Dedication
  3. A Note Regarding Supplemental Files
  4. Preface
    1. SQLite Versions
    2. Email Lists
    3. Example Code Download
    4. How We Got Here
    5. Conventions Used in This Book
    6. Using Code Examples
    7. Safari® Books Online
    8. How to Contact Us
  5. 1. What Is SQLite?
    1. Self-Contained, No Server Required
    2. Single File Database
    3. Zero Configuration
    4. Embedded Device Support
    5. Unique Features
    6. Compatible License
    7. Highly Reliable
  6. 2. Uses of SQLite
    1. Database Junior
    2. Application Files
    3. Application Cache
    4. Archives and Data Stores
    5. Client/Server Stand-in
    6. Teaching Tool
    7. Generic SQL Engine
    8. Not the Best Choice
    9. Big Name Users
  7. 3. Building and Installing SQLite
    1. SQLite Products
    2. Precompiled Distributions
    3. Documentation Distribution
    4. Source Distributions
      1. The Amalgamation
      2. Source Files
      3. Source Downloads
    5. Building
      1. Configure
      2. Manually
      3. Build Customization
    6. Build and Installation Options
    7. An sqlite3 Primer
    8. Summary
  8. 4. The SQL Language
    1. Learning SQL
    2. Brief Background
      1. Declarative
      2. Portability
    3. General Syntax
      1. Basic Syntax
      2. Three-Valued Logic
      3. Simple Operators
    4. SQL Data Languages
    5. Data Definition Language
      1. Tables
      2. Views
      3. Indexes
    6. Data Manipulation Language
      1. Row Modification Commands
      2. The Query Command
    7. Transaction Control Language
      1. ACID Transactions
      2. SQL Transactions
      3. Save-Points
    8. System Catalogs
    9. Wrap-up
  9. 5. The SELECT Command
    1. SQL Tables
    2. The SELECT Pipeline
      1. FROM Clause
      2. WHERE Clause
      3. GROUP BY Clause
      4. SELECT Header
      5. HAVING Clause
      6. DISTINCT Keyword
      7. ORDER BY Clause
      8. LIMIT and OFFSET Clauses
    3. Advanced Techniques
      1. Subqueries
      2. Compound SELECT Statements
      3. Alternate JOIN Notation
    4. SELECT Examples
      1. Simple SELECTs
      2. Simple JOINs
      3. JOIN...ON
      4. JOIN...USING, NATURAL JOIN
      5. OUTER JOIN
      6. Compound JOIN
      7. Self JOIN
      8. WHERE Examples
      9. GROUP BY Examples
      10. ORDER BY Examples
    5. What’s Next
  10. 6. Database Design
    1. Tables and Keys
      1. Keys Define the Table
      2. Foreign Keys
      3. Foreign Key Constraints
      4. Generic ID Keys
      5. Keep It Specific
    2. Common Structures and Relationships
      1. One-to-One Relationships
      2. One-to-Many Relationships
      3. Many-to-Many Relationships
      4. Hierarchies and Trees
    3. Normal Form
      1. Normalization
      2. Denormalization
      3. The First Normal Form
      4. The Second Normal Form
      5. The Third Normal Form
      6. Higher Normal Forms
    4. Indexes
      1. How They Work
      2. Must Be Diverse
      3. INTEGER PRIMARY KEYs
      4. Order Matters
      5. One at a Time
      6. Index Summary
    5. Transferring Design Experience
      1. Tables Are Types
      2. Keys Are Backwards Pointers
      3. Do One Thing
    6. Closing
  11. 7. C Programming Interface
    1. API Overview
      1. Structure
      2. Strings and Unicode
      3. Error Codes
      4. Structures and Allocations
      5. More Info
    2. Library Initialization
    3. Database Connections
      1. Opening
      2. Special Cases
      3. Closing
      4. Example
    4. Prepared Statements
      1. Statement Life Cycle
      2. Prepare
      3. Step
      4. Result Columns
      5. Reset and Finalize
      6. Statement Transitions
      7. Examples
    5. Bound Parameters
      1. Parameter Tokens
      2. Binding Values
      3. Security and Performance
      4. Example
      5. Potential Pitfalls
    6. Convenience Functions
    7. Result Codes and Error Codes
      1. Standard Codes
      2. Extended Codes
      3. Error Functions
      4. Prepare v2
      5. Transactions and Errors
      6. Database Locking
    8. Utility Functions
      1. Version Management
      2. Memory Management
    9. Summary
  12. 8. Additional Features and APIs
    1. Date and Time Features
      1. Application Requirements
      2. Representations
      3. Time and Date Functions
    2. ICU Internationalization Extension
    3. Full-Text Search Module
      1. Creating and Populating FTS Tables
      2. Searching FTS Tables
      3. More Details
    4. R*Trees and Spatial Indexing Module
    5. Scripting Languages and Other Interfaces
      1. Perl
      2. PHP
      3. Python
      4. Java
      5. Tcl
      6. ODBC
      7. .NET
      8. C++
      9. Other Languages
    6. Mobile and Embedded Development
      1. Memory
      2. Storage
      3. Other Resources
      4. iPhone Support
      5. Other Environments
    7. Additional Extensions
  13. 9. SQL Functions and Extensions
    1. Scalar Functions
      1. Registering Functions
      2. Extracting Parameters
      3. Returning Results and Errors
      4. Example
    2. Aggregate Functions
      1. Defining Aggregates
      2. Aggregate Context
      3. Example
    3. Collation Functions
      1. Registering a Collation
      2. Collation Example
    4. SQLite Extensions
      1. Extension Architecture
      2. Extension Design
      3. Example Extension: sql_trig
      4. Building and Integrating Static Extensions
      5. Using Loadable Extensions
      6. Building Loadable Extensions
      7. Loadable Extension Security
      8. Loading Loadable Extensions
      9. Multiple Entry Points
      10. Chapter Summary
  14. 10. Virtual Tables and Modules
    1. Introduction to Modules
      1. Internal Modules
      2. External Modules
      3. Example Modules
      4. SQL for Anything
    2. Module API
    3. Simple Example: dblist Module
      1. Create and Connect
      2. Disconnect and Destroy
      3. Query Optimization
      4. Custom Functions
      5. Table Rename
      6. Opening and Closing Table Cursors
      7. Filtering Rows
      8. Extracting and Returning Data
      9. Virtual Table Modifications
      10. Cursor Sequence
      11. Transaction Control
      12. Register the Module
      13. Example Usage
    4. Advanced Example: weblog Module
      1. Create and Connect
      2. Disconnect and Destroy
      3. Other Table Functions
      4. Open and Close
      5. Filter
      6. Rows and Columns
      7. Register the Module
      8. Example Usage
    5. Best Index and Filter
      1. Purpose and Need
      2. xBestIndex()
      3. xFilter()
      4. Typical Usage
    6. Wrap-Up
  15. A. SQLite Build Options
    1. Shell Directives
      1. ENABLE_READLINE
    2. Default Values
      1. SQLITE_DEFAULT_AUTOVACUUM
      2. SQLITE_DEFAULT_CACHE_SIZE
      3. SQLITE_DEFAULT_FILE_FORMAT
      4. SQLITE_DEFAULT_JOURNAL_SIZE_LIMIT
      5. SQLITE_DEFAULT_MEMSTATUS
      6. SQLITE_DEFAULT_PAGE_SIZE
      7. SQLITE_DEFAULT_TEMP_CACHE_SIZE
      8. YYSTACKDEPTH
    3. Sizes and Limits
      1. SQLITE_MAX_ATTACHED
      2. SQLITE_MAX_COLUMN
      3. SQLITE_MAX_COMPOUND_SELECT
      4. SQLITE_MAX_DEFAULT_PAGE_SIZE
      5. SQLITE_MAX_EXPR_DEPTH
      6. SQLITE_MAX_FUNCTION_ARG
      7. SQLITE_MAX_LENGTH
      8. SQLITE_MAX_LIKE_PATTERN_LENGTH
      9. SQLITE_MAX_PAGE_COUNT
      10. SQLITE_MAX_PAGE_SIZE
      11. SQLITE_MAX_SQL_LENGTH
      12. SQLITE_MAX_TRIGGER_DEPTH
      13. SQLITE_MAX_VARIABLE_NUMBER
    4. Operation and Behavior
      1. SQLITE_CASE_SENSITIVE_LIKE
      2. SQLITE_HAVE_ISNAN
      3. SQLITE_OS_OTHER
      4. SQLITE_SECURE_DELETE
      5. SQLITE_THREADSAFE
      6. SQLITE_TEMP_STORE
    5. Debug Settings
      1. SQLITE_DEBUG
      2. SQLITE_MEMDEBUG
    6. Enable Extensions
      1. SQLITE_ENABLE_ATOMIC_WRITE
      2. SQLITE_ENABLE_COLUMN_METADATA
      3. SQLITE_ENABLE_FTS3
      4. SQLITE_ENABLE_FTS3_PARENTHESIS
      5. SQLITE_ENABLE_ICU
      6. SQLITE_ENABLE_IOTRACE
      7. SQLITE_ENABLE_LOCKING_STYLE
      8. SQLITE_ENABLE_MEMORY_MANAGEMENT
      9. SQLITE_ENABLE_MEMSYS3
      10. SQLITE_ENABLE_MEMSYS5
      11. SQLITE_ENABLE_RTREE
      12. SQLITE_ENABLE_STAT2
      13. SQLITE_ENABLE_UPDATE_DELETE_LIMIT
      14. SQLITE_ENABLE_UNLOCK_NOTIFY
      15. YYTRACKMAXSTACKDEPTH
    7. Limit Features
      1. SQLITE_DISABLE_LFS
      2. SQLITE_DISABLE_DIRSYNC
      3. SQLITE_ZERO_MALLOC
    8. Omit Core Features
  16. B. sqlite3 Command Reference
    1. Command-Line Options
    2. Interactive Dot-Commands
      1. .backup
      2. .bail
      3. .databases
      4. .dump
      5. .echo
      6. .exit
      7. .explain
      8. .headers
      9. .help
      10. .import
      11. .indices
      12. .iotrace
      13. .load
      14. .log
      15. .mode
      16. .nullvalue
      17. .output
      18. .prompt
      19. .quit
      20. .read
      21. .restore
      22. .schema
      23. .separator
      24. .show
      25. .tables
      26. .timeout
      27. .timer
      28. .width
  17. C. SQLite SQL Command Reference
    1. SQLite SQL Commands
      1. ALTER TABLE
      2. ANALYZE
      3. ATTACH DATABASE
      4. BEGIN TRANSACTION
      5. COMMIT TRANSACTION
      6. CREATE INDEX
      7. CREATE TABLE
      8. CREATE TRIGGER
      9. CREATE VIEW
      10. CREATE VIRTUAL TABLE
      11. DELETE
      12. DETACH DATABASE
      13. DROP INDEX
      14. DROP TABLE
      15. DROP TRIGGER
      16. DROP VIEW
      17. END TRANSACTION
      18. EXPLAIN
      19. INSERT
      20. PRAGMA
      21. REINDEX
      22. RELEASE SAVEPOINT
      23. REPLACE
      24. ROLLBACK TRANSACTION
      25. SAVEPOINT
      26. SELECT
      27. UPDATE
      28. VACUUM
  18. D. SQLite SQL Expression Reference
    1. Literal Expressions
    2. Logic Representations
    3. Unary Expressions
    4. Binary Expressions
    5. Function Calls
    6. Column Names
    7. General Expressions
      1. AND
      2. BETWEEN
      3. CASE
      4. CAST
      5. COLLATE
      6. EXISTS
      7. GLOB
      8. IN
      9. IS
      10. ISNULL
      11. LIKE
      12. MATCH
      13. NOTNULL
      14. OR
      15. RAISE
      16. REGEXP
      17. SELECT
  19. E. SQLite SQL Function Reference
    1. Scalar Functions
      1. abs()
      2. changes()
      3. coalesce()
      4. date()
      5. datetime()
      6. glob()
      7. ifnull()
      8. hex()
      9. julianday()
      10. last_insert_rowid()
      11. length()
      12. like()
      13. load_extension()
      14. lower()
      15. ltrim()
      16. match()
      17. max()
      18. min()
      19. nullif()
      20. quote()
      21. random()
      22. randomblob()
      23. regex()
      24. replace()
      25. round()
      26. rtrim()
      27. sqlite_compileoption_get()
      28. sqlite_compileoption_used()
      29. sqlite_source_id()
      30. sqlite_version()
      31. strftime()
      32. substr()
      33. time()
      34. total_changes()
      35. trim()
      36. typeof()
      37. upper()
      38. zeroblob()
    2. Aggregate Functions
      1. avg()
      2. count()
      3. group_concat()
      4. max()
      5. min()
      6. sum()
      7. total()
  20. F. SQLite SQL PRAGMA Reference
    1. SQLite PRAGMAs
      1. auto_vacuum
      2. cache_size
      3. case_sensitive_like
      4. collation_list
      5. count_changes
      6. database_list
      7. default_cache_size
      8. encoding
      9. foreign_keys
      10. foreign_key_list
      11. freelist_count
      12. full_column_names
      13. fullfsync
      14. ignore_check_constraints
      15. incremental_vacuum
      16. index_info
      17. index_list
      18. integrity_check
      19. journal_mode
      20. journal_size_limit
      21. legacy_file_format
      22. locking_mode
      23. lock_proxy_file
      24. lock_status
      25. max_page_count
      26. omit_readlock
      27. page_count
      28. page_size
      29. parser_trace
      30. quick_check
      31. read_uncommitted
      32. recursive_triggers
      33. reverse_unordered_selects
      34. schema_version
      35. secure_delete
      36. short_column_names
      37. sql_trace
      38. synchronous
      39. table_info
      40. temp_store
      41. temp_store_directory
      42. user_version
      43. vdbe_trace
      44. vdbe_listing
      45. writable_schema
  21. G. SQLite C API Reference
    1. API Datatypes
      1. sqlite3
      2. sqlite3_backup
      3. sqlite3_blob
      4. sqlite3_context
      5. sqlite3_int64, sqlite3_uint64, sqlite_int64, sqlite_uint64
      6. sqlite3_module
      7. sqlite3_mutex
      8. sqlite3_stmt
      9. sqlite3_value
      10. sqlite3_vfs
    2. API Functions
      1. sqlite3_aggregate_context()
      2. sqlite3_auto_extension()
      3. sqlite3_backup_finish()
      4. sqlite3_backup_init()
      5. sqlite3_backup_pagecount()
      6. sqlite3_backup_remaining()
      7. sqlite3_backup_step()
      8. sqlite3_bind_xxx()
      9. sqlite3_bind_parameter_count()
      10. sqlite3_bind_parameter_index()
      11. sqlite3_bind_parameter_name()
      12. sqlite3_blob_bytes()
      13. sqlite3_blob_close()
      14. sqlite3_blob_open()
      15. sqlite3_blob_read()
      16. sqlite3_blob_write()
      17. sqlite3_busy_handler()
      18. sqlite3_busy_timeout()
      19. sqlite3_changes()
      20. sqlite3_clear_bindings()
      21. sqlite3_close()
      22. sqlite3_collation_needed()
      23. sqlite3_column_xxx()
      24. sqlite3_column_bytes()
      25. sqlite3_column_count()
      26. sqlite3_column_database_name()
      27. sqlite3_column_decltype()
      28. sqlite3_column_name()
      29. sqlite3_column_origin_name()
      30. sqlite3_column_table_name()
      31. sqlite3_column_type()
      32. sqlite3_commit_hook()
      33. sqlite3_compileoption_get()
      34. sqlite3_compileoption_used()
      35. sqlite3_complete()
      36. sqlite3_config()
      37. sqlite3_context_db_handle()
      38. sqlite3_create_collation()
      39. sqlite3_create_function()
      40. sqlite3_create_module()
      41. sqlite3_data_count()
      42. sqlite3_db_config()
      43. sqlite3_db_handle()
      44. sqlite3_db_mutex()
      45. sqlite3_db_status()
      46. sqlite3_declare_vtab()
      47. sqlite3_enable_load_extension()
      48. sqlite3_enable_shared_cache()
      49. sqlite3_errcode()
      50. sqlite3_errmsg()
      51. sqlite3_exec()
      52. sqlite3_extended_errcode()
      53. sqlite3_extended_result_codes()
      54. sqlite3_file_control()
      55. sqlite3_finalize()
      56. sqlite3_free()
      57. sqlite3_free_table()
      58. sqlite3_get_autocommit()
      59. sqlite3_get_auxdata()
      60. sqlite3_get_table()
      61. sqlite3_initialize()
      62. sqlite3_interrupt()
      63. sqlite3_last_insert_rowid()
      64. sqlite3_libversion()
      65. sqlite3_libversion_number()
      66. sqlite3_limit()
      67. sqlite3_load_extension()
      68. sqlite3_log()
      69. sqlite3_malloc()
      70. sqlite3_memory_highwater()
      71. sqlite3_memory_used()
      72. sqlite3_mprintf()
      73. sqlite3_mutex_alloc()
      74. sqlite3_mutex_enter()
      75. sqlite3_mutex_free()
      76. sqlite3_mutex_held()
      77. sqlite3_mutex_leave()
      78. sqlite3_mutex_notheld()
      79. sqlite3_mutex_try()
      80. sqlite3_next_stmt()
      81. sqlite3_open()
      82. sqlite3_open_v2()
      83. sqlite3_overload_function()
      84. sqlite3_prepare_xxx()
      85. sqlite3_profile()
      86. sqlite3_progress_handler()
      87. sqlite3_randomness()
      88. sqlite3_realloc()
      89. sqlite3_release_memory()
      90. sqlite3_reset()
      91. sqlite3_reset_auto_extension()
      92. sqlite3_result_xxx()
      93. sqlite3_result_error_xxx()
      94. sqlite3_rollback_hook()
      95. sqlite3_set_authorizer()
      96. sqlite3_set_auxdata()
      97. sqlite3_shutdown()
      98. sqlite3_sleep()
      99. sqlite3_snprintf()
      100. sqlite3_soft_heap_limit()
      101. sqlite3_sourceid()
      102. sqlite3_sql()
      103. sqlite3_status()
      104. sqlite3_step()
      105. sqlite3_stmt_status()
      106. sqlite3_strnicmp()
      107. sqlite3_table_column_metadata()
      108. sqlite3_threadsafe()
      109. sqlite3_total_changes()
      110. sqlite3_trace()
      111. sqlite3_unlock_notify()
      112. sqlite3_update_hook()
      113. sqlite3_user_data()
      114. sqlite3_value_xxx()
      115. sqlite3_value_bytes()
      116. sqlite3_value_numeric_type()
      117. sqlite3_value_type()
      118. sqlite3_version[]
      119. sqlite3_vfs_find()
      120. sqlite3_vfs_register()
      121. sqlite3_vfs_unregister()
      122. sqlite3_vmprintf()
  22. Index
  23. About the Author
  24. Colophon
  25. Copyright
O'Reilly logo

General Syntax

Before getting into specific commands in SQL, it is worth looking at the general language structure. Like most languages, SQL has a fairly complete expression syntax that can be used to define command parameters. A more detailed description of the expression support can be found in Appendix D.

Basic Syntax

SQL consists of a number of different commands, such as CREATE TABLE or INSERT. These commands are issued and processed one at a time. Each command implements a different action or feature of the database system.

Although it is customary to use all capital letters for SQL commands and keywords, SQL is a case-insensitive[1] language. All commands and keywords are case insensitive, as are identifiers (such as table names and column names).

Identifiers must be given as literals. If necessary, identifiers can be enclosed in the standards compliant double-quotes (" ") to allow the inclusion of spaces or other nonstandard characters in an identifier. SQLite also allows identifiers to be enclosed in square brackets ([ ]) or back ticks (` `) for compatibility with other popular database products. SQLite reserves the use of any identifier that uses sqlite_ as a prefix.

SQL is whitespace insensitive, including line breaks. Individual statements are separated by a semicolon. If you’re using an interactive application, such as the sqlite3 command-line tool, then you’ll need to use a semicolon to indicate the end of a statement. The semicolon is not strictly required for single statements, however, as it is properly a statement separator and not a statement terminator. When passing SQL commands into the programming API, the semicolon is not required unless you are passing more than one command statement within a single string.

Single-line comments start with a double dash (--) and go to the end of the line. SQL also supports multi-line comments using the C comment syntax (/* */).

As with most languages, numeric literals are represented bare. Both integer (453) and real (rational) numbers (43.23) are recognized, as is exponent-style scientific notation (9.745e-6). In order to avoid ambiguities in the parser, SQLite requires that the decimal point is always represented as a period (.), regardless of the current internationalization setting.

Text literals are enclosed in single quotes (' '). To represent a string literal that includes a single quote character, use two single quotes in a row (publisher = 'O''Reilly'). C-style backslash escapes ( \' ) are not part of the SQL standard and are not supported by SQLite. BLOB literals (binary data) can be represented as an x (or X) followed by a string literal of hexadecimal characters (x'A554E59C').

Warning

Text literals use single quotes. Double quotes are reserved for identifiers (table names, columns, etc.). C-style backslash escapes are not part of the SQL standard.

SQL statements and expressions frequently contain lists. A comma is used as the list separator. SQL does not allow for a trailing comma following the last item of a list.

In general, expressions can be used any place a literal data value is allowed. Expressions can include both mathematical statements, as well as functions. Function-calling syntax is similar to most other computer languages, utilizing the name of the function, followed by a list of parameters enclosed in parentheses. Expressions can be grouped into subexpressions using parentheses.

If an expression is evaluated in the context of a row (such as a filtering expression), the value of a row element can be extracted by naming the column. You may have to qualify the column name with a table name or alias. If you’re using cross-database queries, you may also have to specify which database you’re referring to. The syntax is:

[[database_name.]table_name.]column_name

If no database name is given, it is assumed you’re referring to the main database on the default connection. If the table name/alias is also omitted, the system will make a best-guess using just the column name, but will return an error if the name is ambiguous.

Three-Valued Logic

SQL allows any value to be assigned a NULL. NULL is not a value in itself (SQLite actually implements it as a unique valueless type), but is used as a marker or flag to represent unknown or missing data. The thought is that there are times when values for a specific row element may not be available or may not be applicable.

A NULL may not be a value, but it can be assigned to data elements that normally have values, and can therefore show up in expressions. The problem is that NULLs don’t interact well with other values. If a NULL represents an unknown that might be any possible value, how can we know if the expression NULL > 3 is true or false?

To deal with this problem, SQL must employ a concept called three-valued logic. Three-valued logic is often abbreviated TVL or 3VL, and is more formally known as ternary logic. 3VL essentially adds an “unknown” state to the familiar true/false Boolean logic system.

Here are the truth tables for the 3VL operators NOT, AND, and OR:

ValueNOT Value
TrueFalse
FalseTrue
NULLNULL
3VL AND TRUE FALSE NULL
TRUE TRUEFALSENULL
FALSE FALSEFALSEFALSE
NULL NULLFALSENULL
3VL OR TRUE FALSE NULL
TRUE TRUETRUETRUE
FALSE TRUEFALSENULL
NULL TRUENULLNULL

3VL also dictates how comparisons work. For example, any equality check (=) involving a NULL will evaluate to NULL, including NULL = NULL. Remember that NULL is not a value, it is a flag for the unknown, so the expression NULL = NULL is asking, “Does this unknown equal that unknown?” The only practical answer is, “That is unknown.” It might, but it might not. Similar rules apply to greater-than, less-than, and other comparisons.

Warning

You cannot use the equality operator (=) to test for NULLs. You must use the IS NULL operator.

If you’re having trouble resolving an expression, just remember that a NULL marks an unknown or unresolved value. This is why the expression False AND NULL is false, but True AND NULL is NULL. In the case of the first expression, the NULL can be replaced by either true or false without altering the expression result. That isn’t true of the second expression, where the outcome is unknown (in other words, NULL) because the output depends on the unknown input.

Simple Operators

SQLite supports the following unary prefix operators:

- +

These adjust the sign of a value. The “-” operator flips the sign of the value, effectively multiplying it by -1.0. The “+” operator is essentially a no-op, leaving a value with the same sign it previously had. It does not make negative values positive.

~

As in the C language, the “~” operator performs a bit-wise inversion. This operator is not part of the SQL standard.

NOT

The NOT operator reverses a Boolean expression using 3VL.

There are also a number of binary operators. They are listed here in descending precedence.

||

String concatenation. This is the only string concatenation operator recognized by the SQL standard. Many other database products allow “+” to be used for concatenation, but SQLite does not.

+ - * / %

Standard arithmetic operators for addition, subtraction, multiplication, division, and modulus (remainder).

| & << >>

The bitwise operators or, and, and shift-high/shift-low, as found in the C language. These operators are not part of the SQL standard.

< <= => >

Comparison test operators. Again, just as in the C language we have less-than, less-than or equal, greater-than or equal, and greater than. These operators are subject to SQL’s 3VL regarding NULLs.

= == != <>

Equality test operators. Both “=” and “==” will test for equality, while both “!=” and “<>” test for inequality. Being logic operators, these tests are subject to SQL’s 3VL regarding NULLs. Specifically, value = NULL will always return NULL.

IN LIKE GLOB MATCH REGEXP

These five keywords are logic operators, returning, true, false, or NULL state. See Appendix D for more specifics on these operators.

AND OR

Logical operators. Again, they are subject to SQL’s 3VL.

In addition to these basics, SQL supports a number of specific expression operations. For more information on these and any SQL-specific expressions, see Appendix D.



[1] Unless otherwise specified, case insensitivity only applies to ASCII characters. That is, characters represented by values less than 128.

The best content for your career. Discover unlimited learning on demand for around $1/day.