Introduction

The Astronomical Data Query Language (ADQL) is the language used by the International Virtual Observatory Alliance (IVOA) to represent astronomy queries posted to VO services.

ADQL is based on the Structured Query Language (SQL), especially on SQL 92. Only SELECT sentences are allowed, to express the maximum rows to return SQL 92 specifies the keyword TOP followed by a number after the initial SELECT keyword.

ADQL predefines a list of mathematics and geometric functions. Geometric functions that accepts figures as a string uses the string format specified at the TAP protocol in section '6 Use of STC-S in TAP (informative)'. For the coordinate system only Spherical coordinates in the frames FK5, GALACTIC, and ICRS are supported. Note: unlike ADQL specifies, empty string assumes ICRS as the default coordinate system.

1.- SQL minimal queries

Obtaining all records and all the columns from the table TileImage:

SELECT * FROM jplus.TileImage

Obtaining all image names from the table TileImage:

SELECT name FROM jplus.TileImage

2.- Using Aliases

Aliases are used to give a database table, or a column in a table, a temporary name. Basically aliases are created to make column names more readable.

Renaming a column as tiles_number:

SELECT COUNT(tile_id) AS tiles_number FROM jplus.TileImage

3.- Limit the number of records in output

Use the TOP instruction to limit the number of records to display.

Obtaining the first 100 records of the query in the table TileImage:

SELECT TOP 100 * FROM jplus.TileImage

4.- Ordering records

Use the ORDER BY instruction to sort records in ascending (ASC) or descending (DESC).

Obtaining the 100 images with better FWHM from the table TileImage:

SELECT TOP 100 * FROM jplus.TileImage ORDER BY FWHM_Mean ASC

5.- Specify the number of records to skip before starting to return records

Use the OFFSET instruction to specify the number of records to skip before starting to return records from the query.

If the total number of records is less than the value specified by the OFFSET clause, then the result set is empty. If a query contains both an ORDER BY clause and an OFFSET clause, then the ORDER BY is applied before the specified number of records are dropped by the OFFSET clause.

This instruction can be very useful in big queries where the number of expected output records is bigger than the TAP maximum records limit or the limit indicated in the TOP clause. In that case, you can use this instruction to obtain your data in chucks by executing your query n times only changing the value of OFFSET until you get a result with a number of records less than the limit and then working with all the outputs files obtained.

Obtaining the next 100000 records of the query in the table MagABDualObj:

SELECT TOP 100000 * FROM jplus.MagABDualObj ORDER BY tile_id, number OFFSET 100000

6.- Using arrays elements

As an extension to ADQL it is possible to use square brackets to access array elements of columns defined as arrays. First element of the array is at position 1. This allow, for example, store together all the fluxes or magnitudes measured in different filters for a position in the sky (see also section 6: Using enumerations).

Obtaining the value of the magnitude auto in the filter rSDSS of 100 objects:

SELECT TOP 100 flux_auto[1] FROM jplus.FNuDualObj

Is it possible also to access to a subset of an array (array slice) using square brackes and indicating inside the lower-bound and upper-bound (both included) separated by a colon.

Obtaining the values of the magnitude auto from filter J0378 to filter J0430 (both included) of 100 objects:

SELECT TOP 100 flux_auto[6:9] FROM jplus.FNuDualObj

Basic math: + - * /, can be done between numerical arrays, and between numerical arrays and scalars.

There are special functions to deal with arrays (see Functions section) and, among them, there is a special one arr_map(expr_over_x, arr) that computes a new array by binding each element of arr to x in turn and then computing expr_over_x (ADQL numeric_value_expression which can use that can use column references as usual, except the reserved name x).

7.- Using enumerations

An extension to ADQL is the definition of Enumerations, it is a form of assign a set of names to a range of numbers. Writing a enumeration in a query is done by writing the enumeration name, the symbol :: and the item name of the enumeration that we want to use. That is equivalent to write the associated number of the item and it is useful when accessing an concrete element in a array column or using it as a constant.

Obtaining the value of the magnitude auto in the filter rSDSS of 100 objects:

SELECT TOP 100 flux_auto[jplus::rSDSS] FROM jplus.FNuDualObj

8.- Filtering results

Use constraints to filter records according to logical expressions. In an ADQL query, the constraints are gathered in the WHERE part of the query.

8.1.- Comparison operators

