Luminesce SQL keywords

Keyword

Description

Example

SELECT

Starts a query and lists the columns to return.

select Name, Age from @people

FROM

Names the provider, table variable or subquery to read from.

select * from Lusid.Portfolio

WHERE

Keeps only the rows that match a condition. Also sets provider parameters.

select * from @x where Age > 30

GROUP

With BY, groups rows for aggregate functions. Also forms EXCLUDE GROUP in a window frame.

select Dept, count(*) from @x group by Dept

BY

Used in GROUP BY, ORDER BY and PARTITION BY.

select * from @x order by Name

HAVING

Filters groups after GROUP BY.

select Dept from @x group by Dept having count(*) > 5

ORDER

With BY, sorts the result.

select * from @x order by Name

ASC

Sorts in ascending order (the default).

order by Name asc

DESC

Sorts in descending order.

order by Age desc

NULLS

With FIRST or LAST, puts null values at the start or end of a sort.

order by Age nulls last

FIRST

Used in NULLS FIRST.

order by Age desc nulls first

LAST

Used in NULLS LAST.

order by Age nulls last

LIMIT

Returns at most the given number of rows.

select * from @x limit 10

OFFSET

Skips the given number of rows before returning any.

select * from @x limit 10 offset 20

DISTINCT

Removes duplicate rows, or duplicate values inside an aggregate function.

select distinct Dept from @x

ALL

Keeps duplicate rows. Mostly used in UNION ALL.

select a from @x union all select a from @y

AS

Names a column, table or window. Also sets the type in CAST.

select Age as Years from @x

VALUES

Builds a table from literal rows.

@x = values ('a', 1), ('b', 2);

UNION

Combines two results and removes duplicate rows.

select a from @x union select a from @y

INTERSECT

Returns the rows that appear in both results.

select a from @x intersect select a from @y

EXCEPT

Returns the rows in the first result that are not in the second. In select * (except ...), leaves out the named columns.

select * (except Age) from @x

EXISTS

True if a subquery returns at least one row.

select * from @x where exists (select 1 from @y)

PRAGMA

Changes a setting for the current query. See PRAGMAs.

pragma TimeoutSec = 1800;

EXPLAIN

With QUERY PLAN, reports SQLite's plan for a statement as progress messages. The query still runs.

explain query plan select * from @x

QUERY

Used in EXPLAIN QUERY PLAN.

explain query plan select * from @x

PLAN

Used in EXPLAIN QUERY PLAN.

explain query plan select * from @x

Keyword

Description

Example

JOIN

Combines rows from two sources.

select * from @a a join @b b on a.Id = b.Id

INNER

Returns only the rows that match on both sides. This is the default join.

@a a inner join @b b on a.Id = b.Id

LEFT

Returns every row from the left side, with nulls where the right side has no match.

@a a left join @b b on a.Id = b.Id

OUTER

Optional in LEFT OUTER JOIN and FULL OUTER JOIN. Also forms OUTER APPLY.

@a a left outer join @b b on a.Id = b.Id

FULL

Returns every row from both sides, with nulls where either side has no match.

@a a full join @b b on a.Id = b.Id

CROSS

Pairs every row on the left with every row on the right. Also forms CROSS APPLY.

select * from @a cross join @b

ON

Sets the join condition.

@a a join @b b on a.Id = b.Id

USING

Joins on columns that have the same name on both sides.

@a join @b using (Id)

Keyword

Description

Example

AND

True if both conditions are true.

where Age > 30 and Dept = 'IT'

OR

True if either condition is true.

where Dept = 'IT' or Dept = 'HR'

NOT

Reverses a condition.

where not Active

IN

True if a value is in a list, a subquery or a table variable.

where Dept in ('IT', 'HR')

NOT IN

True if a value is not in a list, a subquery or a table variable.

where Id not in @excluded

IS

Compares two values, treating two nulls as equal.

where a is b

IS NOT

The opposite of IS.

where a is not b

IS NULL

True if a value is null. The SQLite shorthand x ISNULL is not supported.

where Nickname is null

IS NOT NULL

True if a value is not null. The SQLite shorthand x NOTNULL is not supported.

where Nickname is not null

BETWEEN

True if a value is in a range, including both ends.

where Age between 30 and 40

NOT BETWEEN

True if a value is outside a range.

where Age not between 30 and 40

LIKE

Matches a pattern, where % is any run of characters and _ is one character.

