Visual Query Builder

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

Table of contents

• Overview
  • Creating a Visual Query
    • Filtering
    • Sorting
  • Executing a Visual Query
    • Shortcuts
  • The Visual Query Builder symbols
    • Table symbols
    • Join symbols
  • Settings
  • Visualization of an existing SQL query

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 do not 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 the tables will automatically be displayed. The tables will also be added to the SQL expression which can be found 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 only want to see 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 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 do not 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 in ascending order and the conditional expression ORDER BY will be added to the SQL script. To sort the column in descending order, 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 wish to sort, and then on the second, third and so on. You can sort numbers, texts, dates, 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 will automatically be 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 Result set.