[ Master documentation index ]
Introduction
The Query Builder
provides and easy way to develop database queries. The query builder
uses a point and click interface and does not require in-depth
knowledge about the SQL syntax.
The query builder is part of the SQL Commander, alongside the SQL
editor. When you are ready to test a query built with the query
builder, you just copy it to the SQL Editor for execution. To open the
query
builder make sure the SQL Commander tab is selected and then
either choose the SQL->Show Query Builder
menu choice or click the vertical Query Builder button
to the right of the SQL editor.
Note: This document talks only about Tables even though the query builder supports both table and view objects.
Figure: The
query builder
Current Limitations
These are the current limitations in the query builder:
- It is not possible to generate a query builder definition from on an existing SQL statement (reverse engineer)
- Unions and sub selects are not supported.
- Not all join types are supported when joins are expressed as
WHERE clause conditions. The Inner join type is supported for all
databases, but the Left and Right
types are only supported for databases with proprietary syntax to
express these types, e.g., Oracle, SQL Server and Sybase. The Full type
is not supported for any database. If a join type is not supported, the
setting in the Join Properties dialog is ignored.
Creating
a Query
To create a query, make sure the SQL Commander tab is current and open the query builder using the SQL->Show Query Builder menu choice or Query Builder button as described earlier.
Figure: The initial appearance of the query builder
The easiest way to jump between the query builder and the SQL editor is
by clicking the vertical control buttons to the right of the editor.
Clicking these buttons changes the display, but does not copy the query
from one display to the other. To copy the current query from the query
builder to the SQL editor, use the query builder toolbar buttons at the
top of the query builder:
Figure: Query builder toolbar
- The first button (from left) replaces the content of the SQL editor with the query SQL
- The second button adds the query last in the SQL editor
- The third button copies the query to the system clipboard
- The fourth button opens the editor properties
The two first buttons automatically change the display to the SQL Editor.
Adding
Tables
To add tables, make sure the database objects tree and the actual table
and/or view objects are visible. Then select and drag nodes from
the tree into the diagram area.
Figure: Adding tables to the query builder
To add a table, drag it from the object tree to the diagram area of the
query builder. When the table is dropped in the diagram area, it is
shown as a window with the table name as the window title.
Below the title is a text field where an optional table alias can be
entered. If a table alias is specified, it is used in the query builder
and the generated SQL statement to refer this table.
Under the table alias field is a list of all table columns. A check box
in front of each name is used to select whether the column should be
included in the query result set. Columns selected for the query result
set also appear in the Columns and Sorting details tabs.
Joining Tables
To join two tables, select the column in the source table window with
the mouse, drag it to the target table column, and drop it.
Figure: Joining two tables
The two columns now represents a join condition, represented by a link
between the columns. If more than one join condition is needed, link
additional columns in the two tables by dragging and dropping the
columns in the same way as for the first join condition. The default
join type is an Inner join and the default condition is "equal to" (=),
represented as an icon with overlapping circles with the shared area
shaded and an equal sign below them.
Join
Properties
The join characteristics can be modified by either double-clicking the
icon or selecting Join Properties from the right click menu. The join
properties window shows the source and target table columns and the
conditional operator.
The join type defines how the records from the tables should be combined:
- Inner
This is the most common join type as it finds the results in the intersection between the tables.
- Left
This join type limits the results to those in the left table leaving 0 matching records in the right table as NULL.
- Right
This is the same as left join but reversed
- Full
A full join combines the results of both left and right joins.
Figure: Join Properties window
You can change the join type and the conditional operator in the Join Properties dialog.
Note: If you have multiple join conditions (linked columns)
between two tables, you can specify different conditional operators for
each join condition, but the join type is shared between all join
conditions; if you change it for one join condition, it is changed for
all the other join conditions linking the two tables. This is not a
restriction in the query builder but rather how SQL is defined.
Here is the sample SQL generated from the previous join definition:
SELECT *
FROM HR.EMPLOYEES
INNER JOIN HR.DEPARTMENTS
ON (HR.EMPLOYEES.DEPARTMENT_ID = HR.DEPARTMENTS.DEPARTMENT_ID)
|
Remove Tables and Joins
A table window is removed by clicking the close icon in the window header. A join is removed by selecting Remove Join in the right click menu while the mouse pointer is over the join icon.
Figure: Diagram right click menu
All tables and joins may be removed via Remove All Joins and Remove All Tables.
Query
Details
The Details tabs below the diagram area are used to define the various
parts of the query. The tabs briefly represents the following parts of
the final SQL:
SELECT <Columns>
FROM <tables>
WHERE <Conditions>
GROUP BY <Columns>
HAVING <Grouping>
ORDER BY <Sorting>
|
(The <tables> clause is defined in the diagram).
Columns
Use the Columns tab to specify characteristics of the columns that
are included in the query. The list is initially empty until a
column is checked in a table window or if manually adding a column
expression. Columns will appear in the list in the same order as they
are checked but may be manually moved at any time with the up and
down buttons. To include all columns from a table, right click in the column list in the table window and choose Select All.
Figure: The columns tab
The previous screenshot shows a total of 5 checked columns in the two
tables. These are presented in the columns list by their full column
identifier (not by any table alias). To remove a column from the list
then uncheck the corresponding column in the table window.
The alias field is used to
specify an optional alias identifier for the column. The alias will be
the identifier for the column in the final query and will also appear
as the column name in the result set produced by the query. Check the
documentation for the actual database whether the alias must be quoted
since the query builder does not do this for you.
The Aggregate and Group by fields are used in combination:
- The Aggregate field lists the available aggregation functions (AVG, COUNT, MAX, MIN, SUM) that may be used for columns
- The Group by field specifies whether the column should be included in the group for which aggregate columns are summarized
If an aggregate is selected then all non-aggregate columns must be Group By enabled.
A custom expression may be added by entering data in the empty row last in the list, e.g., "col1 + col2" or "TO_CHAR(ts_col, 'DD-MON-YYYY HH24:MI:SSxFF')". Once entered, press enter to insert a new empty row. You can remove a custom expression by selecting it and clicking the Remove button.
Conditions
This Conditions tab is used to manage the WHERE
statement for the query. A where statement may consist of several where
conditions each connected by AND or OR. The evaluation order for each
where condition is defined by indention in the condition list. Each
level in the list will be enclosed by brackets in the final SQL.
Here is an example from the Conditions tab.
Figure: Condition settings
To
create a new where condition press the indexed button in the list. In
the menu that is displayed you may choose to create a new condition on
the same level, a compound condition or delete current.
For compound conditions you may choose whether All (AND), Any (OR), None (NOT OR) or Not All (NOT AND) conditions must be met for its sub conditions. The SQL for the previous conditions is:
WHERE emp.SALARY > 4000
AND (
dept.DEPARTMENT_NAME = 'Human Resources'
OR dept.DEPARTMENT_NAME = 'IT'
)
|
Next to the input field for each condition, there is a drop
down button. When pressed it shows all columns that are available in
the tables currently being in the query builder. Pick columns from the
list instead of typing these manually.
Figure: List of columns in the conditions tab
The values specified in the input fields will be used in the query exactly as specified. You need to manually quote text data.
Grouping
The grouping tab is used to define the conditions for the HAVING
statement. The capabilities for this statement are the same as for
WHERE statements except that grouping process the result set after any
summary functions has been applied. Please read the Conditions section for more information.
Sorting
The sorting tab is used to specify how the final result set will be
sorted. The listed columns are the same as in the Columns tab.
Figure: The sorting tab
All columns listed in the Columns tab are initially listed in the Available Columns table. Select the ones you want to use in the sorting criteria and click the Move Left button to move them to the Sorted Columns table.
In the Sorted Columns table, you can change the default sort order (ascending) by clicking the check box in the Descending Order
column. You can remove columns from the sorting criteria by selecting
them in the Sorted Columns table and clicking the Move Right button.
SQL
Preview
The SQL Preview tab at the bottom of the query builder is used to show
a preview of the final SQL. This is a read-only view and cannot be
modified.
Testing
the Query
To test the query, simply press the appropriate toolbar buttons in the
query builder to copy the SQL to the SQL editor. Then execute the SQL
as usual in the SQL editor.
Figure: Testing the SQL
To further refine the SQL press the Query Builder button and apply the neccessary changes.
Properties
controlling Query Builder
There are a few properties that control how the query builder works and
the SQL it generates. Check the following sections for details.
Express joins as JOIN clause or WHERE condition
This property is available via Connection Properties->[Database Type]->SQL Editor->Generate JOIN Clause in SQL Builder.
Joins can be expressed either via the standardized SQL notation or by
database specific syntax. The database specific syntax is somewhat
different between the supported databases and the Full outer join type is generally not supported. The default setting of this property is by JOIN clause.
A simple inner join expressed as a JOIN clause:
FROM HR.EMPLOYEES
INNER JOIN HR.DEPARTMENTS
ON (HR.EMPLOYEES.DEPARTMENT_ID = HR.DEPARTMENTS.DEPARTMENT_ID |
Here is the same join expressed as a WHERE condition:
FROM HR.EMPLOYEES, HR.DEPARTMENTS
WHERE HR.EMPLOYEES.DEPARTMENT_ID = HR.DEPARTMENTS.DEPARTMENT_ID |
The syntax for expressing Inner and Outer joins in WHERE conditions is
different between databases. Oracle, for example, uses the "(+)"
sequence to the left or right of the conditional operator to express
left or right joins. SQL Server and Sybase use "*=" or "=*" for the
same purpose.
DbVisualizer automatically sets the correct join notation when
generating joins as WHERE conditions for databases that support left
and right joins using WHERE conditions.
Table and Column Name qualifiers
Qualifying table names with the schema or database name and qualifying column names with table name are defined in Connection Properties->[Database Type]->Qualifiers.
Delimited Identifiers
Identifiers that contain mixed case characters or include special characters need to be delimited. Define this in Connection Properties->[Database Type]->Delimited Identifiers.
Drag style and Diagram Size
In the editor properties it is possible to set what style the windows in
the query builder diagram should have when moving them. It is also
possible to set the default size for newly added table windows.
Copyright © 2006 Onseven Software AB. All rights reserved.
