Visualize a query from the query text
dbForge Query Builder allows you to automatically visualize SELECT, INSERT, DELETE, and UPDATE queries of any complexity on a diagram. With the intuitive Text Editor, you can write queries that may include WHERE, JOIN, HAVING, or GROUP BY clauses. After that, the tool automatically generates a comprehensive visual representation of the query structure on a digram and in the Document Outline panel.
The guide describes how to:
For example, let’s visualize the SELECT statement that retrieves the sales data grouped by year and quarter and sorted by brand and category.
SELECT
YEAR(orders.order_date) AS year
,DATEPART(quarter, orders.order_date) AS quarter
,brands.brand_name AS brand
,SUM(order_items.quantity * order_items.list_price) AS order_total
FROM production.products
INNER JOIN production.brands
ON products.brand_id = brands.brand_id
INNER JOIN sales.order_items
ON order_items.product_id = products.product_id
INNER JOIN sales.orders
ON order_items.order_id = orders.order_id
INNER JOIN sales.customers
ON orders.customer_id = customers.customer_id
INNER JOIN sales.staffs
ON orders.staff_id = staffs.staff_id
WHERE YEAR(orders.order_date) IN (2017, 2016)
AND brands.brand_name IN ('Trek', 'Surly')
AND products.category_id IN (SELECT categories.category_id
FROM production.categories
WHERE category_name IN ('Cyclocross Bicycles', 'Mountain Bikes', 'Electric Bikes'))
GROUP BY YEAR(orders.order_date)
,brands.brand_name
,DATEPART(quarter, orders.order_date)
ORDER BY year DESC, quarter;
The FROM clause indicates the tables involved in the query where the main table is production.products. The INNER JOIN clauses connect the related tables by their respective foreign key relationships. The WHERE clause filters the results according to specific conditions:
- YEAR(orders.order_date) IN (2017, 2016) limits the data to orders from 2017 and 2016.
- brands.brand_name IN (‘Trek’, ‘Surly’) includes orders from the specified brands.
- products.category_id IN (SELECT categories.category_id …) restricts the results to specific product categories by referencing a subquery that retrieves the category IDs based on the category names.
The GROUP BY clause groups the results by the specified columns. The ORDER BY clause sorts the result in descending order by year and quarter.
Visualize the query on the diagram
1. On the toolbar, click New Query to open the diagram.
2. In the Connect to Server window that opens, select the server you want to connect.
Alternatively, you can create a new server connection by clicking New Connection. In the Database Connection Properties dialog that opens, enter the connection details:
- Server: Specify a server name.
- Authentication: Select an authentication method.
- Login and Password: Enter the username and password you want to log in.
Click Connect to establish the connection. The query document opens.
3. On the toolbar, select the database from the dropdown list.
4. At the bottom of the query document, click Text.
5. In the Text view, enter the SQL query you want to visualize.
6. At the bottom of the query document, click Query Builder to navigate to the diagram that displays the visual representation of the SELECT query.
Given that the SELECT statement includes a subquery, dbForge Query Builder provides a convenient way to organize and view its structure in two tabs:
- Root contains the main SELECT query.
- Unnamed query identifies a subquery used in the WHERE clause.
Both tabs contain the Tabbed Editor where you can add, remove, or modify the query content:
- Selection: Select the columns and specify the selection criteria for them based on which the data will be retrieved from the tables.
- Joins: Perform various operations related to JOINs, such as creating JOINs between tables, managing and removing JOINs, as well as selecting different types of JOINs and adding conditions to the JOIN clause.
-
Where: Set up criteria based on which data of the selected columns will be retrieved according to the specified condition.
-
Group By: Group rows with the same values by one or more columns.
- Having: Specify a filtering condition on groups created in the GROUP BY clause.
- Order By: Sort resulting data in ascending or descending order.
View the query in the Document Outline panel
You can also view a hierarchical representation of the query in the Document Outline panel.
To open it, navigate to the View main menu and select Document Outline. In the panel, you can expand or collapse clause nodes to navigate through the query structure. Clicking the clause node will display the corresponding tab of the Tabbed Editor for the subquery or the root query. In addition, clicking the table column or table node will highlight the corresponding column or table on the diagram.
Want to Find out More?
Overview
Request a demo
