Visual Query Builder

Note: This feature is available in Lite, Enterprise, Ultimate and Team editions only.

Overview

The Visual Query Builder in DBeaver is a visual tool that helps you build and understand SQL queries in complex databases. It lets you work with your database in a visual way, making it easy to see how tables connect and how data flows between them. This tool is useful when you want to create queries without writing SQL code yourself. The Visual Query Builder in DBeaver automatically converts your visual selections into SQL scripts, making it easier to explore and work with your data.

Getting started

To start using Visual Query Builder, click the Builder button in the SQL Editor for the desired database.

Tip: You can switch between the SQL Editor and the Visual Query Builder easily. If you write a valid SQL query in the SQL Editor, it will show up in the Visual Query Builder. The same way, any query you build visually will appear in the SQL Editor.

Visual Query Builder interface

The Visual Query Builder interface consists of the following sections:

• Workspace: This area displays all the tables, columns, and their relationships that you have added to your query.

• Palette: The Palette shows tools to interact with tables, and the section below displays the tables you have already selected and moved into the Visual Query Builder workspace.

• Query settings window: This section displays the Query settings, where you can configure columns, conditions, joins, sorting, and other settings related to your query.

Executing queries

The Visual Query Builder works in a similar way to the SQL Editor when you run queries.

• Click the Execute SQL statement button to run your query. You will see the results in the same tab.
• You can also use the Execute SQL statement in new tab button to run the query and open the results in a new tab.

You will find both buttons on the vertical toolbar of the Visual Query Builder, giving you easy ways to run your queries and view the results.

Note The toolbar is customizable. For more information, see Toolbar Customization.

Query configuration

The Visual Query Builder allows you to easily add tables, set query conditions, and customize how your query results are displayed using the Query settings available at the bottom of the workspace. These settings provide options for filtering, sorting, selecting columns, and setting join conditions, giving you full control over your query results.

To open/close the Query settings window, click the QUERY button .

Tip: You can toggle the visibility of the generated SQL query text by using the Show/hide generated SQL query text button on the right side of the window or by pressing Ctrl+B (⌃B for macOS).

Below are the sections explaining how to work with the Visual Query Builder capabilities in more detail:

Tables

You can add tables to the Visual Query Builder workspace using one of the following methods:

1. Drag-and-Drop: Drag the tables from the Database Navigator into the Visual Query Builder workspace.

2. Using the Palette:

   • In the Pallete menu, select Add Table from the Tools section.
   • Click on the Visual Query Builder workspace and choose the necessary table.

Columns

By default, all columns (*) from the tables you add are selected. To customize use the following methods:

Selecting columns in the workspace

• Click the checkboxes next to the column names in the table within the Visual Query Builder workspace to include or exclude columns from your query.
• The SQL script will update automatically in the SQL script area, reflecting your selections.

Using the Columns tab in the Query settings

• Select the Columns tab in the Query settings. In this tab, you can specify details related to the columns:

  	Option	Description
  	Column	Select the desired column for your SELECT statement from the dropdown list.
  	Alias	Provide a custom name for the column.
  	Grouping	Define grouping for aggregated data.
  	Aggregation	Select an aggregation function (e.g., COUNT, AVG, MAX, MIN, SUM) to process the selected data.

Adding and removing columns

• Adding a Column: Press the Add button . A new column will be added to the query. Click on the first cell in the Column or Expression column and select a column from the dropdown list that appears.

  

• Removing a Column: To remove a column, click on the row containing its name and press the Remove button .

Adjusting column order

Use the Move Up/Down buttons to change the display order of columns in the result table. This will adjust the sequence in which the columns appear in your query.

Aliases

You can set a user-friendly name for your columns by clicking on a cell in the Alias column and entering the desired name. This alias will be reflected in the SQL script area and the final query result.

Grouping and aggregation

• If you want to apply a grouping condition, click the checkbox in the Grouping column row. This action will update your expression automatically. Other columns will become aggregated, and if there are no other columns, a COUNT(*) expression will be added.

• You can select different aggregation functions from the dropdown list or manually enter your preferred function in the cell.

Tip When you remove columns, they are also removed from the grouping expression. Any newly added columns will be automatically included in the grouping expression.

Conditions

You can add conditional expressions using the Conditions tab, which allows you to manage query conditions by filtering your query results.

• Select the Conditions tab in the Query settings. In this tab, you can specify details related to the columns:

  Setting	Description
  Left Operand	Defines the left operand of the conditional expression. This can be a column or a numeric value.
  Operation	Specifies the comparison rule (e.g., =, >, LIKE) to apply between the left and right operands.
  Right Operand	Defines the right operand of the expression, which can be a column, numeric value, or string.

  

Adding and removing conditions

• Adding a conditional expression: Click the Add button on the right side of the tab. This action will create a new condition row, and a default WHERE clause will be automatically added to the SQL script area.

• Removing a conditional expression: To remove an existing condition, select the row containing the condition you wish to delete and click the Remove button on the right side of the tab.

Joins

When you add a table to the Visual Query Builder workspace, the joins are created automatically based on existing relationships:

• Automatic Joins: If the table you add has an existing relationship with one or more tables already in the workspace, the join will be displayed automatically.

• Inner join behavior: If the table does not have any existing relationship with the tables already in the workspace, the system will create a SIMPLE JOIN with the first table you added to the workspace.

Tip: You can disable automatic joins based on foreign keys in the Miscellaneous tab by unchecking the Auto-create joins according to foreign keys option. For more information, see Miscellaneous section.

Modifying joins

To modify joins:

• Right-click on the connection line between tables in the workspace to open a context menu. This menu allows you to change join types but is limited to modifying Left, Right, and Full joins.

• Open the Joins tab in the Query settings to manage and adjust join settings for all tables in the workspace. In this tab, you can work with the following columns:

  	Setting	Description
  	Table/Conditions	Displays the tables involved in the join along with the join conditions.
  	Type	Allows you to modify the join type (e.g., Simple, Inner, Left, Right, Full, Cross, Natural).
  	Alias	Lets you define a custom name for the join.

  

Sorting

You can set the order of rows in the result table.

• Select the Sorting tab in the Query settings. In this tab, you can specify details related to the columns:

  Setting	Description
  Conditions or Expressions	Click on the first cell in this column to display a drop-down list of all available columns. Select the column you want to sort by clicking on its name.
  Order	Defines whether the selected column should be sorted in ascending or descending order.

  

Adding and removing sorting conditions

• Adding a sorting condition: Click the Add button on the right side of the tab. This action will create a new sorting row, and a default ORDER BY expression will be automatically added to the SQL script area.

• Removing a sorting condition: To remove an existing sorting condition, select the row containing the condition you wish to delete and click the Remove button on the right side of the tab.

Miscellaneous

You can configure additional settings in the Miscellaneous tab.

Setting	Description
Add table aliases	Enable or disable the automatic generation of aliases for tables by selecting this check-box.
Use fully qualified table names	Use full table names (including schema) by selecting this check-box, which disables auto-completion for table names.
Auto-create joins according to foreign keys	Enable or disable the automatic creation of joins based on foreign key relationships.
Autosave on SQL-editor switch	Enable autosave when switching between SQL editors by selecting this check-box.