How to Visualize a SQL Query With Joins, CTEs and Subqueries

A long query with several joins, a couple of CTEs and a subquery is hard to hold in your head. The question you usually have is simple: which tables does this read, and where does each output column come from? Here is what you get when you paste such a query into the SQL lineage analyzer, using the real output for an example, including what it does not show.

Open the SQL Lineage Analyzer

The example query

create table monthly_report as
with recent as (
  select order_id, customer_id, amount
  from orders where order_date >= '2025-01-01'
),
big as (
  select customer_id, sum(amount) as spend
  from recent group by customer_id
)
select
  c.customer_id,
  c.region,
  b.spend,
  (select max(p.paid_at) from payments p
   where p.order_id in (select order_id from recent)) as last_paid
from customers c
join big b on b.customer_id = c.customer_id
left join regions r on r.id = c.region_id;

Paste it into the box on the SQL lineage analyzer. There is no database to connect and nothing to install.

What the graph shows

The graph draws tables, not query pieces. You see four tables feeding monthly_report: customers, orders, regions and payments. The two CTEs, recent and big, are not drawn as boxes. They are resolved into the final result, so you see the real tables the data comes from, not the scaffolding used to build it.

Joins

Every table you join becomes an upstream table of the result. Here customers, regions and orders (through the big CTE) are all linked to monthly_report. A join condition tells you how rows match, but it does not make a column of the result, so a column used only in ON, such as regions.id, is not a column link.

CTEs

A CTE is followed through, including when one CTE reads another. The spend column comes from big, which sums the amount from recent, which selects it from orders. The result links spend straight to orders.amount, and it knows the step in the middle was a calculation:

customers.customer_id -> monthly_report.customer_id   copied
customers.region      -> monthly_report.region        copied
orders.amount         -> monthly_report.spend         calculated: sum (amount)

That last line matters. A reader who sees spend marked as copied would assume it equals amount. The tool tells you it is a sum.

Subqueries

A subquery in the FROM clause works like a CTE: its columns are traced to their sources. A subquery inside a WHERE or IN clause adds its tables to the result's upstream tables, which is why payments is listed above, but it does not create a column.

What you will not see

  • **A column built by a scalar subquery in the SELECT list.** In the example, last_paid depends on payments.paid_at, but the tool links payments to the result at table level only. If that column matters, read it directly.
  • **Filters and join keys.** The order_date filter and the join keys are not column links.
  • **Dynamic SQL**, built from strings at run time, cannot be followed.

Reading a big query

Start with the tables: does the list match what you expected the query to use? An unexpected table is often a join someone forgot. Then open Columns, pick an output column and read what it comes from. For a whole folder of queries, see SQL lineage from a folder of SQL files, and for what changing a table would break, see SQL impact analysis.

Open the SQL Lineage Analyzer

Related tool pages

  • dbt Lineage Viewer for Your SQL Models: Drop your dbt models folder and see the lineage graph from sources through staging to marts, with column links. No dbt run, no warehouse. Free, private.
  • SQL Column Lineage Tool: See which source columns build every column of a view or table across many SQL files. Trace to the raw table or forward to dependants. Free, in your browser.
  • SQL File Dependency Analyzer: Find out which SQL file must run before which, from the tables each file creates and reads. Get a run order, circular dependencies and tables nothing uses.
  • SQL Impact Analysis: What Breaks If a Table Changes: Pick a table and see everything downstream that depends on it, and every upstream source it is built from. Works across a whole folder of SQL files, free.
  • SQL File Lineage Tool: Free SQL file lineage tool: drop a folder of .sql files and see which file feeds which table, column by column. Nothing is uploaded.
  • SQL Query Visualizer: Free SQL query visualizer: paste a query or drop SQL files and see tables, joins and columns as a clear diagram. Runs in your browser, no upload.

Also try

How to use the SQL Lineage Analyzer: step by step

  1. Paste the query into the SQL lineage analyzer.
  2. Read the tables that feed the result.
  3. Open Columns and click an output column to see its sources.

When to use the SQL Lineage Analyzer

  • Understand an inherited query
  • See which tables a report reads
  • Check where an output column comes from
  • Document a complex query

Example

Input: models/ folder: raw schema, staging views, marts, reports (7 .sql files)

Output: A graph of 10 tables and views, 40 column links and the order to run the files

Frequently asked questions

How do I see which tables a SQL query uses?

Paste the query into the SQL lineage analyzer. It lists and draws every table the query reads and the table it writes.

Are CTEs shown as separate nodes?

No. CTEs are resolved into the final result, so the graph shows the real tables the data comes from.

Does it trace columns through CTEs?

Yes. A column that passes through one or more CTEs is traced to its source column, and a calculation made inside a CTE is kept.

Does it handle subqueries?

Subqueries in FROM are traced like CTEs. A scalar subquery in the SELECT list is linked at table level only, not to a source column.

Do I need a database connection?

No. It reads the SQL text in your browser.

Tip: If a column is built by a subquery in the SELECT list, check it by hand. It is the one place where the table link is shown but the column link is not.