Building queries

 Top Next

Active Query Builder is a visual query builder component that allows you to build complex SQL queries via an intuitive visual query building interface.

To work with Active Query Builder, you need basic knowledge of SQL concepts. Active Query Builder will help you to write correct SQL code hiding technical details, but only understanding of the SQL principles will make it possible to achieve desired results.

This Guide will help you to learn basic principles of visual building of SQL queries to achieve best results while working with Active Query Builder.

Click here to Get Started with Active Query Builder.


Getting started

 Top Previous Next

This is how Active Query Builder looks when started.

The main window can be divided into the following parts:

  • The Query Building Area is the main area where the visual representation of query will be displayed. This area allows you to define source database objects and derived tables, define links between them and configure properties of tables and links.
  • The Columns Pane is located below the query building area. It is used to perform all necessary operations with query output columns and expressions. Here you can define field aliases, sorting and grouping, and define criteria.
  • The Query Tree Pane is located at the left. Here you may browse your query and quickly locate any part of it.
  • The page control above the query building area will allow you to switch between the main query and sub-queries.
  • The small area in the corner of the query building area with the "Q" letter is the union sub-query handling control. Here you may add new union sub-queries and perform all necessary operations with them using popup menu.

Working with the query builder you can see only one query or sub-query at the moment. Each single query represented at the Query Building Area and the Column pane.

Learn more:

  • how to build a single query
  • how to build a query with sub-queries


Building a single query

Active Query Builder allows to build complex SQL queries visually:

- Build queries with different join types

- Build queries with sorting, grouping and complex criteria.

- Build queries with different server-specific options.

Learn more about:

  • Adding an object to the query
  • Editing object properties
  • Joining tables
  • Selecting output fields
  • Sorting output fields
  • Defining criteria
  • Grouping output fields
  • Defining SQL query properties


Adding objects to the query

There are two ways of adding database objects to the query.

First, you may use the right-hand tree that displays database objects in a tree-like form. You may double-click on objects or drag them to the query building area.

To find the object you need, you may simply type the first letters of it's name or scroll down the tree. The object within the tree may be grouped by Database, Schema or Type.

To remove an object from the query, select it and press the Del key or simply click the Close button in the object header.

Second, you may right click the query building area and select the Add Object item from the context popup menu. The Add New Object window will appear.

Add New Object window

Add New Object window

The Add New Object window allows you to add as many objects as you wish at once. The objects are grouped by type within four tabs: Tables, Views,Procedures (Functions) and Synonyms. You may select one or several objects by holding the Ctrl key and then press the Add Object button to add these objects to the query. You may repeat this operation several times. After you finish adding objects, press the Close button to hide this window.

For those database servers that have schemas or allow selection of objects from different databases, you may filter objects by database or schema name by selecting the necessary schema or database from the combo box at the top of the window.


Setting object aliases

To set alias for an object or derived table in the query, you may right click at the object and select the Properties item from context popup menu or you may double-click at the object header. The Datasource Properties dialog will appear.

Editing object properties

Editing object properties

Datasource Properties dialog

Datasource Properties dialog

The Datasource Properties dialog may contain other server-specific datasource options, but the Alias property is the same for all database servers.


Joining tables

Adding and removing joins

When two objects referenced with a foreign key relationship are added to the query, they become joined automatically with INNER JOIN. For those servers that have no support of JOIN clause, Active Query Builder adds condition to WHERE part of the query.

To create a link between two objects (i.e. join them) manually, you should select the field by which you want to link the object with another and drag it to the corresponding field of the other object. After you finish dragging, a line connecting the linked fields will appear. Key cardinality symbols are placed at the ends of link when corresponding relationship exists in the database.

Joining tables. Step 1

Joining tables. Step 1

Joining tables. Step 2

Joining tables. Step 2

To remove a link between objects, right-click the link line and select the Remove item from the context popup menu or simply press Delete key.

Changing join type

The join type created by default is INNER JOIN, i.e. only matching records of both tables will be included in the resulting dataset. To change joint type, you may right click the link and choose the join type from "Change type" sub-menu of context popup menu.

To define join type and other link properties you may right click the link and select the Properties item from the context popup menu or double-click it to open the Link Properties dialog.

Joining tables. Step 3

Joining tables. Step 3

Link Properties dialog