where Name like 'Jo%'

NOT LIKE

True if a value does not match a LIKE pattern.

where Name not like 'Jo%'

ESCAPE

Sets the character that makes % or _ literal in a LIKE pattern.

where Code like '10\%%' escape '\'

GLOB

Matches a Unix-style pattern, where * is any run of characters and ? is one character.

where FileName glob '*.csv'

NOT GLOB

True if a value does not match a GLOB pattern.

where FileName not glob '*.tmp'

REGEXP

Matches a regular expression. See REGEXP.

where Code regexp '[A-Z]+-[0-9]+'

NOT REGEXP

True if a value does not match a regular expression.

where Code not regexp '[0-9]'

CASE

Starts a conditional expression.

case when Age < 30 then 'young' else 'old' end

WHEN

Gives a condition in a CASE expression.

case when Age < 30 then 'young' end

THEN

Gives the result for a WHEN condition.

case when Age < 30 then 'young' end

ELSE

Gives the result when no WHEN condition is true.

case when Age < 30 then 'young' else 'old' end

END

Ends a CASE expression.

case when Age < 30 then 'young' end

CAST

Converts a value to another type.

cast(Age as text)

COLLATE NOCASE

Compares text ignoring case, but not accents.

where Name = 'JOSE' collate nocase

COLLATE NOACCENT

Compares text ignoring both case and accents. This is the default.

where Name = 'jose' collate noaccent

COLLATE EXACT

Compares text exactly, respecting case and accents.

where Name = 'José' collate exact

NULL

A missing value.

select null as Nothing

TRUE

The boolean value true.

where Active = true

FALSE

The boolean value false.

where Active = false

CURRENT_DATE

The current date, in UTC.

select current_date

CURRENT_TIME

The current time, in UTC.

select current_time

CURRENT_TIMESTAMP

The current date and time, in UTC.

select current_timestamp

Keyword

Description

Example

OVER

Makes an aggregate or window function work over a window of rows.

sum(Pay) over (order by StartDate)

PARTITION

With BY, splits the rows into separate windows.

sum(Pay) over (partition by Dept)

WINDOW

Names a window definition so you can reuse it.

select rank() over w from @x window w as (order by Age)

FILTER

Limits the rows an aggregate function uses.

count(*) filter (where Age > 30)

ROWS

Defines the window frame as a number of rows.

sum(x) over (rows 2 preceding)

RANGE

Defines the window frame by a range of ORDER BY values.

sum(x) over (order by Day range 7 preceding)

GROUPS

Defines the window frame as a number of peer groups.

sum(x) over (order by Day groups 1 preceding)

UNBOUNDED

Makes the frame start at the first row, or end at the last row.

rows between unbounded preceding and current row

PRECEDING

Counts the frame start back from the current row.

rows 3 preceding

FOLLOWING

Counts the frame end on from the current row.

rows between current row and 1 following

CURRENT

Used in CURRENT ROW.

rows between 1 preceding and current row

ROW

Used in CURRENT ROW.

rows between 1 preceding and current row

EXCLUDE

Leaves some rows out of the frame.

rows 2 preceding exclude current row

NO

Used in EXCLUDE NO OTHERS.

rows 2 preceding exclude no others

OTHERS

Used in EXCLUDE NO OTHERS, which leaves nothing out (the default).

rows 2 preceding exclude no others

TIES

Used in EXCLUDE TIES, which leaves out the current row's peers but keeps the current row.

rows 2 preceding exclude ties

Keyword

Description

Example

USE

Starts a block of input for a direct provider. The block ends with ENDUSE.

@x = use Some.Provider
--option=value
enduse;

ENDUSE

Ends a USE block.

@x = use Some.Provider
--option=value
enduse;

WITH

Passes table or scalar variables into a direct provider. Unlike SQLite, it does not define common table expressions; use table variables instead.

use Some.Provider with @data, @@date enduse

FORCE

Makes sure the named columns exist in a direct provider's result, even if it returns no rows. FORCE can also use as a bare column or table name.

use Some.Provider force ColA, ColB enduse

APPLY

With CROSS or OUTER, runs a subquery once for each row. See FOR-EACH loops.

from @ids i cross apply (select * from Some.Provider where Id = i.Id) r

WAIT

Waits for everything before it to finish, plus an optional number of seconds. See WAIT.

select * from @view wait 5

Keyword

Description

Example

TYPES

