ADQL syntax

ADQL (Astronomical Data Query Language) is based on SQL (Structured Query Language) which is a language to retrieve information from tables stored in a database.

(References: ADQL 2.0 and SQL-92)

A very concise introduction to ADQL is provided in the next sections.

1. Basic syntax

In ADQL/SQL you write queries to the database. A query is compound of the table columns you want to retrieve (the SELECT part), the table or tables that store the data (the FROM part) and the conditions to restrict the data you obtain (the WHERE part). E.g.

SELECT <columns> FROM <tables> WHERE <conditions>

So, if you want to obtain Investigation Name, Start Date and End Date of all items from the table investigation, you may write:

SELECT investigation_name, start_date, end_date FROM hreda.investigation

'investigation_name' is the column name of Investigation Name in investigation table.
'start_date' is the column name of Start Date in investigation table.
'end_date' is the column name of End Date in investigation table.
'hreda' is the database schema name where investigation table belongs to. It is a good practice to add schema names to tables to avoid name clashes.

Probably, you want to obtain also a short description, so you can modify the query as follows:

SELECT investigation_name, short_description, start_date, end_date FROM hreda.investigation

If you want to know all the column names associated to a table, you may use our TAP+ to obtain all the columns and descriptions of a table using the following syntax:

curl "https://hreda.esac.esa.int/hreda-sl-tap/tap/tables?tables=hreda.investigation"

Now, suppose you are interested in investigations performed in a specific date interval (e.g. start=2013-10-30, end=2016-12-31). So you want to restrict the results to that interval. In order to do that, you may add a filter:

SELECT investigation_name, start_date, end_date FROM hreda.investigation
   WHERE (start_date > '2013-10-20') AND (end_date < '2022-12-31')

(You may read the ADQL recommendation to obtain the list of functions that can be used).

2. Additional functions available

Apart from the standard ADQL functions, ESDC TAP+ service offers the next functions:

1. STDDEV(n): standard deviation function.
   
   
2. GREATEST(v1,v2[,v3,..,vn]): Greatest value among the given arguments.
   
   
3. LEAST(v1,v2[,v3,..,vn]): Least value among the given arguments.
   
   

4. ARRAY_ELEMENT(array_column, index1 [,index2....,indexN]): Returns the requested element inside the array. Indexes beginning in 1.

   Example. Given the array [4,5,6]

   SELECT array_element(array,2) : 5

   
   

5. ARRAY_NDIMS(array_column): Returns the number of dimensions of the array (integer).

   Example. Given the array [[1,2,3], [4,5,6]]

   SELECT array_ndims(array) : 2

   
   

6. ARRAY_DIMS(array_column): Returns a text representation of array's dimensions. Initial and end index for each dimension are given.

   Example. Given the array [[1,2,3], [4,5,6]]

   SELECT array_dims(array) : [1:2][1:3]

   
   

7. ARRAY_LENGTH(array_column, index): Returns the length of the requested array dimension (integer), 1 being the first index value.

   Example. Given the array [1,2,3]

   SELECT array_length(array,1) : 3

   
   

8. CARDINALITY(array_column): returns the total number of elements in the array (integer), or 0 if the array is empty.

   Example. Given the array [[1,2],[3,4]]

   SELECT cardinality(array) : 4

   
   

9. SIGN(numeric): sign of the argument (-1, 0, +1)

   Example.

   SELECT SIGN(-4) : -1