Help Tap VizieR

ADQL cheat sheet

Outline

1 - Introduction

The ADQL or Astronomical Data Query Language is used by the VO to represent astronomical queries send to IVOA services. It is a SQL-like searching language improved with geometrical functions.

This cheat sheet gathers principal ADQL features required to understand generated queries or to create your own queries. All information about ADQL are available at this IVOA Document.

2 - SQL minimal queries

3 - Limit the number of records in output

Limit the number of records to display using the TOP instruction:

example :
SELECT TOP 100 *
FROM "I/261/fonac"

Get the first 100 records of the query in the table "I/261/fonac.

4 - Ordering records

Sort records in ascending (ASC) or descending (DESC) with using the ORDER instruction.

example :
SELECT TOP 100 *
FROM "I/261/fonac" ORDER BY Bmag ASC

Get the 100 brightness records of the table I/261/fonac.

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

5.1 - Comparison operators

The different operators of logical comparisons : = or > or < or >= or <= or <> ...

example :
 SELECT TOP 100 "I/261/fonac".RAJ2000, "I/261/fonac".DEJ2000, "I/261/fonac".pmRA, "I/261/fonac".pmDE, "I/261/fonac".Bmag
 FROM "I/261/fonac"
 WHERE "I/261/fonac".Bmag<15 AND "I/261/fonac".Bmag>14
 

Get positions, proper motions and B magnitude of the table I/261/fonac when the B magnitude is greater than 14 and less than 15 (the output is limited to 100 records).

5.2 - IN operator

The IN operator can determine whether a value is within a given set, regardless of the type specified reference values (alpha, numeric, date ...). You can reverse the operation of the IN operator by adding to the NOT operator.

example :
SELECT "B/chandra/chandra".Target,  "B/chandra/chandra".ObsID,  "B/chandra/chandra".RAJ2000,  "B/chandra/chandra".DEJ2000,  "B/chandra/chandra".Status
FROM "B/chandra/chandra"
WHERE "B/chandra/chandra".status NOT IN ('archived','scheduled')

Get target, obsID, RAJ2000, DEJ2000 and status if status does not equal to 'archive' neither to 'scheduled' in the table chandra.

5.3 - BETWEEN operator

The BETWEEN operator can determine whether a value is within a given interval, regardless of the type specified reference values (alpha, numeric, date ...).

example :
SELECT "V/134/arxa".RAJ2000, "V/134/arxa".DEJ2000, "V/134/arxa".Rmag
FROM "V/134/arxa"
WHERE "V/134/arxa".Rmag between 3 and 6

Get positions and R magnitude (Rmag) whose the R magnitude is between 3 and 6 in the table V/134/arxa.

5.4 - LIKE operator

The LIKE operator allows for a partial comparison. It is mainly used with columns with data type alpha. It uses wild cards % and _ ('percent' and 'underscore'). The wild card % replaces any string of characters, including the empty string. The underscore replaces exactly one character.

example :
SELECT "B/chandra/chandra".Target,  "B/chandra/chandra".RAJ2000,  "B/chandra/chandra".DEJ2000,  "B/chandra/chandra".Category
FROM "B/chandra/chandra"
WHERE "B/chandra/chandra".Category LIKE '%BINARIES%'

Get positions and category whose category contains the word 'BINARIES' in the table chandra.

6 - Computed columns in ADQL

6.1 - Mathematical operations :

Compute columns using mathematical operations : +, -, *, /:

example :
 SELECT "RA(ICRS)", "DE(ICRS)", Btmag, VTmag, BTmag - VTmag AS BV
 FROM "I/259/tyc2"
 WHERE Btmag-VTmag>0 AND BTmag-VTMag<0.2

Return values from the tycho catalog with computing the color (BTmag-VTmag) and with adding a constraint on it.

Note: the table "II/259/tyc2" is optional in the SELECT and WHERE part of an ADQL query when a unique table is in action (FROM part).
Note: the columns "RA(ICRS)" and "DE(ICRS)" must be quoted because they contain a special caracter ('()[].').

6.2 - Arithmetical functions :

Arithmetic functions apply a mathematical function on the expression of a data line.

6.3 - SQL aggregate functions :

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

6.4 - The trigonometrics functions

7 - 2D-geometrical functions

7.1 - Geometries available

ADQL provides a set of 2D-functions and geometries (or "REGION") :

We describe below the ADQL REGIONS:

7.2 - ADQL geometrical functions :


8 - Working with several tables

ADQL can join tables according to a identifer or by positions.

You can specify several tables in the WHERE part of an ADQL query and then taking advantage of the columns of differents tables. But, regard that by default the the join between 2 tables is a cartesian product (in which each record of a table is gather with all records of a second table). The WHERE part is a way to organize the join.

Join tables according to a identifier

example :
SELECT TOP 100 *
FROM "J/ApJS/112/557/table1","III/170B/ps_class"
WHERE "J/ApJS/112/557/table1".IRAS="III/170B/ps_class".IRAS

Join by identifier the tables J/ApJS/112/557/table1 and III/170B/ps_class with the column IRAS

Join tables according to their positions

example :
SELECT *
FROM "II/295/SSTGC","II/293/glimpse"
WHERE 1=CONTAINS(POINT('ICRS',"II/295/SSTGC".RAJ2000,"II/295/SSTGC".DEJ2000), BOX('GALACTIC', 0, 0, 30/60., 10/60.)) 
  AND 1=CONTAINS(POINT('ICRS',"II/295/SSTGC".RAJ2000,"II/295/SSTGC".DEJ2000), CIRCLE('ICRS',"II/293/glimpse".RAJ2000,"II/293/glimpse".DEJ2000, 2/3600.))

Crossmatch between 2MASS and Glimpse in the BOX centered with the galaxy center.

Note: Improve your ADQL Crossmatch queries with setting the coordinates of the smallest resource in the first parameters of the CONTAINS function (in the example II/295 is smaller than II/293/glimpse)
Try the "Explain" capability at /TAPVizieR/.

9 - Query Optimisation

(for advanced user only)

The database QueryPlan is the process responsible to define the faster way to execute SQL query.
TAPVizieR provides a method to change the queryPlan:

position_priorityset the index priority on position functions (default is true)
enable_seqscanallows the sequential search (default is false)
enable_nestloopin a join, allows a sequential search from a table to the other (default is true)
enable_hasjoinin a join, allows QueryPlan to create a hashtable on the fly (default is true)
enable_sortin a join, allows the QueryPlan to make a sort (default is true)
enable_materialmaterialize records into memory - not compatible with index scan (default is true)

The QueryPlan option can be added in the ADQL beginning comment:

example :
	--set position_priority=false
	SELECT TOP 100 * FROM "II/349/ps1" 
	WHERE 1=CONTAINS(POINT('ICRS', RAJ2000, DEJ2000), CIRCLE('ICRS', 45, 0, 20.))
	  AND gmag>15
	

Note: use the "Explain" button available in theTAPVizieR interface to see the QueryPlan