Sets the types of the columns returned by the next SELECT.

types bigint, text; select 1, 2

BIGINT

A 64-bit integer.

types bigint; select 1

INT

A 32-bit integer.

types int; select 1

BOOLEAN

True or false.

types boolean; select 1

DOUBLE

A double-precision floating-point number.

types double; select 1

DECIMAL

A decimal number.

types decimal; select 1.5

TEXT

A string.

types text; select 1

Keyword

Description

Example

CHECK

Reserved.

select [Check] from @x

TO

Reserved.

select [To] from @x

COLLATE

Reserved on its own. Use COLLATE NOCASE, COLLATE NOACCENT or COLLATE EXACT.

select [Collate] from @x

Keyword

Description

Example

ABS

Returns the absolute value of a number.

abs(-5)

CHAR

Reserved function.

COALESCE

Returns the first argument that is not null.

coalesce(Nickname, Name)

GLOB

Function form of the GLOB operator: glob(X,Y) is the same as Y GLOB X.

glob('*.csv', FileName)

HEX

Reserved function.

IIF

Returns the second argument if the first is true, otherwise the third.

iif(Age > 30, 'old', 'young')

IFNULL

Returns the first argument if it is not null, otherwise the second.

ifnull(Nickname, Name)

INSTR

Returns the position of the first occurrence of one string in another, or 0.

instr('hello', 'l')

LENGTH

Returns the number of characters in a string.

length('hello')

LIKE

Function form of the LIKE operator: like(X,Y) is the same as Y LIKE X.

like('Jo%', Name)

LIKELY

Reserved function.

LOWER

Converts a string to lower case.

lower('Hello')

LTRIM

Removes spaces, or the given characters, from the start of a string.

ltrim('  hello')

MAX

With one argument, returns the largest value in a group. With several, returns the largest argument.

max(1, 5, 3)

MIN

With one argument, returns the smallest value in a group. With several, returns the smallest argument.

min(1, 5, 3)

NULLIF

Returns null if the two arguments are equal, otherwise the first.

nullif(Qty, 0)

PRINTF

Formats values into a string, like the C printf() function.

printf('%.2f', 3.14159)

QUOTE

Returns a value as an SQL literal.

quote('it''s')

RANDOM

Returns a random 64-bit integer.

random()

RANDOMBLOB

Returns a blob of the given number of random bytes.

randomblob(16)

REPLACE

Replaces every occurrence of one string with another.

replace('a-b-c', '-', '+')

ROUND

Rounds a number to the given number of decimal places.

round(3.14159, 2)

RTRIM

Removes spaces, or the given characters, from the end of a string.

rtrim('hello  ')

SOUNDEX

Returns the soundex code of a string.

soundex('Robert')

SUBSTR

Returns part of a string, from a start position and an optional length.

substr('hello', 2, 3)

TRIM

Removes spaces, or the given characters, from both ends of a string.

trim('  hello  ')

TYPEOF

Returns the storage type of a value: null, integer, real, text or blob.

typeof(1.5)

UNICODE

Returns the Unicode code point of the first character of a string.

unicode('A')

UNLIKELY

Reserved function.

UPPER

Converts a string to upper case.

upper('hello')

ZEROBLOB

Reserved function.

Keyword

Description

Example

DATE

Returns a date as YYYY-MM-DD.

date('now', '-1 day')

TIME

Returns a time as HH:MM:SS.

time('now')

DATETIME

Returns a date and time as YYYY-MM-DD HH:MM:SS.

datetime('now', '+1 hour')

JULIANDAY

Returns the Julian day number of a date.

julianday('now')

STRFTIME

Formats a date using a format string.

strftime('%Y-%m', 'now')

Keyword

Description

Example

AVG

Returns the average of the values that are not null.

avg(Price)

COUNT

Counts rows, or the values that are not null.

count(*)

GROUP_CONCAT

Joins values into one string, separated by commas or the given separator.

group_concat(Name, '; ')

SUM

Returns the sum of the values, or null if there are none.

sum(Qty)

TOTAL

Like SUM, but returns 0.0 rather than null when there are no values.

total(Qty)

Keyword

Description

Example

ROW_NUMBER

Returns the number of the row in its partition, starting at 1.

row_number() over (order by Age)

RANK

Returns the rank of the row, with gaps after ties.

rank() over (order by Score desc)

DENSE_RANK

Returns the rank of the row, without gaps after ties.

