Visual Query Builder

Note: This feature is available in Enterprise, Enterprise for AWS editions only.

Overview

The Visual Query Builder is a user-friendly visualization tool that can help you to create queries to the database and see results. You don’t need to know SQL language to work in it. The Visual Query Builder may be useful for:

• building queries;
• complex queries analysis;
• easy query editing.

To open the Visual Query Builder click the Query Builder tab in the SQL Editor right toolbar.

Creating a Visual Query

1. Select tables in the Navigator tree and drag-and-drop them into the Visual Query Builder area. The existing connections between tables will be displayed automatically. The tables will be added also to the SQL expression which is placed in the field to the right of the diagram.

2. To create a new join between tables, connect their columns holding the left mouse button. The connection between the selected columns of the tables will appear in the diagram and the Inner Join will be added to the SQL script.

3. You can change a Join type clicking the Join label on the connection line.
4. To remove a join between tables, click on the line, then press the Delete button. The connection will be removed from the diagram and the Join will disappear from the SQL script.
5. By default all tables’ columns are included in the query. If you want to see only certain columns in your query result, select the checkbox near the column name.

Filtering

6. WHERE condition with the filter value is used for filtering. To add a filter, write it in the top filter field.

Column name	Operation Sign	Value
A table column name. You have to write a table alias or table name before if another column has the same name	The most common signs: =, >, <, <>, LIKE, ILIKE, BETWEEN	A column value, used as a parameter. Text and time values must be rounded by single quotes, numeric values don’t need any quotes

Filter example:

Sorting

7. To apply a sorting condition to a column press the sorting icon next to a column name on the diagram. The column will be sorted ascending and the conditional expression ORDER BY will be added to the SQL script. To sort the column descending press the sorting icon again to select the down arrow. If you want to remove a condition, continue to click the sorting icon to deactivate it. Sorting can be applied to multiple columns in different tables. First apply sorting on the first column you want to sort, then on the second and so on. You can sort numbers, text, date, time and other values.

Executing a Visual Query

Use the Execute SQL statement button on the left pane to execute a query and get the results in the same tab. If you want to see the result in a new tab press the Execute SQL statement in a new tab button .

Shortcuts

You can use the same shortcuts as in the SQL Editor to execute the Visual Query.

Key	Description
Ctrl+Enter	Execute the SQL statement
Ctrl+\ or Ctrl+Shift+Enter	Execute the SQL statement in a new tab

The Visual Query Builder symbols

The Visual Query Builder uses the following visual tools to display queries on the diagram:

Table symbols

Symbol	Description
	Table Primary Key is bold and displayed at the top of the table.
	Table Alias is used to shorten your Join Statement.
	Colored table header marks the first table in your Join Statement.
	Colorless header marks a joined table in your Join Statement.
	Line goes from the joined table to the first table.

Join symbols

Available Join types are described in the table below. The Visual Query Builder can show results only for those types of Joins that are supported by your database.

Symbol	Description
	Inner Join
	Left Join
	Left Outer Join
	Right Join
	Right Outer Join
	Full Join
	Full Outer Join
	Cross Join

Settings

You can customize the diagram view using the bottom toolbar to make the work with the diagram easier.

• Layout updates the diagram view to display all of its objects in the most optimal way.

• Zoom in and Zoom out enlarges or shrinks the diagram view.

• Settings menu contains additional settings of the Visual Query Builder. Press the Settings button at the bottom toolbar to open it.

  • Layout on update enables Auto-layout feature. As soon as you add a new object to the diagram, the diagram view is automatically updated to display all of its objects in the most optimal way.
  • Show join type on entities moves Join labels from lines into headers of joined tables.
  • Show Type adds information about column types into entities.
  • Show Icons adds icons of column types into entities.
  • Notation changes the representation of connection lines. Simple notation is set by default. You can change it to the IDEF1X language type.

Visualization of an existing SQL query

If you write a JOIN statement by yourself and then want to convert it to the diagram view, just switch the SQL Editor with your statement to the Visual Query Builder.

Note: the Visual Query Builder can transform the syntax of your query, but it does not affect the query result in the Resultset.