CSCI 343 SQL expressions

See the expressions page of the SQLite3 documentation for much more information.

For single values

C/Java vs SQL

Many C/Java operators work with SQL; however, there are some notable exceptions. Here are some things to watch out for.

• Boolean operators need to be spelled out, e.g., AND, OR, and NOT.
• The || operator concatenates two strings.
• Use <> to test if two expressions are not equal.
• Both the single equal sign, =, and the double equal sign, ==, can be used to test for equality.
• Generally when one of the operands is NULL, the result will be NULL.
• However, the operators IS and IS NOT can test for equality even with NULL operands.

And here are a few things that are different but can be avoided.

• SQL has a tertiary BETWEEN operator that can test the range of a number, e.g., 5 BETWEEN 3 AND 7 is true.
• SQL has a similar CASE operator which resembles the case statement of C/Java, e.g., CASE x WHEN x > 0 THEN x ELSE -x.
• Like in C, 0 is considered false and 1 is considered true; however, '6x' is also considered true.
• SQL has a rather wordy cast operators, e.g., CAST('6x' AS INTEGER).

Functions

SQL has a number of core functions that can be applied to single values. The most common, by far, seems to be abs.

String matching

SQL uses the LIKE operator for string matches. With LIKE, the % matches zero or more characters and the _ matches one character By default, the string matching operator is not case sensitive. For example, 'SQL' LIKE 'S%' and 'SQL' LIKE 'S_L' and 'SQL' LIKE 'sql' are all true.

Some SQL implementations even support a Unix-style glob.

For tables

As you would expect for a relational database application language, there are many operators that work on tables.

Membership tests

SQL has a few set operators for testing a value against the result of a table returned from a SELECT.

• EXISTS is a unary operator that tests if a table has any elements.
• IN and NOT IN are binary operators that test for membership in a table.

Aggregate function

Aggregate functions are applied to values from a table to generate a single value. All of these ignore NULL values inside the table.

• avg
• count
• max
• min
• sum
• total

Modified comparison

The SQL standard provides modified comparisons that test a single value against all the values of a table. This is done by placing ALL or ANY after a comparison operators. Modified comparisons are not supported in sqlite3.

Literals

• SQL has a NULL ”value“.
• To be compatible with all versions of SQL, you should surround strings with single quotes. To include a single quote within a string, use the Pascal convention, e.g., 'ain''t'.
• The floating point numbers of C/Java and SQL are the same.
• SQL does not support C/Java style octal and hexadecimal literals.

Testing

You can use the SELECT statement of SQL to test your expressions. Here are some examples.

SELECT 5 + 6 ;
SELECT 1 AND 0 ;
SELECT 1 OR 0 ;
SELECT 1 <> 0 ;
SELECT 1 = 0 ;
SELECT 1 == NULL ;
SELECT 0 == NULL ;
SELECT 1 IS NULL ;
SELECT 5 BETWEEN 3 AND 7 ;
SELECT 0 AND 1 + 5 ;
SELECT (0 AND 1) + 5 ;
SELECT ABS(-5) ;
SELECT 'SQL' LIKE 'S%' ;
SELECT 'SQL' LIKE 'S_L' ;
SELECT 'SQL' LIKE 'sql' ;
SELECT 'banananana' GLOB 'ba[^t]*' ;
SELECT CAST(5 as integer) ;
SELECT CAST('5' as integer) ;
SELECT CAST('x' as integer) ;
SELECT CAST('1x' as integer) ;
SELECT 'ok' ;
SELECT 070 ;
CREATE TABLE t(a INTEGER) ;
INSERT INTO t VALUES(2) ;
INSERT INTO t VALUES(3) ;
INSERT INTO t VALUES(5) ;
INSERT INTO t VALUES(7) ;
SELECT 5 IN t ;
SELECT 4 IN t ;
SELECT COUNT(a) FROM t ;
SELECT AVG(a) FROM t ;
SELECT MAX(a) FROM t;
SELECT 14 | 5 ;
SELECT 14 & 5 ;
SELECT 14 >> 2 ;