{"id":26221,"date":"2024-11-03T14:31:02","date_gmt":"2024-11-03T14:31:02","guid":{"rendered":"https:\/\/sonra.io\/?p=26221"},"modified":"2025-01-07T11:03:58","modified_gmt":"2025-01-07T11:03:58","slug":"sql-visualisation-guide","status":"publish","type":"post","link":"https:\/\/sonra.io\/sql-visualisation-guide\/","title":{"rendered":"SQL Visualisation Guide &#8211; Query Diagrams, Lineage &amp; ERD"},"content":{"rendered":"\n<p>Have you ever inherited the SQL codebase from someone else where it is in a \u201cbit of a mess\u201d? \ud83d\ude01<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Where you find nested CTEs (Common Table Expressions), stacked like Russian dolls.<\/li>\n\n\n\n<li>Where the joins are crossing relentlessly between tables like tangled vines, pointing back on themselves in recursive loops that no one ever intended to untangle.<\/li>\n\n\n\n<li>Where the same joins lead to database views, only to reveal that those views contain\u2026 yet more views. Each layer sinks deeper into a quagmire of cryptic, badly named aliases and obscure logic. Each time you think you figured out one view, another one pops up like a jack in a box.<\/li>\n\n\n\n<li>Where every time you make a fix, something else gets unfixed.<\/li>\n\n\n\n<li>Where the SQL code feels alive laughing at your attempts to bring order to the chaos.<\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-image\"><span class=\"m-posts__contentImg\"><img decoding=\"async\" width=\"1024\" height=\"1024\" src=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/person-working-at-a-desk-surrounded-by-papers-with.jpeg\" alt=\"person working at a desk surrounded by papers with code displayed on screen\" class=\"wp-image-26222\" srcset=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/person-working-at-a-desk-surrounded-by-papers-with.jpeg 1024w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/person-working-at-a-desk-surrounded-by-papers-with-300x300.jpeg 300w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/person-working-at-a-desk-surrounded-by-papers-with-150x150.jpeg 150w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/person-working-at-a-desk-surrounded-by-papers-with-768x768.jpeg 768w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/span><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>Sound familiar? Most data engineers and SQL developers have been there including myself\u2014staring at a spaghetti-like web of queries, trying to untangle a chaotic SQL codebase. It\u2019s a common nightmare. Traditionally, you had to fight your way through the mess with pen and paper, manually drawing relationships, or running one inline view at a time, commenting and uncommenting joins just to make sense of the madness.<\/p>\n\n\n\n<p>SQL Visualisers can bring structure to the chaos of complex SQL code by offering graphical representations of queries, views, CTEs, tables, and their relationships, which are much easier to understand than raw code. They do not replace the code but complement it.<\/p>\n\n\n\n<p>I&#8217;ll dive deep into visualising SQL in this post. Before diving into the details, it&#8217;s important to clarify what &#8220;visualising SQL&#8221; means, as it can vary depending on the context and your goals.<\/p>\n\n\n\n<p>I have come across the following interpretations for the terms <strong>visualising SQL<\/strong> and <strong>SQL visualisation <\/strong>as part of the research for this blog post :<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Visualising SQL code in a diagram (Data Query Language, DQL)<\/li>\n\n\n\n<li>Visualising dependencies in a data pipeline as part of SQL data lineage<\/li>\n\n\n\n<li>Visually building SQL queries (drag and drop)<\/li>\n\n\n\n<li>Creating logical and physical data flow diagrams<\/li>\n\n\n\n<li>Visually tracing and trailing data through an SQL query<\/li>\n\n\n\n<li>Visualising SQL joins in a query as Venn diagrams<\/li>\n\n\n\n<li>Parsing and representing the AST (abstract syntax tree) hierarchy of a query<\/li>\n\n\n\n<li>Visualising the output of an SQL query in charts, graphs, and dashboards<\/li>\n\n\n\n<li>Visualising database schemas, tables, and views in ER diagrams<\/li>\n<\/ul>\n\n\n\n<p>I will break down the different types and approaches to visualising SQL, covering their meaning, use cases, examples, and tools. While I\u2019ll touch on all types of SQL visualisation, I\u2019ll focus on visualising SQL query code, explaining its benefits and exploring the various methods and tools available.<\/p>\n\n\n\n<p>In a hurry? Here are the key takeaways.<\/p>\n\n\n<div class=\"note-block\">\n\t<div class=\"note-block-icon\"><\/div>\n    \t<h4>Key takeaways<\/h4>\n    \t<div class=\"note-block-content\"><p>The concept of SQL visualisation can mean many different things. Here are 8 distinct types of SQL visualisations:<\/p>\n<ul>\n<li>Visualising the SQL query code itself.<\/li>\n<li>Displaying SQL data lineage to track data flow.<\/li>\n<li>Using visual tools to build SQL queries.<\/li>\n<li>Visual aids for learning SQL.<\/li>\n<li>Showing SQL joins as diagrams.<\/li>\n<li>Visualising SQL parse trees (representing query structure).<\/li>\n<li>Displaying SQL results as charts and dashboards.<\/li>\n<li>Creating visual ER diagrams from SQL or DDL code.<\/li>\n<\/ul>\n<p>Each approach helps different users understand and interact with SQL in various ways.<\/p>\n<p>In this post we focus on visualising SQL query code.<\/p>\n<p>SQL, while seemingly simple, can lead to highly complex queries that are hard to read and maintain. Visualising SQL queries can make it easier to interpret and debug complex code.<\/p>\n<p>As AI-generated SQL queries become more prevalent, tools for visualising SQL can play a crucial role in helping users review these automatically generated queries.<\/p>\n<p>There are different ways to visualise SQL code depending on what you want to highlight:<\/p>\n<ul>\n<li><b>Focus on query intent<\/b>: This method shows the main goal of the query, making it easier to understand without getting lost in technical details.<\/li>\n<li><b>Focus on syntax<\/b>: This approach visualizes the specific structure and syntax of the SQL code, showing how the query is built step by step.<\/li>\n<\/ul>\n<p>SQL to text is another way to explain SQL code. Instead of using visuals, it describes the purpose and logic of a query in words. This method complements visual SQL representations by offering a textual explanation of what the query does.<\/p>\n<p>A tool that automatically visualises SQL query code is an SQL Visualiser.<\/p>\n<p>Examples for SQL Visualisers are <a href=\"https:\/\/flowhigh.io\/visualise-sql\">FlowHigh<\/a> and <a href=\"https:\/\/queryvis.com\/\">QueryVis<\/a>.<\/p>\n<\/div>\n<\/div>\n\n\n<script src=\"https:\/\/sonra.io\/wp-content\/my-rcrs\/pdpopup.js\" defer=\"\"><\/script>\n<div class=\"popup-container\">\n    <div class=\"pdpopup\">\n        <span id=\"cls-popup\">&#10005;<\/span>\n        <div class=\"popup-cnt\">\n            <div class=\"popup1st\">\n                <p>Use Flexter to turn <span style=\"color:#3D68F8;\">XML<\/span> and <span style=\"color:#3D68F8;\">JSON<\/span> into Valuable Insights<\/p>\n                <ul class=\"flextlist\"><li>100% Automation<\/li><li>0% Coding<\/li><\/ul>\n                <div class=\"ppbtns\">\n                    <a href=\"https:\/\/sonra.io\/flexter-product-page\/\" class=\"button-effect jmp-btn\" target=\"_blank\" rel=\"noopener\">Learn More<\/a><a href=\"https:\/\/sonra.io\/xml-to-csv-converter\/\" class=\"button-effect jmp-btn\" rel=\"noopener\" target=\"_blank\">Try For Free<\/a>\n                <\/div>\n            <\/div>         \n        <\/div>\n    <\/div>\n<\/div>\n\n\n\n<h2 class=\"wp-block-heading\"><a id=\"post-26221-_auvwx4t2nn1v\"><\/a>What exactly Is SQL Visualisation?<\/h2>\n\n\n\n<p>I\u2019ve uncovered nine distinct categories when it comes to visualising SQL, each with its own unique focus and benefits. Let me break them down for you, covering the meaning behind each approach, the use cases where they shine, real-world examples, and the tools that bring them to life.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><a id=\"post-26221-_c9eg70vrj8ky\"><\/a>Visualising SQL Query Code in a Diagram<\/h3>\n\n\n\n<p>I will cover this topic in depth in three separate sections further down in this blog post :<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><a href=\"https:\/\/sonra.io\/sql-visualisation-guide\/#benefits-and-use-cases-of-visualising-sql-code\" target=\"_blank\" rel=\"noreferrer noopener\">Benefits of visualising SQL code?<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/sonra.io\/sql-visualisation-guide\/#the-top-methods-for-visualising-sql\" target=\"_blank\" rel=\"noreferrer noopener\">How to visualise SQL code?<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/sonra.io\/sql-visualisation-guide\/#sql-visualiser-flowhigh\" target=\"_blank\" rel=\"noreferrer noopener\">SQL Visualiser tools to visualise SQL.<\/a><\/li>\n<\/ul>\n\n\n\n<p>Here is a quick overview to get you started.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><strong>What it means<\/strong><\/h4>\n\n\n\n<p>Visualising SQL transforms SQL code into a visual diagram. SQL queries are inherently structured hierarchically, resembling a tree, and can be graphically represented as a graph or flowchart. The visualisation helps to simplify complex SQL queries by breaking them down into easily understandable components.<\/p>\n\n\n\n<p>A picture is often worth a thousand words<\/p>\n\n\n\n<figure class=\"wp-block-image\" data-hd-variant-url=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/sql-query-example-showing-distinct-product-combina.png\"><span class=\"m-posts__contentImg\"><img decoding=\"async\" width=\"1281\" height=\"643\" src=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/sql-query-example-showing-distinct-product-combina.png\" alt=\"SQL query example showing distinct product combinations and counts\" class=\"wp-image-26223\" srcset=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/sql-query-example-showing-distinct-product-combina.png 1281w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/sql-query-example-showing-distinct-product-combina-300x151.png 300w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/sql-query-example-showing-distinct-product-combina-1024x514.png 1024w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/sql-query-example-showing-distinct-product-combina-768x385.png 768w\" sizes=\"(max-width: 1281px) 100vw, 1281px\" \/><\/span><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p><em>Visualising SQL code: query diagram<\/em><\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><strong>Example<\/strong><\/h4>\n\n\n\n<p>Imagine you have a query joining multiple tables, inline views, subqueries, database views, and CTEs. Instead of reading through the SQL code, the visualisation shows each table and view as boxes (nodes). The lines (edges) connecting them with the SQL Operators (nodes) represent dependencies.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><strong>Use Case<\/strong><\/h4>\n\n\n\n<p>The goal of query visualisation is to help users more quickly understand the intent of a query, as well as its relational query pattern. This is typically done for a single query to better understand how a query is structured hierarchically, e.g. nested inline views and CTEs. It also shows how data sets are related without diving into the code itself. The visual representations of SQL code provide a clear and intuitive way to understand SQL code, making it easier to grasp both the logic and the data relationships within complex queries. This can be particularly helpful for debugging, optimising performance, or explaining the query to others.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><a id=\"post-26221-_oo4yzh1oxsat\"><\/a>Tool<\/h4>\n\n\n\n<p>Using an SQL query visualisation tool you can visualise SQL code in a flow diagram. <a href=\"https:\/\/sonra.io\/flowhigh\/\">FlowHigh SQL Visualiser<\/a> visualises both data sets and SQL Operators as nodes and uses arrows to describe dependencies. I have included a detailed <a href=\"https:\/\/sonra.io\/sql-visualisation-guide\/#sql-visualiser-flowhigh\">review of FlowHigh<\/a> at the end of this post<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><a id=\"post-26221-_4s38ejau5yyv\"><\/a>Visualising data lineage from SQL query<\/h3>\n\n\n\n<p>Data lineage from an individual SQL query focuses on understanding the structure, logic, and relationships within a single query, such as joins, filters, and aggregations. We have looked at this in the section about <a href=\"https:\/\/sonra.io\/sql-visualisation-guide\/#the-top-methods-for-visualising-sql\" target=\"_blank\" rel=\"noreferrer noopener\">Visualising SQL query code.<\/a><\/p>\n\n\n\n<p>Visualising Data lineage in SQL looks at the bigger picture. It focuses on visualising dependencies in a data pipeline on mapping the flow and interconnections between multiple SQL operations across different stages and layers.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><strong>What it means<\/strong><\/h4>\n\n\n\n<p>In the context of data lineage of SQL, visualising dependencies refers to mapping the relationships and connections between different SQL operations\u2014such as queries, updates, transformations, and aggregations\u2014within a larger data pipeline. A data pipeline consists of multiple stages where data is extracted, transformed, and loaded (ETL) or where various queries interact with the data in different stages, and these stages depend on each other to deliver the final outcome.<\/p>\n\n\n\n<p>SQL lineage visualisation provides a clear picture of how data flows through each step, allowing you to trace the source of data, its transformation, and where it is eventually stored or used. The visualisation often takes the form of a flow chart or graph, where each box represents a data set or data flow ( SQL operation). The arrows represent how data is passed between data sets and operations and define the dependencies.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Example<\/h4>\n\n\n\n<p>Suppose you have a data pipeline that processes sales data:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Step 1: A query extracts raw data from several source tables.<\/li>\n\n\n\n<li>Step 2: This data is transformed by joining with a table of customer details.<\/li>\n\n\n\n<li>Step 3: Another query aggregates the results by region and creates summary tables.<\/li>\n\n\n\n<li>Step 4: The final query formats the output for reporting.<\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-image is-resized\"><span class=\"m-posts__contentImg\"><img decoding=\"async\" width=\"813\" height=\"1650\" src=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/flowchart-of-data-queries-with-source-tables-and-r.png\" alt=\"flowchart of data queries with source tables and report output\" class=\"wp-image-26224\" style=\"width:617px;height:auto\" srcset=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/flowchart-of-data-queries-with-source-tables-and-r.png 813w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/flowchart-of-data-queries-with-source-tables-and-r-148x300.png 148w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/flowchart-of-data-queries-with-source-tables-and-r-505x1024.png 505w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/flowchart-of-data-queries-with-source-tables-and-r-768x1559.png 768w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/flowchart-of-data-queries-with-source-tables-and-r-757x1536.png 757w\" sizes=\"(max-width: 813px) 100vw, 813px\" \/><\/span><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>In this pipeline, the results of Step 1 feed into Step 2, and so on. A visualisation of this pipeline would show these steps as boxes connected by arrows, representing how data flows between them and how each query depends on the results of the previous one.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><strong>Use Case<\/strong><\/h4>\n\n\n\n<p>Visualising SQL lineage is super useful. It helps with regulatory compliance by tracking data origins and transformations, making it easier to meet standards like GDPR and HIPAA. It also improves data quality and debugging by tracing data through pipelines to find issues quickly. Plus, it ensures that changes to data flows or data sets in a data pipeline don\u2019t disrupt other data flows.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><span class=\"m-posts__contentImg\"><img decoding=\"async\" width=\"890\" height=\"752\" src=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/data-flow-diagram-illustrating-stages-data-set-and.png\" alt=\"data flow diagram illustrating stages data set and dependencies\" class=\"wp-image-26225\" srcset=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/data-flow-diagram-illustrating-stages-data-set-and.png 890w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/data-flow-diagram-illustrating-stages-data-set-and-300x253.png 300w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/data-flow-diagram-illustrating-stages-data-set-and-768x649.png 768w\" sizes=\"(max-width: 890px) 100vw, 890px\" \/><\/span><\/figure>\n\n\n\n<p><em>SQL data lineage: Dependencies of data sets and SQL data flows in a data pipeline.<\/em><\/p>\n\n\n\n<p>From my experience, <a href=\"https:\/\/sonra.io\/snowflake-data-lineage-guide\/\">data lineage<\/a> plays a big role in improving data governance. It helps boost accountability by showing exactly who owns what and making sure policies are followed.<\/p>\n\n\n\n<p>I\u2019ve found it\u2019s also a good tool for optimising data pipelines by spotting inefficiencies or bugs you might otherwise miss. When it comes to data pipeline migrations, lineage is useful to understand the data pipeline that needs to be migrated.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Tool<\/h4>\n\n\n\n<p>An SQL lineage tool helps organisations to track and visualise the flow of data through systems, pipelines, and transformations, providing insights into the data\u2019s origin, transformations, and destinations.<\/p>\n\n\n\n<p>The tool looks at the history of all the SQL queries that have been run and then creates a visual diagram that shows how these queries are connected or depend on each other.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Visual SQL Query Builder (Drag and Drop)<\/h3>\n\n\n\n<p><\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><a id=\"post-26221-_r8wt54hcnuwu\"><\/a><strong>What is a visual SQL builder or editor<\/strong>?<\/h4>\n\n\n\n<p>Visualising SQL query code interprets the SQL code. Visual SQL builders take the opposite route. Instead of coding SQL, users can visually build queries by dragging and dropping tables, selecting columns, and setting conditions through a graphical interface.<\/p>\n\n\n\n<p>With the advent of Generative AI and LLM visual query builders will lose in importance. Users will use text to SQL to create an initial draft of the SQL and then refine the query further.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><a id=\"post-26221-_l0oql4z58ebo\"><\/a>Example<\/h4>\n\n\n\n<p>Think of graphical query builders where users don\u2019t need to know SQL but can construct complex queries by pointing and clicking.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Use Case<\/h4>\n\n\n\n<p>Visual query building is helpful for non-technical users or when simplifying complex query building. However, I expect it to be replaced by text to SQL using GenAI.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Visual SQL Query Builder Tools<\/h4>\n\n\n\n<p>Visual SQL builders and SQL query editors can be integrated into bigger software packages or be standalone. Some of them are commercial, other visual SQL tools are open source or free.<\/p>\n\n\n\n<p>have come across visual SQL builders in the following type of tools<\/p>\n\n\n\n<p>In <strong>ETL tools<\/strong> a visual editor is used to create data mappings and data flows. I have written elsewhere on this website about how these <a href=\"https:\/\/sonra.io\/to-hell-and-back-with-etl-the-unstoppable-rise-of-data-warehouse-automation\/\" target=\"_blank\" rel=\"noreferrer noopener\">point and click editors reduce productivity<\/a> and are slower than manually writing code. One of the first ETL tools that let you build SQL visually was Oracle Warehouse Builder.<\/p>\n\n\n\n<figure class=\"wp-block-image\" data-hd-variant-url=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/data-structure-diagram-showing-products-total-grou.png\"><span class=\"m-posts__contentImg\"><img decoding=\"async\" width=\"513\" height=\"471\" src=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/data-structure-diagram-showing-products-total-grou.png\" alt=\"data structure diagram showing products total groups and attributes\" class=\"wp-image-26226\" srcset=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/data-structure-diagram-showing-products-total-grou.png 513w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/data-structure-diagram-showing-products-total-grou-300x275.png 300w\" sizes=\"(max-width: 513px) 100vw, 513px\" \/><\/span><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p><strong>SQL IDEs<\/strong> such as DBeaver.<\/p>\n\n\n\n<p>Some <strong>databases<\/strong> ship with visual SQL editors. The first database I worked with was MS Access and I faintly remember that it had some sort of visual SQL builder.<\/p>\n\n\n\n<figure class=\"wp-block-image\" data-hd-variant-url=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/database-query-interface-showing-multiple-related.png\"><span class=\"m-posts__contentImg\"><img decoding=\"async\" width=\"803\" height=\"480\" src=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/database-query-interface-showing-multiple-related.png\" alt=\"database query interface showing multiple related tables and fields\" class=\"wp-image-26227\" srcset=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/database-query-interface-showing-multiple-related.png 803w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/database-query-interface-showing-multiple-related-300x179.png 300w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/database-query-interface-showing-multiple-related-768x459.png 768w\" sizes=\"(max-width: 803px) 100vw, 803px\" \/><\/span><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>A visual SQL builder in <strong>BI tools<\/strong> such as Tableau lets users create SQL queries without writing code by using a drag-and-drop interface to select tables, fields, and conditions. The generated query is then pushed to the database and the result is returned to the BI tool. The first ad-hoc query tool I worked with was Business Objects WebIntelligence (Webi).<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><a id=\"post-26221-_7b3124m438nl\"><\/a>Visual SQL aids for SQL learners and beginners<\/h3>\n\n\n\n<h4 class=\"wp-block-heading\"><strong>What it means<\/strong><\/h4>\n\n\n\n<p>Visual SQL aids break down the SQL query visually. They process the query in stages, showing how different parts of the SQL statement (like SELECT, WHERE, and JOIN clauses) affect the data.<\/p>\n\n\n\n<p>They highlight rows being kept or discarded at each query execution step, making the query&#8217;s logic clear.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><strong>Example<\/strong><\/h4>\n\n\n\n<p>You can follow a single row from an input table as it is transformed, filtered, and aggregated into the final output.<\/p>\n\n\n\n<p>An alternative approach processes the query row by row and checks each row against conditions, such as those in a WHERE clause. If the condition is TRUE, that row is added to the output.<\/p>\n\n\n\n<figure class=\"wp-block-image\" data-hd-variant-url=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/sql-query-interface-displaying-movie-data-and-filt.png\"><span class=\"m-posts__contentImg\"><img decoding=\"async\" width=\"1999\" height=\"823\" src=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/sql-query-interface-displaying-movie-data-and-filt.png\" alt=\"SQL query interface displaying movie data and filtering options\" class=\"wp-image-26228\" srcset=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/sql-query-interface-displaying-movie-data-and-filt.png 1999w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/sql-query-interface-displaying-movie-data-and-filt-300x124.png 300w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/sql-query-interface-displaying-movie-data-and-filt-1024x422.png 1024w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/sql-query-interface-displaying-movie-data-and-filt-768x316.png 768w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/sql-query-interface-displaying-movie-data-and-filt-1536x632.png 1536w\" sizes=\"(max-width: 1999px) 100vw, 1999px\" \/><\/span><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><strong>Use Case<\/strong><\/h4>\n\n\n\n<p>Learn SQL visually<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Visual SQL learning tools<\/h4>\n\n\n\n<p>I have listed three tools<\/p>\n\n\n\n<p><a href=\"https:\/\/animatesql.com\/\" target=\"_blank\" rel=\"noreferrer noopener\">Anikate SQL<\/a><\/p>\n\n\n\n<p><a href=\"https:\/\/rucs-uoft.github.io\/software-projects\/SQL-visualizer\" target=\"_blank\" rel=\"noreferrer noopener\">SQL Visualizer<\/a><\/p>\n\n\n\n<p><a href=\"https:\/\/dl.acm.org\/doi\/10.1145\/2047594.2047641\" target=\"_blank\" rel=\"noreferrer noopener\">SAVI<\/a><\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><a id=\"post-26221-_9dhy65nbulyz\"><\/a>Visualising Joins in a Query as a Venn Diagram<\/h3>\n\n\n\n<h4 class=\"wp-block-heading\"><a id=\"post-26221-_s73exr64m5b1\"><\/a>What it means<\/h4>\n\n\n\n<p>Visualising SQL joins in a Venn diagrams is somewhat related to visual aids for learning SQL.<\/p>\n\n\n\n<p>A Venn diagram for SQL joins visually explains the relationships between two or more tables when performing different types of SQL joins (INNER, LEFT, RIGHT, FULL). Each circle in the Venn diagram represents a table, and the overlapping sections show how the join conditions select data from these tables.<\/p>\n\n\n\n<p><strong>INNER JOIN<\/strong>: Shows only the intersection (common data between the table).<\/p>\n\n\n\n<p><strong>LEFT JOIN<\/strong>: Shows all data from the left table, plus the intersection.<\/p>\n\n\n\n<p><strong>RIGHT JOIN<\/strong>: Shows all data from the right table, plus the intersection.<\/p>\n\n\n\n<p><strong>FULL JOIN<\/strong>: Shows all data from both tables, with or without intersection.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><strong>Example<\/strong><\/h4>\n\n\n\n<p>SQL Joins Visualiser<\/p>\n\n\n\n<figure class=\"wp-block-image\"><span class=\"m-posts__contentImg\"><img decoding=\"async\" width=\"960\" height=\"761\" src=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/sql-joins-visualizer-with-venn-diagram-and-sql-cod.png\" alt=\"SQL joins visualizer with Venn diagram and SQL code example displayed\" class=\"wp-image-26229\" srcset=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/sql-joins-visualizer-with-venn-diagram-and-sql-cod.png 960w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/sql-joins-visualizer-with-venn-diagram-and-sql-cod-300x238.png 300w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/sql-joins-visualizer-with-venn-diagram-and-sql-cod-768x609.png 768w\" sizes=\"(max-width: 960px) 100vw, 960px\" \/><\/span><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>An alternative approach is to visualise SQL joins as a<a href=\"https:\/\/github.com\/amartinson193\/SQL_Checkered_Flag_Join_Diagrams\/blob\/main\/checkered_flag_diagram_pg1.png\" target=\"_blank\" rel=\"noreferrer noopener\"> chequered flag diagram<\/a><\/p>\n\n\n\n<figure class=\"wp-block-image is-resized\"><span class=\"m-posts__contentImg\"><img decoding=\"async\" width=\"1414\" height=\"2000\" src=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/diagram-illustrating-sql-joins-including-inner-lef.jpeg\" alt=\"Diagram illustrating SQL joins including inner left right and full outer joins\" class=\"wp-image-26230\" style=\"width:680px;height:auto\" srcset=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/diagram-illustrating-sql-joins-including-inner-lef.jpeg 1414w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/diagram-illustrating-sql-joins-including-inner-lef-212x300.jpeg 212w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/diagram-illustrating-sql-joins-including-inner-lef-724x1024.jpeg 724w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/diagram-illustrating-sql-joins-including-inner-lef-768x1086.jpeg 768w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/diagram-illustrating-sql-joins-including-inner-lef-1086x1536.jpeg 1086w\" sizes=\"(max-width: 1414px) 100vw, 1414px\" \/><\/span><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><a id=\"post-26221-_q809ipu7tuv7\"><\/a><strong>Use Case<\/strong><\/h4>\n\n\n\n<p>Helpful for people learning <strong>SQL joins<\/strong> or when trying to conceptualise how different tables are merged. It is pretty basic stuff though.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><a id=\"post-26221-_502akie04th9\"><\/a>SQL join visualisation tool<\/h4>\n\n\n\n<p>The <a href=\"https:\/\/sql-joins.leopard.in.ua\/\" target=\"_blank\" rel=\"noreferrer noopener\">SQL join visualiser<\/a> translates the Venn diagram to an SQL query.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><a id=\"post-26221-_ifwtdmsq3j8s\"><\/a>Visualising the SQL parse tree<\/h3>\n\n\n\n<h4 class=\"wp-block-heading\"><a id=\"post-26221-_l60euwyckzc1\"><\/a>What it means<\/h4>\n\n\n\n<p>SQL queries can be broken down into an <a href=\"https:\/\/en.wikipedia.org\/wiki\/Abstract_syntax_tree\" target=\"_blank\" rel=\"noreferrer noopener\">AST (Abstract Syntax Tree)<\/a>, a structured representation of how the query is interpreted by the database.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><a id=\"post-26221-_98x008nqea8p\"><\/a>Example<\/h4>\n\n\n\n<p>A visual tool might show the components of an SQL query (SELECT, FROM, WHERE, etc.) in a hierarchical tree format, allowing you to see how the database parses it.<\/p>\n\n\n\n<p>Let\u2019s take a simple SQL example<\/p>\n\n\n<pre class=\"lang:default decode:true \">SELECT name, age FROM employees WHERE age &gt; 30;<\/pre>\n\n\n<p>This can be represented as an Abstract Syntax Tree<\/p>\n\n\n<pre class=\"lang:default decode:true\">SELECT\n \u251c\u2500\u2500 Columns\n \u2502   \u251c\u2500\u2500 name\n \u2502   \u2514\u2500\u2500 age\n \u251c\u2500\u2500 FROM\n \u2502   \u2514\u2500\u2500 employees\n \u2514\u2500\u2500 WHERE\n     \u2514\u2500\u2500 Condition\n         \u251c\u2500\u2500 Column: age\n         \u251c\u2500\u2500 Operator: &gt;\n         \u2514\u2500\u2500 Value: 30<\/pre>\n\n\n<p>An <a href=\"https:\/\/sonra.io\/sql-parser-use-cases\/\">SQL parser<\/a> can output this as an XML or JSON tree.<\/p>\n\n\n<pre class=\"lang:default decode:true\">&lt;?xml version=\"1.0\" encoding=\"UTF-8\"?&gt;&lt;parSeQL xmlns:xsi=\"http:\/\/www.w3.org\/2001\/XMLSchema-instance\" version=\"1.0\" status=\"OK\" ts=\"2024-10-14T07:13:32.390Z\" xsi:schemaLocation=\"https:\/\/flowhigh.sonra.io\/flowhigh_v1.2.xsd\"&gt;\n  &lt;statements&gt;\n    &lt;statement pos=\"0-46\" cluster_top_Hi_ID=\"-9150035226786510270\" cluster_top_Lo_ID=\"-603914746900942594\" cluster_logical_ID=\"1536843882837981305\" cluster_raw_ID=\"-8046686254704789258\"&gt;\n      &lt;ds pos=\"0-46\" type=\"root\" subType=\"inline\"&gt;\n        &lt;out&gt;\n          &lt;attr pos=\"7-4\" oref=\"C1\"\/&gt;\n          &lt;attr pos=\"13-3\" oref=\"C2\"\/&gt;\n        &lt;\/out&gt;\n        &lt;in&gt;\n          &lt;ds pos=\"22-9\" oref=\"T1\"\/&gt;\n        &lt;\/in&gt;\n        &lt;filter xsi:type=\"filtreg\"&gt;\n          &lt;op pos=\"38-8\" type=\"GT\"&gt;\n            &lt;attr pos=\"38-3\" oref=\"C2\"\/&gt;\n            &lt;const&gt;30&lt;\/const&gt;\n          &lt;\/op&gt;\n        &lt;\/filter&gt;\n      &lt;\/ds&gt;\n    &lt;\/statement&gt;\n  &lt;\/statements&gt;\n  &lt;DBOHier&gt;\n    &lt;dbo oid=\"T1\" type=\"TABLE\" name=\"EMPLOYEES\"&gt;\n      &lt;dbo oid=\"C1\" type=\"COLUMN\" name=\"NAME\"\/&gt;\n      &lt;dbo oid=\"C2\" type=\"COLUMN\" name=\"AGE\"\/&gt;\n    &lt;\/dbo&gt;\n  &lt;\/DBOHier&gt;\n&lt;\/parSeQL&gt;<\/pre>\n\n\n<p><em>Parsed SQL output from <\/em><a href=\"https:\/\/flowhigh.io\/parse-sql\" target=\"_blank\" rel=\"noreferrer noopener\"><em>FlowHigh SQL parser<\/em><\/a><em>.<\/em><\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><a id=\"post-26221-_pprjhscn2skp\"><\/a>Use Case<\/h4>\n\n\n\n<p>I have covered the use cases of an SQL parser extensively in a separate post. Here is a quick summary of the main use cases.<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Data Governance<\/strong>: Assisting with data lineage, cataloging, business rule extraction, and security auditing.<\/li>\n\n\n\n<li><strong>Data Engineering<\/strong>: SQL query formatting, refactoring, performance tuning, and supporting database migrations.<\/li>\n\n\n\n<li><strong>SQL Code Visualization<\/strong>: Documenting and debugging SQL.<\/li>\n\n\n\n<li><strong>Audit Logging<\/strong>: Tracking database usage, auditing table and column access.<\/li>\n\n\n\n<li><strong>Query Optimization<\/strong>: Identifying inefficiencies and automating impact analysis.<\/li>\n\n\n\n<li><strong>SQL Migration<\/strong>: Supporting cross-dialect SQL conversions.<\/li>\n<\/ul>\n\n\n\n<h4 class=\"wp-block-heading\"><a id=\"post-26221-_5dptt2mgvfhe\"><\/a>Tool<\/h4>\n\n\n\n<p>SQL Parser <a href=\"https:\/\/sonra.io\/sql-parser-use-cases\/\" target=\"_blank\" rel=\"noreferrer noopener\">FlowHigh SQL parser<\/a>.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><span class=\"m-posts__contentImg\"><img decoding=\"async\" width=\"823\" height=\"404\" src=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/sql-query-to-select-names-and-ages-of-employees-ov.png\" alt=\"SQL query to select names and ages of employees over thirty\" class=\"wp-image-26231\" srcset=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/sql-query-to-select-names-and-ages-of-employees-ov.png 823w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/sql-query-to-select-names-and-ages-of-employees-ov-300x147.png 300w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/sql-query-to-select-names-and-ages-of-employees-ov-768x377.png 768w\" sizes=\"(max-width: 823px) 100vw, 823px\" \/><\/span><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p><em>SQL visualisation of the query using FlowHigh SQL Visualiser<\/em><\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><a id=\"post-26221-_tikocqs99ixr\"><\/a>SQL Visualisation of Query Output in Charts, Graphs, and Dashboards<\/h3>\n\n\n\n<h4 class=\"wp-block-heading\"><a id=\"post-26221-_b9hn6tyzhqe6\"><\/a><br>What is SQL data visualisation?<\/h4>\n\n\n\n<p>Instead of just seeing the raw data you can take the results of an SQL query and visualise the output charts, graphs, and dashboards. This makes the output easier to understand.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><a id=\"post-26221-_ceig3f8bn891\"><\/a>Example<\/h4>\n\n\n\n<p>Running a query that returns sales data and having the output visualised as a bar chart or pie chart directly.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><a id=\"post-26221-_21gbknakj4xj\"><\/a>Use Case SQL visualisation<\/h4>\n\n\n\n<p>You can make SQL data easier to understand by turning it into visual charts. Charts help you spot patterns, trends, and issues in your data. They also make it easier to keep an eye on performance metrics over time. With visual data, you and your team can make faster decisions because the information is clearer. Charts help you compare data across different categories or time periods and are a great way to present your findings in reports or meetings.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><a id=\"post-26221-_nvo3nb90xa9e\"><\/a>SQL visualisation tools<\/h4>\n\n\n\n<p>SQL data visualisation tools have been around the block for a very long time. The first tool I used was Crystal Reports. It was very clunky compared to the tools of today such as PowerBI or Tableau. The first modern BI tool I used was Web Intelligence (Webi) from Business Objects. This French company popularised many of the innovations around ad hoc querying and semantic layers.<\/p>\n\n\n\n<p>A semantic layer in SQL visualisation acts as an intermediary between the raw data and the end user. It abstracts the complexity of the database, such as SQL tables, joins, or database schemas, and translates them into terms familiar to business users. A BI tool uses the semantic layer and takes what business users do, like choosing filters or options, and turns those actions into SQL queries to get the right data from the database.<\/p>\n\n\n\n<p>Another popular tool I used was Oracle Business Intelligence (OBI). Nowadays, many tools are on the market including open source tools such as <a href=\"https:\/\/superset.apache.org\/\" target=\"_blank\" rel=\"noreferrer noopener\">Superset<\/a>.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><a id=\"post-26221-_z2sqwmqpm0c8\"><\/a>Visualising Databases &#8211; SQL ER Diagrams<\/h3>\n\n\n\n<h4 class=\"wp-block-heading\"><a id=\"post-26221-_t8sddt4fick6\"><\/a>What it means: SQL ER diagram<\/h4>\n\n\n\n<p>An SQL Entity Relationship Diagram (ERD or ER Diagram) can mean two things. (1) You can create an ER Diagram from the tables in a database schema that visualises the relationships between the tables. (2) You can also go the other way and turn the ER diagram to SQL code.<\/p>\n\n\n<div class=\"note-block\">\n\t<div class=\"note-block-icon\"><\/div>\n    \t<h4>Breaking Down SQL Subtypes: DDL and Its Role in ER Diagrams<\/h4>\n    \t<div class=\"note-block-content\"><p>The technically correct term for turning SQL into an ER (Entity-Relationship) diagram is reverse engineering the SQL code. The opposite operation of turning an ER diagram into SQL code is called forward engineering. SQL in the context of reverse and forward engineering is more specifically referring to DDL (Data Defintion Language), DDL defines the structure of the database, including creating tables, defining columns, and setting relationships. This structure can then be converted into an ER diagram.<\/p>\n<p>SQL itself is broader and includes other subtypes like:<\/p>\n<p>DQL (Data Query Language): For querying data.<\/p>\n<p>DML (Data Manipulation Language): For modifying data.<\/p>\n<p>DDL: For defining or altering database structures.<\/p>\n<\/div>\n<\/div>\n\n\n<p><strong>SQL to ER Diagram (Reverse Engineering)<\/strong><\/p>\n\n\n\n<p>When you go from SQL to an ER diagram visualisation, it\u2019s called reverse engineering. This means taking existing SQL code, which defines the tables and relationships (through DDL), and turning it into a visual ER diagram. This helps you understand how the database is structured.<\/p>\n\n\n\n<p><strong>ER Diagram to SQL (Forward Engineering)<\/strong><\/p>\n\n\n\n<p>Going from an ER diagram to SQL is called forward engineering. You model the ER diagram in your data modelling tool. Forward engineering generates the necessary SQL code (DDL) to create the database\u2019s tables, columns, and relationships based on the data model diagram.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><a id=\"post-26221-_btun46z4cqf5\"><\/a>Example<\/h4>\n\n\n\n<p>A tool might generate an ER diagram that shows tables as boxes, with arrows representing relationships (foreign keys) between them.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><a id=\"post-26221-_8p5ix7r7ts8\"><\/a>Use Case<\/h4>\n\n\n\n<p>ER diagrams are widely used in <strong>database design and documentation<\/strong> to understand the structure of a database at a glance.<\/p>\n\n\n\n<p>The purpose of an ER diagram includes:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Visualising Database Structure: Clearly shows how tables are related to each other through their Foreign Key constraints.<\/li>\n\n\n\n<li>Improving Productivity: Allows users to visually model the database, making it faster and easier to design and modify.<\/li>\n\n\n\n<li>Effective Communication: The ER diagram can be used as an effective communication tool between data engineers, data architects and data modellers.<\/li>\n<\/ul>\n\n\n\n<h4 class=\"wp-block-heading\"><a id=\"post-26221-_cl209zocjf6q\"><\/a>Data modelling tools for SQL ER diagrams<\/h4>\n\n\n\n<p>You create an ER diagram with a data modelling tool. Personally I have used various enterprise level data modelling tools such as PowerDesigner, Erwin or ER Studio. They all do the job but you need to make sure that the database you are using is supported by the tool.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\"><a id=\"post-26221-_bs1244jfb6b\"><\/a>Benefits and use cases of visualising SQL code<\/h2>\n\n\n\n<p>We have covered the various meanings of SQL visualisation. Let\u2019s now focus on the main topic of this article and the benefits of creating a visual representation of SQL code in a diagram.<\/p>\n\n\n\n<p>SQL may seem straightforward with its simple syntax and limited set of commands, but it opens the door to creating highly complex queries that can be hard to master. As one of the first declarative languages that engineers learn, SQL presents a challenge\u2014its logic requires a different thought process compared to the more familiar procedural or object-oriented programming paradigms. SQL forces you to focus on what you want from the data, rather than how to get it, which is a significant shift in thinking for many. With SQL, engineers need to think in sets of data rather than procedural steps.<\/p>\n\n\n\n<p><strong>SQL is deceptively simple<\/strong><\/p>\n\n\n\n<p>The deceptive simplicity of SQL often leads to spaghetti code\u2014an entangled mess of unnecessary nesting, redundant joins, over-reliance on views and incorrect logic. What starts as a straightforward query can quickly snowball into a labyrinth of convoluted code that&#8217;s hard to untangle. I&#8217;ve collected and documented some of the <a href=\"https:\/\/sonra.io\/mastering-sql-how-to-detect-and-avoid-34-common-sql-antipatterns\/\" target=\"_blank\" rel=\"noreferrer noopener\">most common SQL antipatterns<\/a>, which barely scratch the surface of the problem, yet give a glimpse into how easily things can spiral out of control.<\/p>\n\n\n\n<p><strong>Query reading versus query writing<\/strong><\/p>\n\n\n\n<p>Query interpretation\u2014deciphering an existing query\u2014is often as challenging as writing a new one. Much like understanding code in any programming language, interpreting a query involves understanding the original author&#8217;s logic, making sense of table relationships, and remembering table schemas and aliases. This skill requires a thorough knowledge of SQL and often serves as a testing ground for one&#8217;s comprehension of database logic. It\u2019s not just about piecing together lines of code; it\u2019s about reconstructing the original thinking behind them, making it a core part of the development process. Limited error detection and debugging in SQL systems makes writing effective SQL queries even more challenging.<\/p>\n\n\n\n<p><strong>The need for SQL visualisers for AI-generated queries<\/strong><\/p>\n\n\n\n<p>I have started using Large Language Models (LLMs) to generate SQL &#8220;starter code,&#8221; but I still need to review the generated queries for accuracy. Some experts predict that, in the future, AI will handle all SQL writing, with humans like us taking on more of a supervisory role. However, understanding and modifying AI-generated queries will remain a key skill. SQL is no exception\u2014reading and interpreting queries can be just as difficult as writing them from scratch.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\" data-hd-variant-url=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/Diagram-of-SQL-Query-Life-Cycle-showing-Text2SQL-composition-and-visualisation-process.png\"><span class=\"m-posts__contentImg\"><img decoding=\"async\" width=\"1024\" height=\"412\" src=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/Diagram-of-SQL-Query-Life-Cycle-showing-Text2SQL-composition-and-visualisation-process-1024x412.png\" alt=\"\" class=\"wp-image-26281\" srcset=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/Diagram-of-SQL-Query-Life-Cycle-showing-Text2SQL-composition-and-visualisation-process-1024x412.png 1024w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/Diagram-of-SQL-Query-Life-Cycle-showing-Text2SQL-composition-and-visualisation-process-300x121.png 300w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/Diagram-of-SQL-Query-Life-Cycle-showing-Text2SQL-composition-and-visualisation-process-768x309.png 768w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/Diagram-of-SQL-Query-Life-Cycle-showing-Text2SQL-composition-and-visualisation-process-1536x618.png 1536w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/Diagram-of-SQL-Query-Life-Cycle-showing-Text2SQL-composition-and-visualisation-process-2048x825.png 2048w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/span><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>Even though it might be easier to modify a generated query than to write one from the ground up, users still need a solid understanding of the query to make effective changes. Tools help users comprehend written queries. Query visualisation is a promising solution. It makes complex SQL easier to grasp.<\/p>\n\n\n\n<p><strong>SQL visualisers versus Visual SQL query builders <\/strong><\/p>\n\n\n\n<p>While there has been a lot of research on visual methods for creating queries (often referred to as Visual Query Languages, or VQLs), the task of visualising existing queries poses different challenges. Automatically generating a visual representation of a query is quite different from building a new one from scratch in a visual SQL builder tool.<\/p>\n\n\n\n<p><strong>Query intent<\/strong><\/p>\n\n\n\n<p>Another issue is query semantics, the actual meaning and intent behind the SQL. Even for experienced developers, reading and understanding SQL queries is often a daunting task, especially when dealing with queries written by someone else. Tasks like code maintenance, modification, or reuse all require deep comprehension of these existing queries.<\/p>\n\n\n\n<p>I&#8217;ve pulled together a comprehensive list of all the benefits, use cases, and compelling reasons why you should be visualising your SQL queries.<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Improved Query Comprehension<\/strong>: Complex queries, especially those involving multiple joins, subqueries, or nested operations, can be hard to interpret in raw code form. Visualisations break down the logic, making it easier to understand relationships between tables and operations.<\/li>\n\n\n\n<li><strong>AI-generated queries<\/strong>: SQL visualisation can help in reviewing and validating SQL generated by LLMs.<\/li>\n\n\n\n<li><strong>Simplifies Complex Query Interpretation<\/strong>: SQL queries, particularly those written by others or involving nested or correlated subqueries, can be hard to interpret. Visualizing these queries provides users with a clearer understanding, allowing them to focus on the structure and relationships within the query without getting lost in the syntax.<\/li>\n\n\n\n<li><strong>Assists in Recognizing Query Patterns<\/strong>: Humans are generally better at recognizing patterns visually than parsing them from text. By converting SQL queries into visual forms, users can easily spot familiar patterns, such as joins or subqueries, and understand their relationships.<\/li>\n\n\n\n<li><strong>Faster Debugging<\/strong>: By representing SQL queries graphically, developers can more easily spot errors, bottlenecks, or inefficiencies in the query logic.<\/li>\n\n\n\n<li><strong>Better Collaboration<\/strong>: Visual diagrams help teams discuss and collaborate on query design, especially for non-techies.<\/li>\n\n\n\n<li><strong>Enhanced Performance Tuning<\/strong>: Identifying performance issues, such as redundant joins or expensive operations, becomes easier when queries are visualised, aiding in optimisation efforts.<\/li>\n\n\n\n<li><strong>Educational Aid<\/strong>: It serves as a great tool for learning SQL, as visualising the operations helps beginners better understand how different SQL clauses interact.<\/li>\n\n\n\n<li><strong>Reducing Complexity<\/strong>: Large, nested queries can be broken into simpler visual steps.<\/li>\n\n\n\n<li><strong>Clearer Relationships<\/strong>: Easily spot table relationships and joins between tables.<\/li>\n\n\n\n<li><strong>Documentation<\/strong>: Visuals provide an intuitive way to document query structures.<\/li>\n\n\n\n<li><strong>Onboarding<\/strong>: Helps new team members understand existing queries faster. From my own experience this also applies to code I have written in the past. It always amazes and puzzles why I would have written a piece of SQL code the way I did. I always find a way to improve it.<\/li>\n\n\n\n<li><strong>Bridges the Gap Between Composition and Interpretation<\/strong>: Writing SQL queries is already a challenge, but understanding them\u2014especially when written by others\u2014can be even more difficult. Visualization tools like QueryViz alleviate this problem by simplifying the interpretation process, thus helping users understand the logic behind complex queries more efficiently.<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\"><a id=\"post-26221-_jpw9f6ugdw7v\"><\/a>The top methods for visualising SQL<\/h2>\n\n\n\n<p>There are different methods and approaches to visualising SQL. Let\u2019s check the most common approaches and the thinking behind them.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><a id=\"post-26221-_g3swshinkt3k\"><\/a>Visualise SQL operations as a dataflow diagram<\/h3>\n\n\n\n<p>The first time I came across visualisation of SQL code was in the excellent book <a href=\"https:\/\/onlinelibrary.wiley.com\/doi\/book\/10.1002\/9781119183419\" target=\"_blank\" rel=\"noreferrer noopener\">Data Analysis Using SQL and Excel<\/a> by Gordon Linoff. The first edition is from 2008, and it&#8217;s one of the few physical books I still keep on my shelf, having switched to eBooks many years ago.<\/p>\n\n\n\n<div class=\"wp-block-media-text is-stacked-on-mobile\"><figure class=\"wp-block-media-text__media\"><span class=\"m-posts__contentImg\"><img decoding=\"async\" width=\"554\" height=\"706\" src=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/book-cover-for-data-analysis-using-sql-and-excel-b.jpeg\" alt=\"Book cover for Data Analysis Using SQL and Excel by Gordon S Linoff\" class=\"wp-image-26233 size-full\" srcset=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/book-cover-for-data-analysis-using-sql-and-excel-b.jpeg 554w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/book-cover-for-data-analysis-using-sql-and-excel-b-235x300.jpeg 235w\" sizes=\"(max-width: 554px) 100vw, 554px\" \/><\/span><\/figure><div class=\"wp-block-media-text__content\">\n<p>Gordon uses an operator based approach for visualising SQL. This visualisation focuses on SQL operators, highlighting key actions like:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Joins between tables, which show how data from different tables is combined.<\/li>\n\n\n\n<li>Set-based operations such as UNION, INTERSECT, or MINUS, which illustrate how datasets are merged or compared.<\/li>\n\n\n\n<li>Aggregation functions (e.g., SUM, COUNT, AVG), which summarise data across multiple rows or tables.<\/li>\n<\/ul>\n\n\n\n<p>In his book Gordon connects SQL and Excel to show how they can be used together for data analysis, transformation, and presentation. Though SQL and Excel often perform similar operations, they do so in very different ways. For example, SQL uses GROUP BY to summarise data, while Excel achieves similar results using pivot tables or functions like SUMIF().<\/p>\n<\/div><\/div>\n\n\n\n<p>To combine these tools, Gordon introduces what he calls dataflows as a common way to express data transformations independent of tooling and technology. A dataflow is a graphical representation showing how data moves and changes, much like an architectural blueprint for data processing. Dataflows consist of nodes (which transform the data) and edges (which represent the pipes through which data flows). As data moves through these pipes, it gets manipulated and transformed, eventually becoming useful information.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><span class=\"m-posts__contentImg\"><img decoding=\"async\" width=\"567\" height=\"261\" src=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/data-processing-flowchart-with-tables-showing-zcta.png\" alt=\"data processing flowchart with tables showing zcta5 stab and totpop\" class=\"wp-image-26234\" srcset=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/data-processing-flowchart-with-tables-showing-zcta.png 567w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/data-processing-flowchart-with-tables-showing-zcta-300x138.png 300w\" sizes=\"(max-width: 567px) 100vw, 567px\" \/><\/span><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>In essence, dataflows provide a visual and tool-independent way to think about how data gets processed, making them a great tool for communicating how data flows across different platforms like SQL and Excel.<\/p>\n\n\n\n<p>Gordon\u2019s approach focuses on the operations of the query (or verbs and actions) as he calls them rather than the structure represented by tables and inline views.<\/p>\n\n\n\n<p>He outlines the following operations that can be used for creating a dataflow diagram:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>READ<\/strong>: Extracts all columns from a database table.<\/li>\n\n\n\n<li><strong>OUTPUT<\/strong>: Creates output in table or chart format.<\/li>\n\n\n\n<li><strong>SELECT<\/strong>: Chooses specific columns to pass along.<\/li>\n\n\n\n<li><strong>FILTER<\/strong>: Filters rows based on conditions.<\/li>\n\n\n\n<li><strong>APPEND<\/strong>: Adds new calculated columns.<\/li>\n\n\n\n<li><strong>UNION<\/strong>: Merges multiple datasets into one.<\/li>\n\n\n\n<li><strong>AGGREGATE<\/strong>: Summarises data by groups.<\/li>\n\n\n\n<li><strong>LOOKUP<\/strong>: Matches and appends columns from another table.<\/li>\n\n\n\n<li><strong>CROSSJOIN<\/strong>: Creates Cartesian product of two tables.<\/li>\n\n\n\n<li><strong>JOIN<\/strong>: Combines two tables by a key.<\/li>\n\n\n\n<li><strong>SORT<\/strong>: Orders data based on specified keys.<\/li>\n<\/ul>\n\n\n\n<p>Here is an example from the book. The following SQL statement translates to the dataflow diagram.<\/p>\n\n\n<pre class=\"lang:default decode:true\">SELECT toscore.*\n     ,COALESCE(statelu.avgamount\n         ,defaultlu.avgamount) AS predamount\n FROM (SELECT o.*\n         FROM Orders o\n        WHERE YEAR(o.OrderDate)=2016) toscore\n LEFT OUTER\n JOIN (SELECT o.State\n             ,AVG(o.TotalPrice) AS avgamount\n         FROM Orders o\n        WHERE YEAR(o.OrderDate)=2015\n        GROUP BY o.state) statelu\n   ON o.State=statelu.State\nCROSS\n JOIN (SELECT AVG(o.TotalPrice) AS avgamount\n         FROM Orders o\n        WHERE YEAR(o.OrderDate)=2015) defaultlu<\/pre>\n\n\n<figure class=\"wp-block-image\"><span class=\"m-posts__contentImg\"><img decoding=\"async\" width=\"763\" height=\"553\" src=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/flowchart-of-data-processing-with-filtering-joinin.png\" alt=\"flowchart of data processing with filtering joining and aggregating orders\" class=\"wp-image-26235\" srcset=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/flowchart-of-data-processing-with-filtering-joinin.png 763w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/flowchart-of-data-processing-with-filtering-joinin-300x217.png 300w\" sizes=\"(max-width: 763px) 100vw, 763px\" \/><\/span><\/figure>\n\n\n\n<p>&nbsp;<\/p>\n\n\n\n<p><strong>Automating SQL visualisation<\/strong><\/p>\n\n\n\n<p>Gordon Linoff uses the proverbial pen and paper to draft up the SQL data flow diagram using Visio or a similar workflow tool. We created <a href=\"https:\/\/flowhigh.io\/visualise-sql\" target=\"_blank\" rel=\"noreferrer noopener\">FlowHigh SQL Visualiser<\/a> to automate the process.<\/p>\n\n\n\n<figure class=\"wp-block-image\" data-hd-variant-url=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/sql-query-showing-inner-join-and-group-by-operatio.png\"><span class=\"m-posts__contentImg\"><img decoding=\"async\" width=\"1010\" height=\"599\" src=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/sql-query-showing-inner-join-and-group-by-operatio.png\" alt=\"sql query showing inner join and group by operations on orders\" class=\"wp-image-26236\" srcset=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/sql-query-showing-inner-join-and-group-by-operatio.png 1010w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/sql-query-showing-inner-join-and-group-by-operatio-300x178.png 300w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/sql-query-showing-inner-join-and-group-by-operatio-768x455.png 768w\" sizes=\"(max-width: 1010px) 100vw, 1010px\" \/><\/span><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><a id=\"post-26221-_ya3tk8jqrar8\"><\/a>Unlocking Query Logic and Intent<\/h3>\n\n\n\n<p>Logic based SQL diagrams are based on the <a href=\"https:\/\/en.wikipedia.org\/wiki\/First-order_logic\" target=\"_blank\" rel=\"noreferrer noopener\">first-order logic<\/a> (FOL) that underlies SQL. The key idea is that these diagrams capture the logical intent of a query\u2014essentially what the query is trying to do\u2014rather than focusing on the low-level syntax details that make SQL challenging to read. A tool that implements logic based SQL diagrams is <a href=\"https:\/\/queryvis.com\/\" target=\"_blank\" rel=\"noreferrer noopener\">QueryVis<\/a>. QueryVis mainly targets experienced SQL users.<\/p>\n\n\n\n<p>The visualisations in QueryVis are simple, showing only what&#8217;s needed, and clear, making sure that queries with different structures but the same semantic meaning are shown in the same way. The QueryVis authors argue that these alternative expressions often complicate the process of interpreting the query, even when their underlying logic is the same.<\/p>\n\n\n\n<p>As long as the patterns are the same they abstract away from syntax details and the following queries would be represented visually the same<\/p>\n\n\n\n<figure class=\"wp-block-image\"><span class=\"m-posts__contentImg\"><img decoding=\"async\" width=\"1262\" height=\"650\" src=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/two-sql-queries-to-find-sailors-without-reservatio.png\" alt=\"Two SQL queries to find sailors without reservations and their logic\" class=\"wp-image-26237\" srcset=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/two-sql-queries-to-find-sailors-without-reservatio.png 1262w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/two-sql-queries-to-find-sailors-without-reservatio-300x155.png 300w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/two-sql-queries-to-find-sailors-without-reservatio-1024x527.png 1024w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/two-sql-queries-to-find-sailors-without-reservatio-768x396.png 768w\" sizes=\"(max-width: 1262px) 100vw, 1262px\" \/><\/span><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p><a href=\"https:\/\/northeastern-datalab.github.io\/visual-query-representation-tutorial\/slides\/VLDB_2023-Visual_Representations_of_Relational_Queries.pdf\" target=\"_blank\" rel=\"noreferrer noopener\">Visual Query Representation Tutorial <\/a><\/p>\n\n\n\n<p>QueryVis video<\/p>\n\n\n\n<figure class=\"wp-block-embed is-type-video is-provider-youtube wp-block-embed-youtube wp-embed-aspect-16-9 wp-has-aspect-ratio\"><div class=\"wp-block-embed__wrapper\">\n<iframe title=\"QueryVis: Logic-Based Diagrams Help Users Understand Complicated SQL Queries Faster\" width=\"640\" height=\"360\" src=\"https:\/\/www.youtube.com\/embed\/ONCGIShkS9E?list=PL_72ERGKF6DTTD6T5oR4WQPuCyHZd7x_N\" frameborder=\"0\" allow=\"accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share\" referrerpolicy=\"strict-origin-when-cross-origin\" allowfullscreen><\/iframe>\n<\/div><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\"><a id=\"post-26221-_4zygnyr9sf1r\"><\/a>SQLVis helps with query writing<\/h3>\n\n\n\n<p>Unlike QueryVis, which focuses on understanding existing SQL queries and targets experienced users, <a href=\"https:\/\/github.com\/AlexOdyn\/sqlvisplus\" target=\"_blank\" rel=\"noreferrer noopener\">SQLVis<\/a> is designed to help SQL learners write new queries. SQLVis uses a method called <a href=\"https:\/\/pure.tue.nl\/ws\/portalfiles\/portal\/191839095\/VLHCC_author_version.pdf\" target=\"_blank\" rel=\"noreferrer noopener\">Visual Query Representations<\/a> (VQR), which shows SQL queries in a visual format and shows the actual SQL code side by side with the query. This approach helps learners better understand how SQL works and makes it easier for them to write accurate and reusable SQL queries.<\/p>\n\n\n\n<figure class=\"wp-block-embed is-type-video is-provider-youtube wp-block-embed-youtube wp-embed-aspect-16-9 wp-has-aspect-ratio\"><div class=\"wp-block-embed__wrapper\">\n<iframe title=\"SQLVis demo video\" width=\"640\" height=\"360\" src=\"https:\/\/www.youtube.com\/embed\/CWeUviYIBiA?feature=oembed\" frameborder=\"0\" allow=\"accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share\" referrerpolicy=\"strict-origin-when-cross-origin\" allowfullscreen><\/iframe>\n<\/div><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\"><a id=\"post-26221-_culphmwi3usf\"><\/a>SQL queries in plain English<\/h3>\n\n\n\n<p>When it comes to SQL, most research focuses on using AI to generate SQL code. However, LLMs and GenAI can also be used to describe the intent of an SQL query.<\/p>\n\n\n\n<p>Rather than being an alternative to SQL query visualisation, textual explanations of SQL can complement the visual aid of SQL visualisers when interpreting SQL and understanding the meaning of SQL code.<\/p>\n\n\n\n<p>Current limitations as of October 2024 are that LLMs produce long sentences, are limited to simple SQL queries, and that textual descriptions do not readily reveal common logical patterns behind queries.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\"><a id=\"post-26221-_t6gy2pi9bd80\"><\/a>SQL Visualiser Flowhigh<\/h2>\n\n\n\n<p>We have gone through a lot of theory and concepts. Let\u2019s see Sonra\u2019s <a href=\"https:\/\/flowhigh.io\/visualise-sql\" target=\"_blank\" rel=\"noreferrer noopener\">FlowHigh SQL Visualiser<\/a> in action. <a href=\"https:\/\/sonra.io\/flowhigh\/\" target=\"_blank\" rel=\"noreferrer noopener\">FlowHigh<\/a> is an online SQL query visualiser.<\/p>\n\n\n\n<p>Let\u2019s start with a simple piece of SQL with a couple of CTEs and some nesting.<\/p>\n\n\n<pre class=\"lang:default decode:true \">WITH f_customer_sales AS (\n    SELECT fs.OrderDateKey\n          ,fs.ProductKey\n          ,fs.OrderQuantity*fs.UnitPrice AS TotalSale\n          ,dc.FirstName\n          ,dc.LastName\n      FROM FactInternetSales fs\n \tINNER\n      JOIN DimCustomer dc\n        ON dc.CustomerKey=fs.CustomerKey)\n,d_date AS (\n    SELECT DateKey\n          ,CalendarYear\n      FROM DimDate\n \tWHERE CalendarYear=2017)\nSELECT CalendarYear\n      ,ProductKey\n      ,SUM(TotalSale) AS TotalSales\n  FROM f_customer_sales\nINNER\n  JOIN d_date\n    ON d_date.DateKey=f_customer_sales.OrderDateKey\nGROUP BY CalendarYear\n      ,ProductKey\nORDER BY CalendarYear ASC\n      ,TotalSales DESC<\/pre>\n\n\n<p>Let\u2019s have a look at the visual diagram that FlowHigh Visualiser generates<\/p>\n\n\n\n<figure class=\"wp-block-image\"><span class=\"m-posts__contentImg\"><img decoding=\"async\" width=\"1396\" height=\"1057\" src=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/flowchart-of-database-tables-and-joins-with-highli.png\" alt=\"flowchart of database tables and joins with highlighted common table expressions\" class=\"wp-image-26238\" srcset=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/flowchart-of-database-tables-and-joins-with-highli.png 1396w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/flowchart-of-database-tables-and-joins-with-highli-300x227.png 300w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/flowchart-of-database-tables-and-joins-with-highli-1024x775.png 1024w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/flowchart-of-database-tables-and-joins-with-highli-768x582.png 768w\" sizes=\"(max-width: 1396px) 100vw, 1396px\" \/><\/span><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>Using the visual diagram of the SQL code we can quickly see that it breaks down into two CTEs.<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>The first CTE f_customer_sales joins the tables FactInternetSales and DimCustomer together.<\/li>\n\n\n\n<li>The second CTE d_date queries the table DimDate and applies a filter on the CalendarYear field.<\/li>\n<\/ul>\n\n\n\n<p>The two CTEs are then joined and an aggregation is applied. In a final step the resultset is sorted and returned to the user.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Table types<\/h3>\n\n\n\n<p>For representing SQL code we have come up with a categorisation for tables.<\/p>\n\n\n\n<p>Each different type gets its own symbol and colour.<\/p>\n\n\n\n<p>Let\u2019s go through them one by one<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><a id=\"post-26221-_f4acgmmbg49l\"><\/a>Physical table<\/h4>\n\n\n\n<p>A physical table is a standard table where the data is persisted and has been created with the CREATE TABLE DDL statement.<\/p>\n\n\n\n<p>The following query references a physical table<\/p>\n\n\n<pre class=\"lang:default decode:true\">SELECT col1\n      ,col2\n  FROM order_item<\/pre>\n\n\n<p>\u2026 and the representation by FlowHigh on the diagram.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><span class=\"m-posts__contentImg\"><img decoding=\"async\" width=\"379\" height=\"220\" src=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/flowchart-showing-order_item-connected-to-select-o.png\" alt=\"flowchart showing order_item connected to select option\" class=\"wp-image-26239\" srcset=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/flowchart-showing-order_item-connected-to-select-o.png 379w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/flowchart-showing-order_item-connected-to-select-o-300x174.png 300w\" sizes=\"(max-width: 379px) 100vw, 379px\" \/><\/span><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><a id=\"post-26221-_kmhofb1naovf\"><\/a>Pseudo table<\/h4>\n\n\n\n<p>Pseudo tables are tables that are created on the fly by hard coding values.<\/p>\n\n\n\n<p>An example<\/p>\n\n\n<pre class=\"lang:default decode:true\">SELECT CAST('2021-05-24' AS DATE) AS QueryDate\nUNION ALL\nSELECT CAST('2021-05-25' AS DATE) AS QueryDate<\/pre>\n\n\n<figure class=\"wp-block-image\"><span class=\"m-posts__contentImg\"><img decoding=\"async\" width=\"543\" height=\"417\" src=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/flowchart-showing-union-all-operation-and-select-o.png\" alt=\"flowchart showing union all operation and select option with inputs PT1 PT2\" class=\"wp-image-26240\" srcset=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/flowchart-showing-union-all-operation-and-select-o.png 543w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/flowchart-showing-union-all-operation-and-select-o-300x230.png 300w\" sizes=\"(max-width: 543px) 100vw, 543px\" \/><\/span><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><a id=\"post-26221-_rhpiq5ems9hz\"><\/a>Common Table Expressions (CTE)<\/h4>\n\n\n\n<p>We have already seen CTEs in action in our first example. CTEs are reusable snippets of SQL that can be nested and referenced in other components of the same SQL statement.<\/p>\n\n\n\n<p>There is one CTE visualisation feature I did not mention. Sometimes developers write a CTE and then don\u2019t use it.<\/p>\n\n\n\n<p>Here is an example<\/p>\n\n\n<pre class=\"lang:default decode:true\">WITH f_customer_sales AS (\n    SELECT fs.OrderDateKey\n          ,fs.ProductKey\n          ,fs.OrderQuantity*fs.UnitPrice AS TotalSale\n          ,dc.FirstName\n          ,dc.LastName\n      FROM FactInternetSales fs\n \tINNER\n      JOIN DimCustomer dc\n        ON dc.CustomerKey=fs.CustomerKey)\n,d_date AS (\n    SELECT DateKey\n          ,CalendarYear\n      FROM DimDate\n \tWHERE CalendarYear=2017)\n,d_geo AS (\n    SELECT geo_nm\n      FROM geography)\nSELECT CalendarYear\n      ,ProductKey\n      ,SUM(TotalSale) AS TotalSales\n  FROM f_customer_sales\nINNER\n  JOIN d_date\n    ON d_date.DateKey=f_customer_sales.OrderDateKey\nGROUP BY CalendarYear\n      ,ProductKey\nORDER BY CalendarYear ASC\n      ,TotalSales DESC<\/pre>\n\n\n<p>We have added a third CTE d_geo to the SQL. However, this is not referenced anywhere else.<\/p>\n\n\n\n<p>We can see that this results in an orphaned component on the diagram which is not connected to any other parts of the SQL. We also grey this out on the diagram.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><span class=\"m-posts__contentImg\"><img decoding=\"async\" width=\"861\" height=\"891\" src=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/database-flowchart-showing-joins-and-filters-for-c.png\" alt=\"database flowchart showing joins and filters for customer sales data\" class=\"wp-image-26241\" srcset=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/database-flowchart-showing-joins-and-filters-for-c.png 861w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/database-flowchart-showing-joins-and-filters-for-c-290x300.png 290w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/database-flowchart-showing-joins-and-filters-for-c-768x795.png 768w\" sizes=\"(max-width: 861px) 100vw, 861px\" \/><\/span><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>This will also be detected by the <a href=\"https:\/\/flowhigh.io\/visualise-sql\" target=\"_blank\" rel=\"noreferrer noopener\">FlowHigh Analyser<\/a> as an SQL anti pattern.<\/p>\n\n\n\n<figure class=\"wp-block-image\" data-hd-variant-url=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/code-snippet-showing-sql-query-with-orphaned-cte-m.png\"><span class=\"m-posts__contentImg\"><img decoding=\"async\" width=\"496\" height=\"314\" src=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/code-snippet-showing-sql-query-with-orphaned-cte-m.png\" alt=\"code snippet showing SQL query with orphaned CTE marked in red\" class=\"wp-image-26242\" srcset=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/code-snippet-showing-sql-query-with-orphaned-cte-m.png 496w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/code-snippet-showing-sql-query-with-orphaned-cte-m-300x190.png 300w\" sizes=\"(max-width: 496px) 100vw, 496px\" \/><\/span><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<figure class=\"wp-block-image\" data-hd-variant-url=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/sql-query-discussing-unused-common-table-expressio.png\"><span class=\"m-posts__contentImg\"><img decoding=\"async\" width=\"1298\" height=\"980\" src=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/sql-query-discussing-unused-common-table-expressio.png\" alt=\"SQL query discussing unused Common Table Expressions and performance issues\" class=\"wp-image-26243\" srcset=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/sql-query-discussing-unused-common-table-expressio.png 1298w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/sql-query-discussing-unused-common-table-expressio-300x227.png 300w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/sql-query-discussing-unused-common-table-expressio-1024x773.png 1024w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/sql-query-discussing-unused-common-table-expressio-768x580.png 768w\" sizes=\"(max-width: 1298px) 100vw, 1298px\" \/><\/span><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><a id=\"post-26221-_ygah4qq6mmz9\"><\/a>Inline view<\/h4>\n\n\n\n<p>Inline views are similar to CTEs. They can both be nested. One of the differences is that CTEs are reusable and multiple instances can be referenced inside the same SQL statement.<\/p>\n\n\n<pre class=\"lang:default decode:true\">SELECT category_name\n      ,max_list_price\n  FROM product_categories a\n  JOIN(SELECT category_id\n         \t,MAX( list_price) max_list_price\n     \tFROM products\n        GROUP BYcategory_id) b\n    ON a.category_id=b.category_id\nORDER BYcategory_name;<\/pre>\n\n\n<figure class=\"wp-block-image\"><span class=\"m-posts__contentImg\"><img decoding=\"async\" width=\"856\" height=\"906\" src=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/flowchart-showing-data-operations-and-group-by-fun.png\" alt=\"flowchart showing data operations and group by function in design tool\" class=\"wp-image-26244\" srcset=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/flowchart-showing-data-operations-and-group-by-fun.png 856w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/flowchart-showing-data-operations-and-group-by-fun-283x300.png 283w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/flowchart-showing-data-operations-and-group-by-fun-768x813.png 768w\" sizes=\"(max-width: 856px) 100vw, 856px\" \/><\/span><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><a id=\"post-26221-_yz44d3sbj9rn\"><\/a>Recursive CTE<\/h4>\n\n\n\n<p>Recursive CTEs traverse a hierarchy. Here is an example<\/p>\n\n\n<pre class=\"lang:default decode:true \">WITH cte_numbers(n\n          ,weekday)AS (\n    SELECT0\n          ,DATENAME(DW\n              ,0)\n   UNION ALL\n    SELECT n+1\n          ,DATENAME(DW\n              ,n+1)\n      FROM cte_numbers\n \tWHERE n&lt;6)\nSELECT weekday\n  FROM cte_numbers;<\/pre>\n\n\n<p>The diagram shows the anchor and recursive clause. For a great explanation on how recursive CTEs work check this blog post <a href=\"https:\/\/www.sqlservertutorial.net\/sql-server-basics\/sql-server-recursive-cte\/\" target=\"_blank\" rel=\"noreferrer noopener\">Understanding Recursive CTEs<\/a>.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><span class=\"m-posts__contentImg\"><img decoding=\"async\" width=\"755\" height=\"848\" src=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/diagram-illustrating-recursive-sql-query-structure.png\" alt=\"diagram illustrating recursive SQL query structure with clauses and filters\" class=\"wp-image-26245\" srcset=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/diagram-illustrating-recursive-sql-query-structure.png 755w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/diagram-illustrating-recursive-sql-query-structure-267x300.png 267w\" sizes=\"(max-width: 755px) 100vw, 755px\" \/><\/span><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><a id=\"post-26221-_d05trz9mq4s3\"><\/a>Function table<\/h4>\n\n\n\n<p>Some databases and SQL dialects offer User Defined Table Functions (UDTF).<\/p>\n\n\n\n<p>Here is an example for a UDTF<\/p>\n\n\n<pre class=\"lang:default decode:true \">SELECT *\n  FROM TABLE(get_countries_for_user(123)) cc\nWHERE cc.country_code IN ('US'\n          ,'FR'\n          ,'CA')\nORDER BY country_code;<\/pre>\n\n\n<p>And how FlowHigh displays a UDTF in the diagram<\/p>\n\n\n\n<figure class=\"wp-block-image\"><span class=\"m-posts__contentImg\"><img decoding=\"async\" width=\"504\" height=\"573\" src=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/flowchart-showing-a-data-processing-sequence-with.png\" alt=\"flowchart showing a data processing sequence with filter sort and select steps\" class=\"wp-image-26246\" srcset=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/flowchart-showing-a-data-processing-sequence-with.png 504w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/flowchart-showing-a-data-processing-sequence-with-264x300.png 264w\" sizes=\"(max-width: 504px) 100vw, 504px\" \/><\/span><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><a id=\"post-26221-_chuitnob41iv\"><\/a>Browsing and searching SQL<\/h3>\n\n\n\n<p>You can browse the diagram<\/p>\n\n\n\n<p>You can find tables by browsing and selecting tables from the Table List. This is very useful if you need to quickly find a table in a very large and complex SQL statement with many subqueries and table joins.<\/p>\n\n\n\n<p>In the figure below we have selected the DimCutsomer table and the f_customer_sales CTE. They are highlighted on the diagram.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><span class=\"m-posts__contentImg\"><img decoding=\"async\" width=\"1462\" height=\"1122\" src=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/data-flow-diagram-depicting-joins-and-grouping-in.png\" alt=\"data flow diagram depicting joins and grouping in a database query setup\" class=\"wp-image-26247\" srcset=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/data-flow-diagram-depicting-joins-and-grouping-in.png 1462w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/data-flow-diagram-depicting-joins-and-grouping-in-300x230.png 300w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/data-flow-diagram-depicting-joins-and-grouping-in-1024x786.png 1024w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/data-flow-diagram-depicting-joins-and-grouping-in-768x589.png 768w\" sizes=\"(max-width: 1462px) 100vw, 1462px\" \/><\/span><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>You can also browse the diagram itself and select tables which will then highlight the corresponding part of the SQL code.<\/p>\n\n\n\n<p>In the figure below we have selected the f_customer_sales CTE. This will highlight the definition of the CTE and also any instances of the CTE.<\/p>\n\n\n\n<figure class=\"wp-block-image\" data-hd-variant-url=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/diagram-of-a-sql-query-illustrating-data-processin.png\"><span class=\"m-posts__contentImg\"><img decoding=\"async\" width=\"1582\" height=\"1036\" src=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/diagram-of-a-sql-query-illustrating-data-processin.png\" alt=\"Diagram of a SQL query illustrating data processing steps\" class=\"wp-image-26248\" srcset=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/diagram-of-a-sql-query-illustrating-data-processin.png 1582w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/diagram-of-a-sql-query-illustrating-data-processin-300x196.png 300w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/diagram-of-a-sql-query-illustrating-data-processin-1024x671.png 1024w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/diagram-of-a-sql-query-illustrating-data-processin-768x503.png 768w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/diagram-of-a-sql-query-illustrating-data-processin-1536x1006.png 1536w\" sizes=\"(max-width: 1582px) 100vw, 1582px\" \/><\/span><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>We search for the d_date CTE and results are highlighted in the editor and the diagram<\/p>\n\n\n\n<figure class=\"wp-block-image\" data-hd-variant-url=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/data-processing-diagram-illustrating-sql-query-str.png\"><span class=\"m-posts__contentImg\"><img decoding=\"async\" width=\"1634\" height=\"691\" src=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/data-processing-diagram-illustrating-sql-query-str.png\" alt=\"data processing diagram illustrating SQL query structure and joins\" class=\"wp-image-26249\" srcset=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/data-processing-diagram-illustrating-sql-query-str.png 1634w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/data-processing-diagram-illustrating-sql-query-str-300x127.png 300w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/data-processing-diagram-illustrating-sql-query-str-1024x433.png 1024w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/data-processing-diagram-illustrating-sql-query-str-768x325.png 768w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/data-processing-diagram-illustrating-sql-query-str-1536x650.png 1536w\" sizes=\"(max-width: 1634px) 100vw, 1634px\" \/><\/span><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><a id=\"post-26221-_x3dxes5p2fjw\"><\/a>Navigating the SQL visualisation<\/h3>\n\n\n\n<p><\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><a id=\"post-26221-_cmvjahv0qti9\"><\/a>Expand and collapse<\/h4>\n\n\n\n<p>You can collapse and also expand any CTEs and Inline Views. This is useful to drill down into the SQL hierarchy and nest or unnest any lower or higher level parts of the SQL.<\/p>\n\n\n\n<p>Let\u2019s collapse both CTEs to focus on the top level tables that make up our SQL.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><span class=\"m-posts__contentImg\"><img decoding=\"async\" width=\"1034\" height=\"845\" src=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/data-flow-diagram-showing-joins-and-group-by-opera.png\" alt=\"data flow diagram showing joins and group by operations for analysis\" class=\"wp-image-26250\" srcset=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/data-flow-diagram-showing-joins-and-group-by-opera.png 1034w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/data-flow-diagram-showing-joins-and-group-by-opera-300x245.png 300w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/data-flow-diagram-showing-joins-and-group-by-opera-1024x837.png 1024w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/data-flow-diagram-showing-joins-and-group-by-opera-768x628.png 768w\" sizes=\"(max-width: 1034px) 100vw, 1034px\" \/><\/span><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>We can now drill further into our code one CTE at a time<\/p>\n\n\n\n<p>The same expand and collapse functionality also exists for the SQL code editor. We can also expand and collapse and inline views or CTEs.<\/p>\n\n\n\n<figure class=\"wp-block-image\" data-hd-variant-url=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/code-snippet-of-sql-query-for-customer-sales-data.png\"><span class=\"m-posts__contentImg\"><img decoding=\"async\" width=\"491\" height=\"312\" src=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/code-snippet-of-sql-query-for-customer-sales-data.png\" alt=\"code snippet of SQL query for customer sales data analysis\" class=\"wp-image-26251\" srcset=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/code-snippet-of-sql-query-for-customer-sales-data.png 491w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/code-snippet-of-sql-query-for-customer-sales-data-300x191.png 300w\" sizes=\"(max-width: 491px) 100vw, 491px\" \/><\/span><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>This drill down feature particularly useful for deeply nested and complex SQL<\/p>\n\n\n\n<p>Level 1<\/p>\n\n\n\n<figure class=\"wp-block-image\"><span class=\"m-posts__contentImg\"><img decoding=\"async\" width=\"727\" height=\"1067\" src=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/diagram-showing-a-data-processing-flow-with-filter.png\" alt=\"Diagram showing a data processing flow with filter sort and limit steps\" class=\"wp-image-26252\" srcset=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/diagram-showing-a-data-processing-flow-with-filter.png 727w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/diagram-showing-a-data-processing-flow-with-filter-204x300.png 204w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/diagram-showing-a-data-processing-flow-with-filter-698x1024.png 698w\" sizes=\"(max-width: 727px) 100vw, 727px\" \/><\/span><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>Level 2<\/p>\n\n\n\n<figure class=\"wp-block-image\"><span class=\"m-posts__contentImg\"><img decoding=\"async\" width=\"449\" height=\"1035\" src=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/flowchart-diagram-showing-various-data-operations.png\" alt=\"flowchart diagram showing various data operations and selections\" class=\"wp-image-26253\" srcset=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/flowchart-diagram-showing-various-data-operations.png 449w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/flowchart-diagram-showing-various-data-operations-130x300.png 130w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/flowchart-diagram-showing-various-data-operations-444x1024.png 444w\" sizes=\"(max-width: 449px) 100vw, 449px\" \/><\/span><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>Level 3<\/p>\n\n\n\n<figure class=\"wp-block-image\"><span class=\"m-posts__contentImg\"><img decoding=\"async\" width=\"1062\" height=\"689\" src=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/data-flow-diagram-showing-unions-grouping-and-sale.png\" alt=\"data flow diagram showing unions grouping and sales details\" class=\"wp-image-26254\" srcset=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/data-flow-diagram-showing-unions-grouping-and-sale.png 1062w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/data-flow-diagram-showing-unions-grouping-and-sale-300x195.png 300w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/data-flow-diagram-showing-unions-grouping-and-sale-1024x664.png 1024w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/data-flow-diagram-showing-unions-grouping-and-sale-768x498.png 768w\" sizes=\"(max-width: 1062px) 100vw, 1062px\" \/><\/span><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>Level 4<\/p>\n\n\n\n<figure class=\"wp-block-image\" data-hd-variant-url=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/data-flow-diagram-showing-various-data-joins-and-f.png\"><span class=\"m-posts__contentImg\"><img decoding=\"async\" width=\"1419\" height=\"855\" src=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/data-flow-diagram-showing-various-data-joins-and-f.png\" alt=\"data flow diagram showing various data joins and filters in a query\" class=\"wp-image-26255\" srcset=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/data-flow-diagram-showing-various-data-joins-and-f.png 1419w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/data-flow-diagram-showing-various-data-joins-and-f-300x181.png 300w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/data-flow-diagram-showing-various-data-joins-and-f-1024x617.png 1024w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/data-flow-diagram-showing-various-data-joins-and-f-768x463.png 768w\" sizes=\"(max-width: 1419px) 100vw, 1419px\" \/><\/span><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><a id=\"post-26221-_yqid8iaek6d8\"><\/a>Columns<\/h4>\n\n\n\n<p>You can also view column level detail<\/p>\n\n\n\n<p>Hover over a table<\/p>\n\n\n\n<figure class=\"wp-block-image\"><span class=\"m-posts__contentImg\"><img decoding=\"async\" width=\"313\" height=\"138\" src=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/data-model-showing-connection-between-dimdate-and.png\" alt=\"data model showing connection between DimDate and CalendarYear DateKey\" class=\"wp-image-26256\" srcset=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/data-model-showing-connection-between-dimdate-and.png 313w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/data-model-showing-connection-between-dimdate-and-300x132.png 300w\" sizes=\"(max-width: 313px) 100vw, 313px\" \/><\/span><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>\u2026or an operator<\/p>\n\n\n\n<figure class=\"wp-block-image\"><span class=\"m-posts__contentImg\"><img decoding=\"async\" width=\"708\" height=\"164\" src=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/inner-join-expression-showing-date-key-mapping-bet.png\" alt=\"Inner join expression showing date key mapping between tables\" class=\"wp-image-26257\" srcset=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/inner-join-expression-showing-date-key-mapping-bet.png 708w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/inner-join-expression-showing-date-key-mapping-bet-300x69.png 300w\" sizes=\"(max-width: 708px) 100vw, 708px\" \/><\/span><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>Expand a table<\/p>\n\n\n\n<figure class=\"wp-block-image\"><span class=\"m-posts__contentImg\"><img decoding=\"async\" width=\"276\" height=\"131\" src=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/dimdate-table-with-calendar-year-and-date-key-attr.png\" alt=\"dimdate table with calendar year and date key attributes\" class=\"wp-image-26258\"\/><\/span><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>Expand all tables<\/p>\n\n\n\n<figure class=\"wp-block-image\" data-hd-variant-url=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/data-flow-diagram-showing-joins-and-filters-for-cu.png\"><span class=\"m-posts__contentImg\"><img decoding=\"async\" width=\"1294\" height=\"985\" src=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/data-flow-diagram-showing-joins-and-filters-for-cu.png\" alt=\"Data flow diagram showing joins and filters for customer sales analysis\" class=\"wp-image-26259\" srcset=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/data-flow-diagram-showing-joins-and-filters-for-cu.png 1294w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/data-flow-diagram-showing-joins-and-filters-for-cu-300x228.png 300w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/data-flow-diagram-showing-joins-and-filters-for-cu-1024x779.png 1024w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/data-flow-diagram-showing-joins-and-filters-for-cu-768x585.png 768w\" sizes=\"(max-width: 1294px) 100vw, 1294px\" \/><\/span><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<h3 class=\"wp-block-heading\"><a id=\"post-26221-_276gzv5jth9\"><\/a>Other features<\/h3>\n\n\n\n<h4 class=\"wp-block-heading\"><a id=\"post-26221-_b75qqzz52fi7\"><\/a><br>Export and download SQL diagram<\/h4>\n\n\n\n<p>You can also download the SQL diagram as a PNG to use in your documentation.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><span class=\"m-posts__contentImg\"><img decoding=\"async\" width=\"867\" height=\"337\" src=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/data-flow-diagram-showing-inner-join-between-sales.png\" alt=\"data flow diagram showing inner join between sales customer and date\" class=\"wp-image-26260\" srcset=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/data-flow-diagram-showing-inner-join-between-sales.png 867w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/data-flow-diagram-showing-inner-join-between-sales-300x117.png 300w, https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/data-flow-diagram-showing-inner-join-between-sales-768x299.png 768w\" sizes=\"(max-width: 867px) 100vw, 867px\" \/><\/span><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><a id=\"post-26221-_1xgyf97pprle\"><\/a>Zoom<\/h4>\n\n\n\n<p>You can zoom in and out of a diagram by using your mouse wheel or the zoom feature in the bottom right corner of the diagram<\/p>\n\n\n\n<figure class=\"wp-block-image\"><span class=\"m-posts__contentImg\"><img decoding=\"async\" width=\"266\" height=\"109\" src=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/10\/word-image-26221-40.png\" alt=\"\" class=\"wp-image-26261\"\/><\/span><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\"><a id=\"post-26221-_5kivlzg5g60o\"><\/a>FAQs<\/h2>\n\n\n\n<div class=\"accordion-container\">\n                                    <div class=\"accordion\">\n                <input type=\"checkbox\" id=\"accordion-69f0ea22885d1\" class=\"accordion-toggle\">\n                <label for=\"accordion-69f0ea22885d1\" class=\"accordion-header\">\n        <h3 class=\"accordion-title\">\n            Can SQL be visualised?        <\/h3>\n                    <span class=\"accordion-icon\"><\/span>\n                <\/label>\n                <div class=\"accordion-content\">\n                    <p><p><!-- wp:paragraph --><\/p>\n<p>SQL is structured hierarchically as a tree with different levels of nesting and relationships. The tables and operators (WHERE, GROUP BY etc.) can be represented as nodes and the dependencies between them as edges on a graph.<\/p>\n<p><!-- \/wp:paragraph --> <!-- wp:paragraph --><\/p>\n<p>Yes, SQL can be visualised in several ways to help users better understand complex queries and data relationships. Some common methods of visualising SQL include:<\/p>\n<p><!-- \/wp:paragraph --> <!-- wp:paragraph --><\/p>\n<p>Query Diagrams: Visual representations of SQL queries, showing how tables are joined, the relationships between them, and which fields are selected.<\/p>\n<p><!-- \/wp:paragraph --><\/p>\n<\/p>\n                <\/div>\n            <\/div>\n                                <div class=\"accordion\">\n                <input type=\"checkbox\" id=\"accordion-69f0ea2288659\" class=\"accordion-toggle\">\n                <label for=\"accordion-69f0ea2288659\" class=\"accordion-header\">\n        <h3 class=\"accordion-title\">\n            Can you create lineage from a Snowflake query?        <\/h3>\n                    <span class=\"accordion-icon\"><\/span>\n                <\/label>\n                <div class=\"accordion-content\">\n                    <p><p>Yes. Snowflake is a popular data platform for data analytics and AI. It has very strong support for SQL. You can create lineage and visualise Snowflake SQL the same way that you can visualise SQL from any other SQL database.<\/p>\n<\/p>\n                <\/div>\n            <\/div>\n                                <div class=\"accordion\">\n                <input type=\"checkbox\" id=\"accordion-69f0ea2288669\" class=\"accordion-toggle\">\n                <label for=\"accordion-69f0ea2288669\" class=\"accordion-header\">\n        <h3 class=\"accordion-title\">\n            Can you visualise using SQL?        <\/h3>\n                    <span class=\"accordion-icon\"><\/span>\n                <\/label>\n                <div class=\"accordion-content\">\n                    <p><p><!-- wp:paragraph --><\/p>\n<p>You can use SQL to create data visualisations like charts, graphs, and dashboards by querying the database to retrieve specific data. SQL allows you to filter, group, and aggregate data, which is then passed to a visualisation tool such as Tableau, Power BI. Data engineers and data scientists use SQL in notebooks such as Jupyter Notebook to create charts and data visualisations. These tools take the results of your SQL queries and turn them into visual formats like bar charts, pie charts, line graphs, and dashboards that make it easier to understand trends, patterns, and insights from the data.<\/p>\n<p><!-- \/wp:paragraph --> <!-- wp:paragraph --><\/p>\n<p>For example, you can use SQL to calculate total sales by region, and then a visualisation tool can display that data as a bar chart or pie chart. SQL gives you control over what data to include in the visualisation, while the visualisation tools handle presenting that data in a user-friendly, graphical format.<\/p>\n<p><!-- \/wp:paragraph --><\/p>\n<\/p>\n                <\/div>\n            <\/div>\n                                <div class=\"accordion\">\n                <input type=\"checkbox\" id=\"accordion-69f0ea22886ab\" class=\"accordion-toggle\">\n                <label for=\"accordion-69f0ea22886ab\" class=\"accordion-header\">\n        <h3 class=\"accordion-title\">\n            How to visualise SQL?        <\/h3>\n                    <span class=\"accordion-icon\"><\/span>\n                <\/label>\n                <div class=\"accordion-content\">\n                    <p><p>You can visualise SQL manually using pen and paper or a diagramming tool such as Visio or Excalidraw (my recommendation). You will need to analyse the structure of the SQL query code. Alternatively you can automate the approach by using an <a href=\"https:\/\/flowhigh.io\" target=\"_blank\" rel=\"noreferrer noopener\">SQL Visualiser tool such as FlowHigh<\/a>. The automated approach automatically analyses the structure of the SQL query using an SQL parser. The parser creates an AST tree that then can be used to visualise the structure of the SQL query.<\/p>\n<\/p>\n                <\/div>\n            <\/div>\n            <\/div>\n\n\n<h2 class=\"wp-block-heading\"><a id=\"post-26221-_7abzmqao7h3c\"><\/a>Further reading<\/h2>\n\n\n\n<p><a href=\"https:\/\/doi.org\/10.1145\/3231712\" target=\"_blank\" rel=\"noreferrer noopener\">Errors and Complications in SQL Query Formulation<\/a><\/p>\n\n\n\n<p><a href=\"https:\/\/arxiv.org\/abs\/2004.11375\" target=\"_blank\" rel=\"noreferrer noopener\">QueryVis: Logic-based diagrams help users understand complicated SQL queries faster<\/a><\/p>\n\n\n\n<p>QueryVis Demo<\/p>\n\n\n\n<p><a href=\"http:\/\/queryviz.com\/\">SQL Query Visualization<\/a><\/p>\n\n\n\n<p><a href=\"https:\/\/www.vldb.org\/pvldb\/vol16\/p3890-gatterbauer.pdf\" target=\"_blank\" rel=\"noreferrer noopener\">A Tutorial on Visual Representations of Relational Queries<\/a><\/p>\n\n\n\n<p><a href=\"https:\/\/northeastern-datalab.github.io\/visual-query-representation-tutorial\/slides\/VLDB_2023-Visual_Representations_of_Relational_Queries.pdf\" target=\"_blank\" rel=\"noreferrer noopener\">Visual Representations of Relational Queries<\/a><\/p>\n\n\n\n<p>SQLVis<\/p>\n\n\n\n<p><a href=\"https:\/\/github.com\/AlexOdyn\/sqlvisplus\" target=\"_blank\" rel=\"noreferrer noopener\">SQLVis<\/a><\/p>\n\n\n\n<p><a href=\"https:\/\/pure.tue.nl\/ws\/portalfiles\/portal\/191839095\/VLHCC_author_version.pdf\" target=\"_blank\" rel=\"noreferrer noopener\">Visual Query Representations<\/a><\/p>\n\n\n\n<figure class=\"wp-block-embed is-type-video is-provider-youtube wp-block-embed-youtube wp-embed-aspect-16-9 wp-has-aspect-ratio\"><div class=\"wp-block-embed__wrapper\">\n<iframe title=\"SQLVis demo video\" width=\"640\" height=\"360\" src=\"https:\/\/www.youtube.com\/embed\/CWeUviYIBiA?feature=oembed\" frameborder=\"0\" allow=\"accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share\" referrerpolicy=\"strict-origin-when-cross-origin\" allowfullscreen><\/iframe>\n<\/div><\/figure>\n","protected":false},"excerpt":{"rendered":"<p>Have you ever inherited the SQL codebase from someone else where it is in a \u201cbit of a mess\u201d? \ud83d\ude01 Sound familiar? Most data engineers and SQL developers have been there including myself\u2014staring at a spaghetti-like web of queries, trying to untangle a chaotic SQL codebase. It\u2019s a common nightmare. Traditionally, you had to fight &#8230;<\/p>\n","protected":false},"author":2,"featured_media":26287,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"inline_featured_image":false,"footnotes":""},"categories":[872,391],"tags":[],"class_list":["post-26221","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-data-lineage","category-sql"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.3 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>SQL Visualisation Guide - Query Diagrams, Lineage &amp; ERD<\/title>\n<meta name=\"description\" content=\"Uncover SQL visualisation techniques with query diagrams, ER diagrams, lineage, and tools to make complex SQL easy to understand.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/sonra.io\/sql-visualisation-guide\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Visualisation Guide - Query Diagrams, Lineage &amp; ERD\" \/>\n<meta property=\"og:description\" content=\"Uncover SQL visualisation techniques with query diagrams, ER diagrams, lineage, and tools to make complex SQL easy to understand.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/sonra.io\/sql-visualisation-guide\/\" \/>\n<meta property=\"og:site_name\" content=\"Sonra\" \/>\n<meta property=\"article:published_time\" content=\"2024-11-03T14:31:02+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2025-01-07T11:03:58+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/sonra.io\/wp-content\/uploads\/2024\/11\/SQL-Visualisation-Guide-Query-Diagrams-Lineage-ERD.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"1200\" \/>\n\t<meta property=\"og:image:height\" content=\"675\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"Uli Bethke\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Uli Bethke\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"32 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/sonra.io\\\/sql-visualisation-guide\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sonra.io\\\/sql-visualisation-guide\\\/\"},\"author\":{\"name\":\"Uli Bethke\",\"@id\":\"https:\\\/\\\/sonra.io\\\/#\\\/schema\\\/person\\\/5367add554eb9510e03c4c3821bc8efc\"},\"headline\":\"SQL Visualisation Guide &#8211; Query Diagrams, Lineage &amp; ERD\",\"datePublished\":\"2024-11-03T14:31:02+00:00\",\"dateModified\":\"2025-01-07T11:03:58+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/sonra.io\\\/sql-visualisation-guide\\\/\"},\"wordCount\":5474,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\\\/\\\/sonra.io\\\/#organization\"},\"image\":{\"@id\":\"https:\\\/\\\/sonra.io\\\/sql-visualisation-guide\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/sonra.io\\\/wp-content\\\/uploads\\\/2024\\\/11\\\/SQL-Visualisation-Guide-Query-Diagrams-Lineage-ERD.jpg\",\"articleSection\":[\"Data Lineage\",\"SQL\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/sonra.io\\\/sql-visualisation-guide\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/sonra.io\\\/sql-visualisation-guide\\\/\",\"url\":\"https:\\\/\\\/sonra.io\\\/sql-visualisation-guide\\\/\",\"name\":\"SQL Visualisation Guide - Query Diagrams, Lineage &amp; ERD\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sonra.io\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/sonra.io\\\/sql-visualisation-guide\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/sonra.io\\\/sql-visualisation-guide\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/sonra.io\\\/wp-content\\\/uploads\\\/2024\\\/11\\\/SQL-Visualisation-Guide-Query-Diagrams-Lineage-ERD.jpg\",\"datePublished\":\"2024-11-03T14:31:02+00:00\",\"dateModified\":\"2025-01-07T11:03:58+00:00\",\"description\":\"Uncover SQL visualisation techniques with query diagrams, ER diagrams, lineage, and tools to make complex SQL easy to understand.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/sonra.io\\\/sql-visualisation-guide\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/sonra.io\\\/sql-visualisation-guide\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/sonra.io\\\/sql-visualisation-guide\\\/#primaryimage\",\"url\":\"https:\\\/\\\/sonra.io\\\/wp-content\\\/uploads\\\/2024\\\/11\\\/SQL-Visualisation-Guide-Query-Diagrams-Lineage-ERD.jpg\",\"contentUrl\":\"https:\\\/\\\/sonra.io\\\/wp-content\\\/uploads\\\/2024\\\/11\\\/SQL-Visualisation-Guide-Query-Diagrams-Lineage-ERD.jpg\",\"width\":1200,\"height\":675},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/sonra.io\\\/sql-visualisation-guide\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/sonra.io\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL\",\"item\":\"https:\\\/\\\/sonra.io\\\/category\\\/sql\\\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"SQL Visualisation Guide &#8211; Query Diagrams, Lineage &amp; ERD\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/sonra.io\\\/#website\",\"url\":\"https:\\\/\\\/sonra.io\\\/\",\"name\":\"Sonra\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\\\/\\\/sonra.io\\\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/sonra.io\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\\\/\\\/sonra.io\\\/#organization\",\"name\":\"Sonra\",\"alternateName\":\"Sonra.io\",\"url\":\"https:\\\/\\\/sonra.io\\\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/sonra.io\\\/#\\\/schema\\\/logo\\\/image\\\/\",\"url\":\"https:\\\/\\\/sonra.io\\\/wp-content\\\/uploads\\\/2015\\\/02\\\/sonra-logo-circle.png\",\"contentUrl\":\"https:\\\/\\\/sonra.io\\\/wp-content\\\/uploads\\\/2015\\\/02\\\/sonra-logo-circle.png\",\"width\":600,\"height\":600,\"caption\":\"Sonra\"},\"image\":{\"@id\":\"https:\\\/\\\/sonra.io\\\/#\\\/schema\\\/logo\\\/image\\\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/sonra.io\\\/#\\\/schema\\\/person\\\/5367add554eb9510e03c4c3821bc8efc\",\"name\":\"Uli Bethke\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/7cedc25811b980fbc3c0ed40dae3ca8e7ad436e9e19cb966c0d7c53957701c3a?s=96&d=https%3A%2F%2Fsonra.io%2Fwp-content%2Fuploads%2F2023%2F04%2FScreenshot_15-removebg-preview.png&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/7cedc25811b980fbc3c0ed40dae3ca8e7ad436e9e19cb966c0d7c53957701c3a?s=96&d=https%3A%2F%2Fsonra.io%2Fwp-content%2Fuploads%2F2023%2F04%2FScreenshot_15-removebg-preview.png&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/7cedc25811b980fbc3c0ed40dae3ca8e7ad436e9e19cb966c0d7c53957701c3a?s=96&d=https%3A%2F%2Fsonra.io%2Fwp-content%2Fuploads%2F2023%2F04%2FScreenshot_15-removebg-preview.png&r=g\",\"caption\":\"Uli Bethke\"},\"url\":\"https:\\\/\\\/sonra.io\\\/author\\\/ubet\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"SQL Visualisation Guide - Query Diagrams, Lineage &amp; ERD","description":"Uncover SQL visualisation techniques with query diagrams, ER diagrams, lineage, and tools to make complex SQL easy to understand.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/sonra.io\/sql-visualisation-guide\/","og_locale":"en_US","og_type":"article","og_title":"SQL Visualisation Guide - Query Diagrams, Lineage &amp; ERD","og_description":"Uncover SQL visualisation techniques with query diagrams, ER diagrams, lineage, and tools to make complex SQL easy to understand.","og_url":"https:\/\/sonra.io\/sql-visualisation-guide\/","og_site_name":"Sonra","article_published_time":"2024-11-03T14:31:02+00:00","article_modified_time":"2025-01-07T11:03:58+00:00","og_image":[{"width":1200,"height":675,"url":"https:\/\/sonra.io\/wp-content\/uploads\/2024\/11\/SQL-Visualisation-Guide-Query-Diagrams-Lineage-ERD.jpg","type":"image\/jpeg"}],"author":"Uli Bethke","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Uli Bethke","Est. reading time":"32 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/sonra.io\/sql-visualisation-guide\/#article","isPartOf":{"@id":"https:\/\/sonra.io\/sql-visualisation-guide\/"},"author":{"name":"Uli Bethke","@id":"https:\/\/sonra.io\/#\/schema\/person\/5367add554eb9510e03c4c3821bc8efc"},"headline":"SQL Visualisation Guide &#8211; Query Diagrams, Lineage &amp; ERD","datePublished":"2024-11-03T14:31:02+00:00","dateModified":"2025-01-07T11:03:58+00:00","mainEntityOfPage":{"@id":"https:\/\/sonra.io\/sql-visualisation-guide\/"},"wordCount":5474,"commentCount":0,"publisher":{"@id":"https:\/\/sonra.io\/#organization"},"image":{"@id":"https:\/\/sonra.io\/sql-visualisation-guide\/#primaryimage"},"thumbnailUrl":"https:\/\/sonra.io\/wp-content\/uploads\/2024\/11\/SQL-Visualisation-Guide-Query-Diagrams-Lineage-ERD.jpg","articleSection":["Data Lineage","SQL"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/sonra.io\/sql-visualisation-guide\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/sonra.io\/sql-visualisation-guide\/","url":"https:\/\/sonra.io\/sql-visualisation-guide\/","name":"SQL Visualisation Guide - Query Diagrams, Lineage &amp; ERD","isPartOf":{"@id":"https:\/\/sonra.io\/#website"},"primaryImageOfPage":{"@id":"https:\/\/sonra.io\/sql-visualisation-guide\/#primaryimage"},"image":{"@id":"https:\/\/sonra.io\/sql-visualisation-guide\/#primaryimage"},"thumbnailUrl":"https:\/\/sonra.io\/wp-content\/uploads\/2024\/11\/SQL-Visualisation-Guide-Query-Diagrams-Lineage-ERD.jpg","datePublished":"2024-11-03T14:31:02+00:00","dateModified":"2025-01-07T11:03:58+00:00","description":"Uncover SQL visualisation techniques with query diagrams, ER diagrams, lineage, and tools to make complex SQL easy to understand.","breadcrumb":{"@id":"https:\/\/sonra.io\/sql-visualisation-guide\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/sonra.io\/sql-visualisation-guide\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/sonra.io\/sql-visualisation-guide\/#primaryimage","url":"https:\/\/sonra.io\/wp-content\/uploads\/2024\/11\/SQL-Visualisation-Guide-Query-Diagrams-Lineage-ERD.jpg","contentUrl":"https:\/\/sonra.io\/wp-content\/uploads\/2024\/11\/SQL-Visualisation-Guide-Query-Diagrams-Lineage-ERD.jpg","width":1200,"height":675},{"@type":"BreadcrumbList","@id":"https:\/\/sonra.io\/sql-visualisation-guide\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/sonra.io\/"},{"@type":"ListItem","position":2,"name":"SQL","item":"https:\/\/sonra.io\/category\/sql\/"},{"@type":"ListItem","position":3,"name":"SQL Visualisation Guide &#8211; Query Diagrams, Lineage &amp; ERD"}]},{"@type":"WebSite","@id":"https:\/\/sonra.io\/#website","url":"https:\/\/sonra.io\/","name":"Sonra","description":"","publisher":{"@id":"https:\/\/sonra.io\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/sonra.io\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/sonra.io\/#organization","name":"Sonra","alternateName":"Sonra.io","url":"https:\/\/sonra.io\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/sonra.io\/#\/schema\/logo\/image\/","url":"https:\/\/sonra.io\/wp-content\/uploads\/2015\/02\/sonra-logo-circle.png","contentUrl":"https:\/\/sonra.io\/wp-content\/uploads\/2015\/02\/sonra-logo-circle.png","width":600,"height":600,"caption":"Sonra"},"image":{"@id":"https:\/\/sonra.io\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/sonra.io\/#\/schema\/person\/5367add554eb9510e03c4c3821bc8efc","name":"Uli Bethke","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/7cedc25811b980fbc3c0ed40dae3ca8e7ad436e9e19cb966c0d7c53957701c3a?s=96&d=https%3A%2F%2Fsonra.io%2Fwp-content%2Fuploads%2F2023%2F04%2FScreenshot_15-removebg-preview.png&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/7cedc25811b980fbc3c0ed40dae3ca8e7ad436e9e19cb966c0d7c53957701c3a?s=96&d=https%3A%2F%2Fsonra.io%2Fwp-content%2Fuploads%2F2023%2F04%2FScreenshot_15-removebg-preview.png&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/7cedc25811b980fbc3c0ed40dae3ca8e7ad436e9e19cb966c0d7c53957701c3a?s=96&d=https%3A%2F%2Fsonra.io%2Fwp-content%2Fuploads%2F2023%2F04%2FScreenshot_15-removebg-preview.png&r=g","caption":"Uli Bethke"},"url":"https:\/\/sonra.io\/author\/ubet\/"}]}},"_links":{"self":[{"href":"https:\/\/sonra.io\/wp-json\/wp\/v2\/posts\/26221","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sonra.io\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sonra.io\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sonra.io\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/sonra.io\/wp-json\/wp\/v2\/comments?post=26221"}],"version-history":[{"count":37,"href":"https:\/\/sonra.io\/wp-json\/wp\/v2\/posts\/26221\/revisions"}],"predecessor-version":[{"id":27027,"href":"https:\/\/sonra.io\/wp-json\/wp\/v2\/posts\/26221\/revisions\/27027"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/sonra.io\/wp-json\/wp\/v2\/media\/26287"}],"wp:attachment":[{"href":"https:\/\/sonra.io\/wp-json\/wp\/v2\/media?parent=26221"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sonra.io\/wp-json\/wp\/v2\/categories?post=26221"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sonra.io\/wp-json\/wp\/v2\/tags?post=26221"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}