Alpha

These pages are built from main, turborel 0.0.0. Anything may change from one build to the next: SQL, API, protocol and files alike.

Retrieving Data

The examples are of the tables of Getting Started.

The query specification

A query says what to select, from which tables, of which rows:

SELECT NAME, SALARY FROM EMPLOYEES WHERE DEPT = 'ENG' ORDER BY SALARY DESC;
 NAME                           |      SALARY
--------------------------------+-------------
 Grace                          |     8100.00
 Ada                            |     7200.00
(2 rows)

SELECT names the columns of the result, or * for all; FROM the tables; WHERE the condition each row kept meets (Predicates). GROUP BY makes a row of each group, of set functions such as COUNT and AVG, and HAVING keeps the groups its condition is true of (Query Expressions).

Joins

A join pairs the rows of two tables: A JOIN B ON condition the pairs the condition is true of; LEFT, RIGHT and FULL joins also keep the rows of one or both that pair with none, the other's columns null.

SELECT D.NAME, COUNT(*) AS STAFF, AVG(E.SALARY) AS AVERAGE
  FROM DEPARTMENTS D JOIN EMPLOYEES E ON E.DEPT = D.CODE
 GROUP BY D.NAME ORDER BY D.NAME;
 NAME                           |                STAFF |              AVERAGE
--------------------------------+----------------------+----------------------
 Engineering                    |                    2 |            7650.0000
 Operations                     |                    1 |            5400.0000
(2 rows)

Subqueries

A query in parentheses may stand for a value, its one row's, or for a set of rows, with IN, EXISTS, ANY and ALL:

SELECT NAME FROM EMPLOYEES WHERE SALARY > (SELECT AVG(SALARY) FROM EMPLOYEES);
SELECT NAME FROM DEPARTMENTS D
 WHERE NOT EXISTS (SELECT * FROM EMPLOYEES E WHERE E.DEPT = D.CODE AND E.SALARY > 6000);
SELECT NAME FROM EMPLOYEES WHERE DEPT IN (SELECT CODE FROM DEPARTMENTS WHERE NAME LIKE 'Eng%') ORDER BY NAME;
 NAME
--------------------------------
 Ada
 Grace
(2 rows)
 NAME
--------------------------------
 Operations
(1 row)
 NAME
--------------------------------
 Ada
 Grace
(2 rows)

The second subquery reads D.CODE, a column of the query around it: it is found again for each department.

Set operations

UNION, EXCEPT and INTERSECT combine the rows of two queries of the same columns:

SELECT CODE FROM DEPARTMENTS EXCEPT SELECT DEPT FROM EMPLOYEES WHERE SALARY < 6000;

-----
 ENG
(1 row)

Ordering

Without ORDER BY, rows come in no order to count on. ORDER BY sorts by columns of the result, each ascending or DESC, nulls last ascending:

SELECT DEPT, NAME, SALARY FROM EMPLOYEES ORDER BY DEPT, SALARY DESC;
 DEPT | NAME                           |      SALARY
------+--------------------------------+-------------
 ENG  | Grace                          |     8100.00
 ENG  | Ada                            |     7200.00
 OPS  | Edsger                         |     5400.00
(3 rows)

A sort key is a column the query selects: ORDER BY DEPT of a query that does not select DEPT is refused.