dense_rank() over (order by Score desc)

PERCENT_RANK

Returns the relative rank of the row, from 0.0 to 1.0.

percent_rank() over (order by Score)

CUME_DIST

Returns the cumulative distribution of the row, from 0.0 to 1.0.

cume_dist() over (order by Score)

NTILE

Splits the partition into N groups and returns the group number of the row.

ntile(4) over (order by Score)

LAG

Returns a value from an earlier row.

lag(Price) over (order by TradeDate)

LEAD

Returns a value from a later row.

lead(Price) over (order by TradeDate)

FIRST_VALUE

Returns a value from the first row of the frame.

first_value(Price) over (order by TradeDate)

LAST_VALUE

Returns a value from the last row of the frame.

last_value(Price) over (order by TradeDate)

NTH_VALUE

Returns a value from the Nth row of the frame.

nth_value(Price, 2) over (order by TradeDate)

Keyword

Description

Example

STDEV

Returns the standard deviation of the values.

stdev(Price)

VARIANCE

Returns the variance of the values.

variance(Price)

MODE

Returns the most common value.

mode(Rating)

MEDIAN

Returns the median value.

median(Price)

LOWER_QUARTILE

Returns the lower quartile (25th percentile).

lower_quartile(Price)

UPPER_QUARTILE

Returns the upper quartile (75th percentile).

upper_quartile(Price)

REPLICATE

Repeats a string the given number of times.

replicate('ab', 3)

LEFTSTR

Returns the given number of characters from the start of a string.

leftstr('hello', 2)

RIGHTSTR

Returns the given number of characters from the end of a string.

rightstr('hello', 2)

REVERSE

Reverses a string.

reverse('hello')

PROPER

Capitalises the first letter of each word.

proper('hello world')

PADL

Pads a string on the left with spaces to the given length.

padl('7', 3)

PADR

Pads a string on the right with spaces to the given length.

padr('7', 3)

PADC

Pads a string on both sides with spaces to centre it in the given length.

padc('7', 3)

STRFILTER

Keeps only the characters that appear in a second string (case-sensitive).

strfilter('a1b2', '0123456789')

CHARINDEX

Returns the position of one string in another, optionally starting from a given position.

charindex('de', 'abcdefdex')

DIFFERENCE

Compares the soundex codes of two strings and returns 0 (not alike) to 4 (very alike).

difference('hello', 'hallo')

LOG

Returns the natural logarithm.

log(10)

LOG10

Returns the base-10 logarithm.

log10(1000)

EXP

Returns e raised to the given power.

exp(1)

POWER

Raises a number to a power.

power(2, 10)

SIGN

Returns 1, 0 or -1 for a positive, zero or negative number.

sign(-5)

SQRT

Returns the square root.

sqrt(16)

SQUARE

Returns the square.

square(4)

CEIL

Rounds up to the next integer.

ceil(1.2)

FLOOR

Rounds down to the next integer.

floor(1.8)

Keyword

Description

Example

JSON

Checks that a string is valid JSON and returns it minified.

json(' { "a" : 1 } ')

JSON_ARRAY

Builds a JSON array from its arguments.

json_array(1, 2, 'x')

JSON_ARRAY_LENGTH

Returns the number of elements in a JSON array.

json_array_length('[1,2,3]')

JSON_EXTRACT

Returns the value at a path in a JSON document.

json_extract('{"a":{"b":2}}', '$.a.b')

JSON_INSERT

Adds a value at a path, if nothing is there already.

json_insert('{"a":1}', '$.b', 2)

JSON_OBJECT

Builds a JSON object from name and value pairs.

json_object('a', 1, 'b', 'x')

JSON_PATCH

Merges one JSON object into another.

json_patch('{"a":1}', '{"b":2}')

JSON_QUOTE

Converts a value to its JSON form.

json_quote('hello')

JSON_REMOVE

Removes the values at the given paths.

json_remove('{"a":1,"b":2}', '$.a')

JSON_TYPE

Returns the type of a JSON value, such as object, array or text.

json_type('[1,2]')

JSON_VALID

Returns 1 if a string is valid JSON, otherwise 0.

json_valid('{"a":1}')

JSON_GROUP_ARRAY

Aggregates values into a JSON array.

json_group_array(Name)

JSON_GROUP_OBJECT

Aggregates name and value pairs into a JSON object.

json_group_object(Name, Age)