Link Properties dialog


Selecting output fields

Adding and removing fields and expressions

To add a field to the list of query output fields you may check the box at the left of a field name in the datasource field list at the Query Building Area. To include all the fields of the specific object you may check the box at the left of the asterisk item in the datasource field list. Also you may drag fields from the Query Building Area to the Columns pane to get the same result.

Another way to add a field is to select a field name from the drop-down list of the Expression column in the Columns Pane. And of course you may type any valid expression in the Expression column in the Columns Pane. To add an empty line to the Columns pane, press the Alt(Ctrl*)+Insert key.

Selecting output fields

Selecting output fields

To remove a field from the Columns Pane you should uncheck the checkbox at the left of the field name in the Query Building Area or press the Alt(Ctrl*)+Delete key in the Columns Pane.

To move a line in the Columns Pane up and down, you may press the Alt(Ctrl*)+Up/Down keys.

Also you may add, remove and re-order lines at the Columns Pane using the context popup menu.

(*) The Ctrl key is used instead of Alt key for shortcuts in the VCL and ActiveX versions of Active Query Builder.

Working with expressions in the Columns Pane

The Output column determines presence of expression in the SELECT list of the query.

The Alias column allows you to set aliases for your expressions. Aliases become headings of columns in result dataset.

Defining field aliases

Defining field aliases

The Asterisk Item

Even if you don't select any fields from query datasources, an asterisk item will be added to the select list of result query ("Select * From ..."). This is made because a SELECT query without any columns will produce an error for most of the database servers, thus it's useless for end-users.

By selecting any fields or by adding any output expressions to the query, an asterisk item will be removed from result query (you may add it manually at any time). The asterisk items placed first at the datasource field's list are intended to add all fields from specific datasource, not from all of the datasources ("Select datasource.* From ..."). These asterisk items are not affect the rest of the datasource field check boxes, but act independently. If you want to check all the datasource field check boxes, not the asterisk item, use appropriate items of the datasource context popup menu.

See also:

  • Sorting output fields
  • Defining criteria
  • Grouping output fields


Sorting a dataset

To define sorting of result dataset, you may use the Sort Type and Sort Order columns of the Columns Pane.

The Sort Type column allows you to specify the way the fields will be sorted - in the Ascending or Descending order.

The Sort Order column allows you to setup the order in which fields will be sorted, if more than one field will be sorted.

To disable sorting by some field you should clear the Sort Type column for this field.

Defining dataset sorting

Defining dataset sorting


Defining criteria

To define criteria, you may use the Criteria and all of the Or columns of the Columns Pane.

In these cells you should write conditions omitting the expression itself. For example, to get the following criteria in your query:

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

you should type ">= 10 AND <= 20" in Criteria cell of a Field1 expression.

Criteria placed in the Or columns will be grouped by columns using the AND operator and then concatenated in the WHERE (or HAVING) clause using the OR operator. For example, the this visual representation will produce the following SQL statement. Please note that criteria for Field1 is placed both to the Criteria and Or columns.

Defining criteria

WHERE (field1 = 10) AND ((field2 < 0) OR (field2 > 10))

Some expressions may be of Boolean type, for example the EXISTS clause. In this case you should type "= True" in Criteria column of such expressions or "= False" if you want to place NOT operator before the expression.

BTW, the most common practice to learn how to build some query is to write it once by hand and see how it will be parsed and represented visually.


Grouping output fields

To build a query with grouping, you mark expressions for grouping with the Grouping checkbox.

A query with grouping must have only grouping or aggregate expressions in the SELECT list. Thus Active Query Builder allows to set the Output checkbox for grouping and aggregate expressions. If you try to set this checkbox for a column without Grouping or Aggregate function set, a Grouping checkbox will be set automatically to maintain validity of result SQL query.

When Columns Pane contains columns marked with the Grouping checkbox, a new column called "Criteria for" appears in the grid. This column specifies appliance of criteria to expression groups or to their values.

For example, you have a column "Quantity" with Aggregate function "Avg" in your query and you type the "> 10" in Criteria column. Having the "for groups" value set in the Criteria for column, the result query will contain only groups with average quantity greater than 10, and your query will have the "Avg(Quantity) > 10" condition in HAVING clause. Having the "for values" value set in the Criteria for column, the result query will calculate the Average aggregate function only for records with Quantity value greater than 10, and your query will have the "Quantity > 10" condition in WHERE clause.