Use the different operators = (equal), <> (not equal), < (less than), > (greater than), <= (less and equal than) and >= (greater and equal than) to compare values.

Obtaining the objects whose magnitude auto in filter rSDSS is less than 16 magnitudes:

SELECT * FROM jplus.MagABDualObj WHERE MAG_AUTO[jplus::rSDSS] < 16

8.2.- IN operator

Use the IN operator to determine whether a value is within a given set. You can reverse the operation of the IN operator by adding to the NOT operator.

Obtaining the objects in the images with tile identifier 2034, 2035 and 2036:

SELECT * FROM jplus.MagABDualObj WHERE tile_id IN (2034, 2035, 2036)

8.3.- BETWEEN operator

Use the BETWEEN operator to determine whether a value is within a given interval.

Obtaining the objects whose magnitude auto in filter rSDSS is between 15 and 16 magnitudes:

SELECT * FROM jplus.MagABDualObj WHERE MAG_AUTO[jplus::rSDSS] BETWEEN 15 AND 16

8.4.- LIKE and ILIKE operators

Use the LIKE operator to execute partial comparisons. The wild card % replaces any string of characters, including the empty string. The underscore replaces exactly one character. You can reverse the operation of the LIKE operator by adding to the NOT operator. Use ILIKE for case insensitive comparations.

Obtaining the images of the pointing 01494:

SELECT * FROM jplus.TileImage WHERE name LIKE '1000001-JPLUS-01494-%'

8.5.- Using several conditions

Use AND operator to obtain records if both the first condition AND the second condition are true.

Obtaining the objects whose magnitude auto in filter rSDSS is less than 16 magnitudes and in filter gSDSS less than 17 magnitudes:

SELECT * FROM jplus.MagABDualObj WHERE MAG_AUTO[jplus::rSDSS] < 16 AND MAG_AUTO[jplus::gSDSS] < 17

Use OR operator to obtain records if the first condition is true, the second condition is true or both are true.

Obtaining the objects whose magnitude auto in filter rSDSS is less than 16 magnitudes or in filter gSDSS is less than 17 magnitudes:

SELECT * FROM jplus.MagABDualObj WHERE MAG_AUTO[jplus::rSDSS] < 16 OR MAG_AUTO[jplus::gSDSS] < 17

9.- Mathematical operations

Use mathematical operations: +, -, * and / to compute columns. Obtaining the objects with a difference of more than 1 magnitude between filters rSDSS and gSDSS in magnitude auto:

Obtaining the objects with a difference of more than 1 magnitude between filters rSDSS and gSDSS in magnitude auto:

SELECT * FROM jplus.MagABDualObj WHERE MAG_AUTO[jplus::gSDSS] - MAG_AUTO[jplus::rSDSS] > 1

10.- SQL aggregate functions

SQL aggregate functions return a single value, calculated from values in a column.

10.1.- AVG function

Use AVG(column_name) function to obtain the average value in a column for a group of data lines. This function applies only to numeric data.

Obtaining the average value of the FWHM of all images:

SELECT AVG(FWHM_Mean) FROM jplus.TileImage

10.2.- COUNT function

Use COUNT(column_name) function to obtain a count of rows from a reference column values if it is not NULL.

Obtaining the total number of images:

SELECT COUNT(tile_id) FROM jplus.TileImage

10.3.- SUM function

Use SUM(column_name) function to obtain the sum of values in a column for a group of data lines. This function applies only to numeric data.

10.4.- MAX function

Use MAX(column_name) function to obtain the largest value of a column for a group of data lines.

Obtaining the object with a largest magnitude auto in rSDSS filter:

SELECT MAX(MAG_AUTO[jplus::rSDSS]) FROM jplus.MagABDualObj

10.5.- MIN function

Use MIN(column_name) function to obtain the smallest value of a column for a group of data lines.

Obtaining the object with a smallest magnitude auto in rSDSS filter:

SELECT MIN(MAG_AUTO[jplus::rSDSS]) FROM jplus.MagABDualObj

10.6.- GROUP BY statement

Use GROUP BY statement in conjunction with the aggregate functions to group the result-set by one or more columns.

Obtaining the number of objects per magnitude (rounding the magnitude):

SELECT ROUND(MAG_AUTO[jplus::rSDSS], 0) AS MAG, COUNT(*) FROM jplus.MagABDualObj GROUP BY MAG

11.- Join tables according to a identifier

