JDB/Queries

Overview

A SQL query has the form:

select from where order

The from part is required, others are optional.

For example:

   Reads__locD '*,weight,p.city,s.city from spj where weight=12 and p.city = Paris order by qty'
+---+---+---+---+------+------+------+
|sid|pid|jid|qty|weight|p.city|s.city|
+---+---+---+---+------+------+------+
|S2 |P5 |J2 |100|12    |Paris |Paris |
|S5 |P5 |J7 |100|12    |Paris |Athens|
|S5 |P5 |J4 |400|12    |Paris |Athens|
|S5 |P5 |J5 |500|12    |Paris |Athens|
+---+---+---+---+------+------+------+

The query is executed in order:

1. from 1. where 1. select 1. order

SQL keywords from, order by etc must be in lower case.

Query Expressions

select

The select expression determines the columns. The expression may be simple or aggregate.

A simple select expression gives only the columns to be read, and is any comma-separated list of:

• column names
• table.column names
• * meaning all columns in the current table, except autoid, tranid
• ** meaning all columns readable from the current table, except autoid, tranid and any duplicates (i.e. not both a referencing foreign key and its referenced foreign key)
• *+ as ** but restricted to key columns only

If the list includes an asterisked expression, the columns from that expression are returned first.

Columns must be in the current table or a referenced table (i.e. reachable from the current table).

If the select expression is not given, then * is used.

An aggregate select expression gives the columns and aggregations to be performed on them. In this case, the asterisked expressions are not supported. See Aggregation for details.

from

The from expression determines the starting table, and has the form: from tablename.

where

The where expression determines the records to be read. It is a list of selection phrases joined by and/or, each optionally preceded by not. Operators supported are:

ge >=
le <=
ne <>
eq =
gt >
lt <
in
is
notin

The selection phrases are applied from left-to-right, except that parentheses can control the order of execution.

These include simple queries of column against value, and between two columns. For example, in the sandp database:

  Reads__locD '** from sp where s.city=Paris'
+---+-----+------+------+---+-----+-----+------+------+---+
|sid|sname|status|s.city|pid|pname|color|weight|p.city|qty|
+---+-----+------+------+---+-----+-----+------+------+---+
|S2 |Jones|10    |Paris |P1 |Nut  |Red  |12    |London|300|
|S2 |Jones|10    |Paris |P2 |Bolt |Green|17    |Paris |400|
|S3 |Blake|30    |Paris |P2 |Bolt |Green|17    |Paris |200|
+---+-----+------+------+---+-----+-----+------+------+---+

  Reads__locD  '** from sp where s.city=p.city'
+---+-----+------+------+---+-----+-----+------+------+---+
|sid|sname|status|s.city|pid|pname|color|weight|p.city|qty|
+---+-----+------+------+---+-----+-----+------+------+---+
|S1 |Smith|20    |London|P1 |Nut  |Red  |12    |London|300|
|S1 |Smith|20    |London|P4 |Screw|Red  |14    |London|200|
|S1 |Smith|20    |London|P6 |Cog  |Red  |19    |London|100|
|S2 |Jones|10    |Paris |P2 |Bolt |Green|17    |Paris |400|
|S3 |Blake|30    |Paris |P2 |Bolt |Green|17    |Paris |200|
|S4 |Clark|20    |London|P4 |Screw|Red  |14    |London|300|
+---+-----+------+------+---+-----+-----+------+------+---+

Parentheses can be used to group in or notin lists:

   Reads__locD '*,city from spj where qty gt 200 and city notin (Athens,London)'
+---+---+---+---+-----+
|sid|pid|jid|qty|city |
+---+---+---+---+-----+
|S2 |P3 |J1 |400|Paris|
|S2 |P3 |J6 |400|Oslo |
|S3 |P4 |J2 |500|Rome |
+---+---+---+---+-----+

order

The order expression sorts the result, as has the form: order by columnlist, where columnlist is a comma-separated list of column names in the result, each optionally followed by desc for descending.

The following example from sed has records sorted by decreasing sno, and by (ascending) ename within sno:

   Reads__locD 'ename,sno,r.name from e order by sno desc,ename'
+---------+---+------+
|ename    |sno|r.name|
+---------+---+------+
|Robinson |34 |Naples|
|Smith    |34 |Naples|
|Jasper   |33 |Rome  |
|Jones    |33 |Rome  |
|Steinberg|33 |Rome  |
|Rafferty |31 |Naples|
+---------+---+------+