Defining grouping


Defining SQL query properties

Various database server specific options are managed within the Query Properties dialog. You may open it using the Properties item of context popup menu of the Query Building Area.

Query Properties dialog for MS SQL Server:

Query Properties dialog for MySQL:


Building a query with sub-queries

Active Query Builder allows to build complex SQL queries with Unions, Sub Queries and Derived Tables visually.

Learn more about:

  • Navigating the query tree
  • Adding a sub-query
  • Adding a derived table
  • Working with unions


Navigating the query tree

The Query Structure Tree represents the query structure in compact tree-like form. Using it, you may jump to any Union, Sub-query, Derived table and to any part of the query with a single mouse click. This feature is extremely useful in case of complex SQL queries.

By setting the appropriate component's options you may set up the tree to hide unnecessary nodes. For example, you may hide Objects and/or Expressions nodes and show only sub-queries structure of the query.

Query Structure Tree

Query Structure Tree


Working wih sub queries

Note: This chapter describes working with Sub-queries used in criteria expressions to limit result dataset (usually in the WHERE and HAVING clauses). To find out how to work with sub-queries used as datasources for the main query (i.e. in the FROM and WITH clauses), please refer to the Derived tables chapter.

You may add a sub-query as part of the expression or condition in the Columns Pane while editing text in a cell. To add a sub-query, right click at the text position for a new sub-query and select the Insert Sub-query item from context popup menu or type the "(Select)" text to the Expression column, the "= (Select)" or "In (Select)" text to the Criteria column. Please note that curly brackets are necessary.

Adding a sub-query

Adding a sub-query

To build a newly added sub-query visually, confirm editing by pressing the Enter key. The corresponding tab is created above the Query Building Area and ellipsis button become visible at the right side of the cell (*). By clicking on the ellipsis button you'll be switched to a sub-query tab where you can build it visually in the same way as the main query. If the cell contains more than one sub-query, the drop-down list will appear to select the necessary sub-query.

(*) The ellipsis button appears only in the VCL and ActiveX Editions of Active Query Builder.

You may always get back to the main query and switch to any sub-query or derived table using tabs above the Query Building Area or using the Query Structure Tree.

Getting access to a sub-query

Getting access to a sub-query


Working with derived tables

Derived Table is a sub-query used as a datasource for the main query.

To add a derived table, you should right click on the Query Building Area and select the Add Derived Table item from context popup menu.

Adding a derived table

Adding a derived table

A new object representing the newly created derived table will be added to the query building area, and the corresponding tab will be created for it. This tab allows you to build it visually in the same way as the main query. Another way to switch to the corresponding derived table tab is to right click at the caption of an object representing the derived table and select the "Switch to derived table" item from context popup menu.

You may set an alias for derived table the same way as for ordinary database object.

You may always get back to the main query and switch to any sub-query or derived table using tabs above the Query Building Area or using the Query Structure Tree.

Getting access to a derived table

Getting access to a derived table


Working with unions

 Top Previous

Union sub-queries are managed within the Union Panel in the top-right corner of the Query Building Area. Initially there's only one union sub-query labeled with the "Q" letter. All required operations are performed by means of context popup menus.

Union sub-queries can be grouped with other sub-queries and joined with different operators (UNION, UNION ALL, EXCEPT, INTERSECT).

  • To add a new union sub-query, select the New Union sub-query menu item.
  • To enclose the sub-query in brackets, select the Enclose in Brackets menu item.
  • To move the sub-query or bracket to the top of the query (the topmost sub-query is the left one), select the Move Left menu item.
  • To move the sub-query or bracket to the bottom of the query, select the Move Right menu item.
  • To remove the sub-query or bracket, select the Remove menu item.
  • To change the union joining operator, select the necessary operator in the list of supported operators in context popup menu.

Union Sub-Query operations:

  • Move Left
  • Move Right
  • New Union Sub-Query
  • Enclose In Brackets
  • Remove
Union sub-query operations

Union sub-query operations

Operations with brackets:

  • Move Left
  • Move Right
  • Remove Brackets
Operations with brackets

Operations with brackets

Select union joining operator
Select uniting operator

Select uniting operator