Blog Details

SQLVisual: A Tool for Quickly Understanding SQL Queries

SQL is a powerful and popular language, but it can become hard to understand once the queries become lengthy, even for expert users or the original creator of a query.

We have developed a tool that capture the logical intent of queries in visual diagrams that can help users understand the meaning of queries faster and more accurately than SQL text alone. First, let's quickly take a look at the graphical representation generated by the SQL query below.

    
SELECT
	DATE_TRUNC('YEAR', hire_date) year, city, count(e.id)
FROM (
	SELECT deptno, hire_date, city, id FROM employee
        WHERE DATE_TRUNC('DAY',hire_date) > '2020-01-01') as e
JOIN (SELECT deptname FROM department WHERE name = 'IT') as d
    ON  e.deptno = d.deptno
GROUP BY DATE_TRUNC('YEAR', hire_date), e.city
    

In the above diagram, we can see the SQL text displayed in a graphical format, showcasing elements such as joins, filtering conditions, and subqueries. When you hover over a node in the diagram, the corresponding SQL text segment will be highlighted. This greatly enhances the understanding of the SQL's meaning, making it more easily comprehensible.

Next, let's take a closer look at the specific meaning conveyed in the diagram. In the above diagram, we leverage the concepts of relational algebra and logical execution plans in the database. Let's illustrate this with an SQL example.

• TableScan : Denotes the name of the table being scanned in the SQL statement,corresponding to the
  'employee' and 'department' sections in the SQL text.
• Filter         : Represents the filtering condition in the SQL, corresponding to the "WHERE name = 'IT'" section
  in the text.
• Project     : Selecting and displaying columns from a table, corresponding to the 'SELECT deptname' section
  in the text.
• Subquery : Represents a subquery in the SQL, corresponding to the 'd' and 'e' parts in the SQL text.
• Join          : Combining two or more tables based on a related column, corresponding to the
  'e join d on e.deptno = d.deptno' section in the SQL.
• Aggregate: Calculates summary statistics on table data, grouping by specific columns. corresponding to the
  'SELECT year, city, count(e.id)' and 'GROUP BY YEAR, e.city' section in the SQL.

Summarize

By visualizing SQL, we help users quickly understand the meaning of queries (e.g., their own past queries or others' queries). This makes SQL more reusable and maintainable. Now, you can visualize your SQL on your own,just copy and paste your SQL (without including schema information) Have fun!