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 qabc03:

SELECT * FROM quiescentgalaxies.qabc03

Obtaining all sources identifier from the table qabc03:

SELECT galaxy_id FROM quiescentgalaxies.qabc03

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 sources_number:

SELECT COUNT(galaxy_id) AS sources_number FROM quiescentgalaxies.qabc03

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 qabc03:

SELECT TOP 100 * FROM quiescentgalaxies.qabc03

4.- Ordering records

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

Obtaining the 100 images with higher Stellar mass from the table qabc03:

SELECT TOP 100 * FROM quiescentgalaxies.qabc03 ORDER BY mstar 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 100 records of the query in the table qabc03:

SELECT TOP 100 * FROM quiescentgalaxies.qabc03 OFFSET 100

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).

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.

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.

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 Stellar mass is less than 1.5 solMass:

SELECT * FROM quiescentgalaxies.qabc03 WHERE mstar < 1.5

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 with the identifiers 81421100765, 81421101074 and 81421101630:

SELECT * FROM quiescentgalaxies.qabc03 WHERE galaxy_id IN (81421100765, 81421101074, 81421101630)

8.3.- BETWEEN operator

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

Obtaining the objects whose Stellar mass is between 1.5 and 1.6 solMass:

SELECT * FROM quiescentgalaxies.qabc03 WHERE mstar BETWEEN 1.5 AND 1.6

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.

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 Stellar mass is less than 16 1.6 solMass and photoredshift less than 0.3:

SELECT * FROM quiescentgalaxies.qabc03 WHERE mstar < 1.6 AND z < 0.3

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 Stellar mass is less than 16 1.6 solMass or photoredshift is less than 0.3:

SELECT * FROM quiescentgalaxies.qabc03 WHERE mstar < 1.6 OR z < 0.3

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:

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 Stellar mass of all the objects:

SELECT AVG(mstar) FROM quiescentgalaxies.qabc03

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 sources:

SELECT COUNT(galaxy_id) FROM quiescentgalaxies.qabc03

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 stellar mass:

SELECT MAX(mstar) FROM quiescentgalaxies.qabc03

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 stellar mass:

SELECT MIN(mstar) FROM quiescentgalaxies.qabc03

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 stellar mass (rounding the stellar mass):

SELECT ROUND(mstar, 0) AS MAG, COUNT(*) FROM quiescentgalaxies.qabc03 GROUP BY mstar

11.- Join tables according to a identifier

Obtaining all objects using both methods qabc03 and qabasti:

SELECT qabc03.galaxy_id as galaxy_id, qabc03.*, qabasti.* FROM quiescentgalaxies.qabc03 AS qabc03, quiescentgalaxies.qabasti AS qabasti WHERE qabc03.galaxy_id = qabasti.galaxy_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 all objects in both methods qabc03 and qabasti:

SELECT GALAXY_ID, ALPHA_J2000, DELTA_J2000 FROM quiescentgalaxies.qabc03 UNION SELECT GALAXY_ID, ALPHA_J2000, DELTA_J2000 FROM quiescentgalaxies.qabasti

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.

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.

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.

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