LabKey SQL is a SQL dialect that supports (1) most standard SQL functionality and (2) provides extended functionality that is unique to LabKey, including:
Keyword | Description |
AS | Aliases can be explicitly named using the AS keyword. Note that the AS keyword is optional: the following select clauses both create an alias called "Name":
SELECT LCASE(FirstName) AS Name Implicit aliases are automatically generated for expressions in the SELECT list. In the query below, an output column named "Expression1" is automatically created for the expression "LCASE(FirstName)": SELECT LCASE(FirstName) |
ASCENDING, ASC | Return results in ascending value order. ORDER BY Weight ASC |
CAST(AS) | CAST(R.d AS VARCHAR)
Defined valid datatype keywords which can be used as cast/convert targets, and to what java.sql.Types name each keyword maps. Keywords are case-insensitive. BIGINT Examples: CAST(TimeCreated AS DATE) CAST(WEEK(i.date) as INTEGER) as WeekOfYear, |
DESCENDING, DESC | Return results in descending value order. |
DISTINCT | Return distinct, non duplicate values. SELECT DISTINCT Country FROM Demographics |
FALSE | |
FROM |
The FROM clause in LabKey SQL must contain at least one table. It can also contain JOINs to other tables. Commas are supported in the FROM clause: To refer to tables in LabKey folders other than the current folder, see Cross-Folder Queries. |
GROUP BY | Used with aggregate functions to group the results. Defines the "for each" or "per". The example below returns the number of records "for each" participant:
SELECT "Physical Exam".ParticipantId, COUNT("Physical Exam".Created) "Number of Records" |
HAVING | Used with aggregate functions to limit the results. The following example returns participants with 10 or more records in the Physical Exam table: SELECT "Physical Exam".ParticipantId, COUNT("Physical Exam".Created) "Number of Records" FROM "Physical Exam" GROUP BY "Physical Exam".ParticipantId HAVINGCOUNT("Physical Exam".Created) > 10 |
JOIN, RIGHT JOIN, LEFT JOIN, FULL JOIN |
Example: SELECT "Physical Exam".* FROM "Physical Exam" FULL JOIN "Lab Results" ON "Physical Exam".ParticipantId = "Lab Results".ParticipantId |
LIMIT | Limits the number or records returned by the query. The following example returns the 10 most recent records:
SELECT * |
ORDER BY | Often used with LIMIT to improve performance: SELECT "Physical Exam".ParticipantID, "Physical Exam".Height_cm AS Height FROM "Physical Exam" ORDER BY Height DESC LIMIT 5 Troubleshooting: "Why is the ORDER BY clause being ignored?" When authoring queries in LabKey SQL, ORDER BY clauses may appear to not be respected in the results displayed to the user. This is because a LabKey SQL query is typically processed as a subquery within a parent query, and the parent's sort order overrides the ORDER BY clause in the subquery. |
PARAMETERS | Queries can declare parameters using the PARAMETERS keyword. Default values data types are supported as shown below:
PARAMETERS (X INTEGER DEFAULT 37) Parameter names will override any unqualified table column with the same name. Use a table qualification to disambiguate. In the example below, R.X refers to the column while X refers to the parameter: PARAMETERS(X INTEGER DEFAULT 5) Supported data types for parameters are: BIGINT, BIT, CHAR, DECIMAL, DOUBLE, FLOAT, INTEGER, LONGVARCHAR, NUMERIC, REAL, SMALLINT, TIMESTAMP, TINYINT, VARCHAR Parameter values can be passed via JavaScript API calls to the query. For details see Parameterized SQL Queries. |
PIVOT BY | Re-visualize a table by rotating or "pivoting" a portion of it, essentially promoting cell data to column headers. See Write a Pivot Query for examples. |
SELECT | SELECTqueries are the only type of query that can currently be written in LabKey SQL. Sub-selects are allowed both asanexpression,and in the FROM clause.
Aliases are automatically generated for expressions after SELECT. In the query below, an output column named "Expression1" is automatically generated for the expression "LCASE(FirstName)": SELECT LCASE(FirstName) FROM... |
TRUE | |
UNION, UNION ALL | The UNION clause is the same as standard SQL. LabKey SQL supports UNION in subqueries. |
WHERE | Filter the results for certain values. Example: SELECT "Physical Exam".* FROM "Physical Exam" WHERE YEAR(Date) = '2010' |
Operator | Description |
String Operators | |
|| | String concatenation. For example: SELECT Demographics.ParticipantId, Demographics.City || ', ' || Demographics.State AS CityOfOrigin FROM Demographics |
LIKE | |
NOT LIKE | |
Arithmetic Operators | |
+ | Add |
- | Subtract |
* | Multiply |
/ | Divide |
Comparison operators | |
= | Equals |
!= | Does not equal |
<> | Does not equal |
> | Is greater than |
< | Is less than |
>= | Is greater than or equal to |
<= | Is less than or equal to |
IS NULL | Is NULL |
IS NOT NULL | Is NOT NULL |
BETWEEN | Between two values. Values can be numbers, strings or dates. |
IN | In |
NOT IN | Not in |
Bitwise Operators | |
& | Bitwise AND |
| | Bitwise OR |
^ | Bitwise exclusive OR |
AND Operators | |
AND | Logical AND |
OR | Logical OR |
LIKE | Like |
NOT LIKE | Not like |
Order of Precedence | Operators |
1 | - (unary) , + (unary), CASE |
2 | *, / (multiplication, division) |
3 | +, -, & (binary plus, binary minus) |
4 | & (bitwise and) |
5 | ^ (bitwise xor) |
6 | | (bitwise or) |
7 | || (concatenation) |
8 | <, >, <=, >=, IN, NOT IN, BETWEEN, NOT BETWEEN, LIKE, NOT LIKE |
9 | =, IS, IS NOT, <>, != |
10 | NOT |
11 | AND |
12 | OR |
NOTE: Prior to 14.3 bitwise operator precedence varies based on the underlying SQL database. We suggest always using parentheses when using bitwise operators.
Function | Description |
COUNT | The special syntax COUNT(*) is supported as of LabKey v9.2. |
MIN | Minimum |
MAX | Maximum |
AVG | Average |
SUM | Sum |
STDDEV | Standard deviation |
GROUP_CONCAT | An aggregate function, much like MAX, MIN, AVG, COUNT, etc. It can be used wherever the standard aggregate functions can be used, and is subject to the same grouping rules. Like the built-in MySQL functionality, it will return a string value which is comma-separated list of all of the values for that grouping. A custom separator, instead of the default comma, can be specified. The example below specifies a semi-colon as the separator: SELECT Participant, GROUP_CONCAT(DISTINCT Category, ';') AS CATEGORIES FROM SomeSchema.SomeTable To use a line-break as the separator, use the following: SELECT Participant, GROUP_CONCAT(DISTINCT Category, chr(10)) AS CATEGORIES FROM SomeSchema.SomeTable |
Many of these functions are similar to standard SQL functions -- see the JBDC escape syntax documentation for additional information.
Function | Description |
abs(value) | Returns the absolute value. |
acos(value) | Returns the arc cosine. |
age(date1, date2) |
Supplies the difference in age between the two dates, calculated in years. |
age(date1, date2, interval) |
The interval indicates the unit of age measurement, either SQL_TSI_MONTH or SQL_TSI_YEAR. |
age_in_months(date1, date2) | Behavior is undefined if date2 is before date1. |
age_in_years(date1, date2) | Behavior is undefined if date2 is before date1. |
asin(value) | Returns the arc sine. |
atan(value) | Returns the arc tangent. |
atan2(value1, value2) | Returns the arctangent of the quotient of two values. |
case | LabKey SQL parser sometimes requires the use of additional parentheses within the statement. CASE (value) WHEN (test1) THEN (result1) ELSE (result2) END CASE WHEN (test1) THEN (result1) ELSE (result2) END |
ceiling(value) | Rounds the value up. |
coalesce(value1,...,valueN) | Returns the first non-null value in the argument list. Use to set default values for display. |
concat(value1,value2) | Concatenates two values. |
contextPath() | Returns the context path starting with “/” (e.g. “/labkey”). Returns the empty string if there is no current context path. (Returns VARCHAR.) |
cos(radians) | Returns the cosine. |
cot(radians) | Returns the cotangent. |
curdate() | Returns the current date. |
curtime() | Returns the current time |
dayofmonth(date) | Returns the day of the month (1-31) for a given date. |
dayofweek(date) | Returns the day of the week (1-7) for a given date. (Sun=1 and Sat=7) |
dayofyear(date) | Returns the day of the year (1-365) for a given date. |
degrees(radians) | Returns degrees based on the given radians. |
exp(n) | Returns Euler's number e raised to the nth power. e = 2.71828183 |
floor(value) | Rounds down to the nearest integer. |
folderName() | LabKey SQL extension function. Returns the name of the current folder, without beginning or trailing "/". (Returns VARCHAR.) |
folderPath() | LabKey SQL extension function. Returns the current folder path (starts with “/”, but does not end with “/”). The root returns “/”. (Returns VARCHAR.) |
greatest(a, b, c, ...) | Returns the greatest value from the list expressions provided. Any number of expressions may be used. The expressions must have the same data type, which will also be the type of the result. The LEAST() function is similar, but returns the smallest value from the list of expressions. GREATEST() and LEAST() are not implemented for SAS databases.
When NULL values appear in the list of expressions, different database implementations as follows: Example: SELECT greatest(MyAssay.score_1, MyAssay.score_2, MyAssay.score_3) As HIGH_SCORE |
hour(time) | Returns the hour for a given date/time. |
ifdefined(column_name) | IFDEFINED(NAME) allows queries to reference columns that may not be present on a table. Without using IFDEFINED(), LabKey will raise a SQL parse error if the column cannot be resolved. Using IFDEFINED(), a column that cannot be resolved is treated as a NULL value. The IFDEFINED() syntax is useful for writing queries over PIVOT queries or assay tables where columns may be added or removed by an administrator. |
ifnull(testValue, defaultValue) | If testValue is null, returns the defaultValue. Example: IFNULL(Units,0) |
isequal | LabKey SQL extension function. ISEQUAL(a,b) is equivalent to (a=b OR (a IS NULL AND b IS NULL)) |
ismemberof(groupid) | LabKey SQL extension function. Returns true if the current user is a member of the specified group. |
javaConstant(fieldName) | LabKey SQL extension function. Provides access to public static final variable values. For details see Using SQL Functions in Java Modules. |
lcase(string) | Convert all characters of a string to lower case. |
least(a, b, c, ...) | Returns the smallest value from the list expressions provided. For more details, see greatest() above. |
left(string, integer) | Returns the left side of the string, to the given number of characters. Example: SELECT LEFT('STRINGVALUE',3) returns 'STR' |
length(string) | Returns the length of the given string. |
locate(substring, string) locate(substring, string, startIndex) | Returns the location of the first occurrence of substring within string. startIndex provides a starting position to begin the search. |
log(n) | Returns the natural logarithm of n. |
log10(n) | Base base 10 logarithm on n. |
lower(string) | Convert all characters of a string to lower case. |
ltrim(string) | Trims white space characters from the left side of the string. For example: LTRIM(' Trim String') |
minute(time) | Returns the minute value for the given time. |
mod(dividend, divider) | Returns the remainderofthedivisionofdividendbydivider. |
moduleProperty(module name, property name) |
LabKey SQL extension function. Returns a module property, based on the module and property names. For details see Using SQL Functions in Java Modules. |
month(date) | Returns the month value (1-12) of the given date. |
monthname(date) | Return the month name of the given date. |
now() | Returns the system date and time. |
overlaps | LabKey SQL extension function. Supported only when Postrgres is installed as the primary database. SELECT OVERLAPS (START1, END1, START2, END2) AS COLUMN1 FROM MYTABLE The LabKey SQL syntax above is translated into the following Postgres syntax: SELECT (START1, END1) OVERLAPS (START2, END2) AS COLUMN1 FROM MYTABLE |
pi() | Returns the value of π. |
power(base, exponent) | Returns base raised to the power of exponent. For example, power(10,2) returns 100. |
quarter(date) | Returns the yearly quarter for the given date where the 1st quarter = Jan 1-March 31, 2nd quarter = April 1-June 30, 3rd quarter = July 1-Sept30, 4th quarter = Oct 1-Dec 31 |
radians(degrees) | Returns the radians for the given degrees. |
rand(), rand(seed) | Returns a random number between 0 and 1. |
repeat(string, count) | Returns the string repeated the given number of times. SELECT REPEAT('Hello',2) returns 'HelloHello'.
|
round(value, precision) | Rounds the value to the specified number of decimal places. ROUND(43.3432,2) returns 43.34 |
rtrim(string) | Trims white space characters from the right side of the string. For example: RTRIM('Trim String ') |
second(time) | Returns the second value for the given time. |
sign(value) | Returns the sign, positive or negative, for the given value. |
sin(value) | Returnsthesineforthe given value. |
startswith(string, prefix) | Tests to see if the string starts with the specified prefix. For example, STARTSWITH('12345','2') returns FALSE. |
sqrt(value) | Returns the square root of the value. |
substring(string, start, end) | Returns a portion of the string as specified by the start and end locations. |
tan(value) |
Returns the tangent of the value. |
timestampadd(interval, number_to_add, timestamp) |
Adds an interval to the given timestamp value. The interval value must be surrounded by quotes. Possible values for interval: SQL_TSI_FRAC_SECOND Example: TIMESTAMPADD('SQL_TSI_QUARTER', 1, "Physical Exam".date) AS NextExam |
timestampdiff(interval, timestamp1, timestamp2) |
The interval must be surrounded by quotes. This differs from JDBC syntax. Note that PostgreSQL does not support the following intervals: SQL_TSI_FRAC_SECOND As a workaround, use the 'age' functions defined above. Example: TIMESTAMPDIFF('SQL_TSI_DAY', SpecimenEvent.StorageDate, SpecimenEvent.ShipDate) |
truncate(numeric value, precision) | Truncates the numeric value to the precision specified. This is an arithmetic truncation, not a string truncation. TRUNCATE(123.4567,1) returns 123.4 TRUNCATE(123.4567,2) returns 123.45 TRUNCATE(123.4567,-1) returns 120.0 |
ucase(string), upper(string) | Converts all characters to upper case. |
userid() | LabKey SQL extension function. Returns the userid, an integer, of the logged in user. |
username() | LabKey SQL extension function. Returns the current user display name. VARCHAR |
week(date) | Returns the week value (1-52) of the given date. |
year(date) | Return the year of the given date. Assuming the system date is March 4 2023, then YEAR(NOW()) return 2023. |
PostgreSQL Specific FunctionsLabKey SQL supports the following PostgreSQL functions. See the PostgreSQL docs for usage details.
|
MS SQL Server Specific FunctionsLabKey SQL supports the following SQL Server functions. See the SQL Server docs for usage details.
|
Syntax Item | Description |
Case Sensitivity | Schema names, table names, column names, SQL keywords, function names are case-insensitive in LabKey SQL. |
Comments | Comments that use the standard SQL syntax can be included in queries. '--' starts a line comment. Also, '/* */' can surround a comment block:
-- line comment 1 |
Identifiers | Identifiers in LabKey SQL may be quoted using double quotes. (Double quotes within an identifier are escaped with a second double quote.) SELECT "Physical Exam".* ... |
Lookups | Lookups columns are columns that see data in other tables. They are essentially foreign key columns that can be managed through an intuitive user interface. See Lookups for details on creating lookup columns. Lookups use a convenient syntax of the form "Table.ForeignKey.FieldFromForeignTable" to achieve what would normally require a JOIN in SQL. Example:
Issues.AssignedTo.DisplayName |
String Literals | String literals are quoted with single quotes ('). Within a single quoted string, a single quote is escaped with another single quote.
|
Date/Time Literals |
Date and Timestamp (Date&Time) literals can be specified using the JDBC escape syntax {ts '2001-02-03 04:05:06'} {d '2001-02-03'} |