Obtaining all objects data with the zero point and image exposure time of 100 objects:

SELECT TOP 100 objs.*, imgs.zpt, imgs.texposed FROM jplus.MagABDualObj AS objs, jplus.TileImage AS imgs WHERE objs.tile_id = imgs.tile_id

12.- Set Operators

You can combine the results of two queries with the same number of columns and compatible types for each column. The operators are UNION, EXCEPT and INTERSECT.

The UNION operator combines the results of two queries, accepting rows from both the first and second set of results, removing duplicate rows unless UNION ALL is used.

Obtaining objects in the data release with data in GAIA or Panstarrs:

SELECT tile_id, number FROM jplus.xmatch_gaia_dr2 UNION SELECT tile_id, number FROM jplus.xmatch_panstarrs_dr1

The EXCEPT operator combines the results of two queries, accepting rows that are in the first set of results but are not in the second, removing duplicate rows unless EXCEPT ALL is used.

Obtaining objects in the data release without data in GAIA:

SELECT tile_id, number FROM jplus.FNuDualObj EXCEPT SELECT tile_id, number FROM jplus.xmatch_gaia_dr2

The INTERSECT operator combines the results of two queries, accepting rows that are strictly in both the first and second set of results, removing duplicate rows unless INTERSECT ALL is used.

Obtaining objects in the data release with data in GAIA and Panstarrs:

SELECT tile_id, number FROM jplus.xmatch_gaia_dr2 INTERSECT SELECT tile_id, number FROM jplus.xmatch_panstarrs_dr1

13.- Common Table Expressions

The WITH operator creates a temporary named result set that can be referred to elsewhere in the main query. Using a common table expression can make complex queries easier to understand by factoring subqueries out of the main SQL statement.

Obtaining images with wide filter:

WITH wide_filter AS (SELECT filter_id FROM jplus.Filter WHERE width > 999) SELECT t.* FROM jplus.TileImage t JOIN wide_filter USING (filter_id)

14.- Type operations - CAST

The CAST operator returns the value of the first argument converted into the datatype specified by the second argument. The allowed conversion types are: INTEGER, SMALLINT, BIGINT, REAL, DOUBLE PRECISION, CHAR(number), VARCHAR(number) and TIMESTAMP. Basic usage: CAST('2012-01-15 9:00' AS TIMESTAMP)

15.- Other functions

Other functions like arithmetical functions, trigonometric functions, geometrical functions, conversion functions, ... are also available. Please, see Functions section to know more about them.

Note: at the moment the geometric functions are partially supported, 'contains', 'distance' and 'area' are only implemented for basic figures. This is a work in progress!.

16.- Bitwise operators

The following bitwise operators are available to be used with integer numbers:

• & Bit 'and'. Example: flag & 2048
• | Bit 'or'. Example: flag | 2048
• # Bit 'xor'. Example: flag # 2048
• ~ Bit 'not'. Example: ~flag

17.- More examples

SELECT * FROM jplus.MagABSingleObj WHERE TILE_ID = 2035

SELECT * FROM jplus.MagABSingleObj WHERE TILE_ID = 2035 AND number = 12

SELECT i.zpt, m.* FROM jplus.FNuSingleObj m, jplus.TileImage i 
WHERE i.tile_id = m.tile_id AND i.name = '1000001-JPLUS-02623-v1_zSDSS_swp' AND (FLAGS = 0 OR FLAGS = 2048)

SELECT * FROM jplus.MagABDualObj WHERE TILE_ID = 2035 AND MAG_AUTO[jplus::gSDSS] - MAG_AUTO[jplus::rSDSS] > 1

SELECT ROUND(MAG_AUTO[jplus::rSDSS], 0) AS MAG, COUNT(*) FROM jplus.MagABDualObj GROUP BY MAG ORDER BY MAG

SELECT TOP 50 * FROM jplus.TileImage WHERE ra > 116.0 AND ra < 116.5 

SELECT * FROM jplus.TileImage 
WHERE filter_id = jplus::rSDSS

SELECT m.alpha_j2000, m.delta_j2000, m.flux_auto, m.class_star, i.name, i.tile_id
FROM jplus.FNuDualObj m, jplus.TileImage i
WHERE m.tile_id = i.tile_id AND CONTAINS(POINT('', m.alpha_j2000, m.delta_j2000), CIRCLE('', 7.8574, 5.67881, .005)) = 1