Building SQL queries using the Query Builder

Building a query is a matter of adding the tables you need, joining them, ticking the columns you want, and refining the result in the Columns pane.

The basic workflow:

1. Go to the SQL Builder page.
2. Drag objects from the Object List into the Query Building Area.
3. Create joins by dragging a field from one object onto the matching field of another, then set the join's properties from its context menu.
4. Choose the fields for your query by ticking the checkbox next to each field name.
5. Use the Columns pane to set aliases, sorting, grouping, aggregate functions and selection criteria.
6. Press F4 (or Build SQL from any of the Query Builder's context menus) to generate the query on the SQL page.
7. Press F6 (or Build and Execute SQL) to generate and run it, showing the result set on the SQL page.

Editing object properties

Right-click an object in the Query Building Area for its context menu. Besides Check all and Uncheck all, which tick or clear every field at once, it offers Remove, Autofit Object Height, and Properties….

Properties… opens the Datasource Properties dialog, where you can give the object an alias. The alias is what the generated SQL uses to refer to it, so a short one makes a long query much easier to read.

Joining tables

To join two objects, drag the field you want to link from one object onto the corresponding field of the other. A line appears between the linked fields to show the join, with a marker at each end for that side's cardinality.

The default join is an INNER JOIN — only rows that match on both sides appear in the result. Right-click the link line for its own menu.

Select all rows from… is the quickest way to change the join type: turning it on for the object on the left gives you a LEFT OUTER JOIN, for the one on the right a RIGHT OUTER JOIN, and for both a FULL OUTER JOIN. Remove deletes the join.

Properties… opens the Link Properties dialog, which offers the same two options as checkboxes and, below them, the join condition itself. Edit the Join expression when you need a condition that dragging cannot express — anything other than a plain equality between two fields.

The Columns pane

Everything about the query's output columns is set in the grid below the canvas. It has one row per output column, and these columns of its own:

Column	What it does
Output	Whether the expression appears in the SELECT list
Expression	The field or expression itself
Aggregate	An aggregate function to wrap it in
Alias	The name the column is given with AS
Sort Type	Ascending or Descending
Sort Order	Which sorted column takes priority
Grouping	Whether to group by this column
Criteria, Or…	Conditions the rows must satisfy

The quickest way to add a field is to tick the checkbox to the left of its name in the Query Building Area; to include all of an object's fields, tick the one next to its asterisk (*). You can also add a field by choosing it from the drop-down in the Expression column, or by typing any valid expression there.

To remove a field, untick its checkbox in the Query Building Area, or untick Output. Right-click the narrow grey column down the left of the grid to reorder or delete rows.

Sorting

Sort Type sets the direction — Ascending or Descending — and Sort Order the priority when more than one column is sorted. Clear a column's Sort Type to stop sorting by it.

Criteria

Write the condition in the Criteria column without repeating the expression itself. To produce

WHERE (field >= 10) AND (field <= 20)

enter this:

>= 10 AND <= 20

Conditions on different rows are combined with AND. The Or… columns to the right hold alternatives: conditions in the same Or… column are AND-ed together, and each Or… column is OR-ed with the others.

Grouping and aggregate functions

Tick Grouping for every column you want to group on; the header checkbox ticks them all at once. Columns that are not grouped need an aggregate function, chosen from the Aggregate drop-down — Count, Sum, Max and the rest, each with a Distinct variant.

Once a query groups, a criterion on a grouped column belongs in HAVING rather than WHERE, and the Query Builder puts it there for you.

Sub-queries

A sub-query can stand in for an expression or a criterion. Click into the Expression or Criteria cell to open its editor, right-click, and choose Insert Sub-Query.

The cell then reads (Select *), and a new tab appears beside Main where the sub-query is built visually exactly like the main query. Typing (Select) in the Expression column, or a condition such as = (Select) or In (Select) in the Criteria column, does the same thing.

Derived tables

A derived table is a sub-query used in the FROM clause. Right-click an empty part of the Query Building Area and choose Add Derived Table.

A new object appears in the canvas to represent it, together with a tab for editing it. Join it and tick its fields just like a table.

Unions

Union sub-queries are managed from the Union submenu of the Query Building Area's context menu, and from the small panel of Q buttons in the canvas's top-right corner.

• New Union Sub-Query adds another sub-query to the union.
• Copy Union Sub-Query adds one that starts as a copy of the current one.
• Enclose In Brackets wraps a sub-query in brackets, so the operators around it apply in the order you want.
• Remove deletes a sub-query, or a pair of brackets.

Each sub-query in the union has a Q button in the panel; click one to switch to it. Right-click a button for the same commands plus Move Left and Move Right, which change the sub-query's position in the union — the left-most is the first in the generated SQL.

Between two Q buttons sits the uniting operator. Click it to choose between UNION, which discards duplicate rows, and UNION ALL, which keeps them.

See also: The Query Builder layout · Generating views using the Query Builder