Simplify SQL Workflows With New Features: Flow (->>) and UNION BY NAME
As data systems grow more complex, developers are increasingly tasked with building scalable SQL workflows that are both powerful and easy to maintain. Writing complex SQL can be challenging, especially when a large problem must be broken down into subtasks and then consolidated to generate a final result. This process often involves writing intricate Common Table Expressions (CTEs), storing intermediate results in temporary tables, using RESULT_SCAN() or carefully aggregating data from disparate tables. Fortunately, the following new SQL features can significantly simplify your experience:
Flow operator (
->>): Think Unix pipes, but for SQL.UNION [ALL] BY NAME: Mix and match queries without worrying about column order.
Chaining your commands with the Flow operator (->>)
We often need to run multiple SQL statements that rely on the results of the previously executed statement. This has typically involved steps such as writing intermediate results in temporary table(s), using RESULT_SCAN(), creating complex subqueries and so on. The new Flow operator (->>) simplifies this frequently encountered scenario. This operator behaves like Unix pipes, but for SQL. It lets you chain multiple SQL statements together, where each step’s output can be used by any of the future steps in the chain.
The benefits
Cleaner code: Your multistep logic becomes a single, readable pipeline.
Less clutter: No more writing intermediate results to temporary tables or using RESULT_SCAN().
Easy chaining: Use
$1,$2and so on to refer to the results of previous statements in the chain.Flexibility: Works with
SHOW,SELECT,DMLstatements and more.
Example: Analyzing sales data
Let's say you're a sales analyst trying to find your top-performing products in North America. First, let’s create some tables and add sample data.
CREATE OR REPLACE TABLE sales_by_region (
product_name VARCHAR,
region VARCHAR,
sales_amount DECIMAL(10, 2)
);
INSERT INTO sales_by_region VALUES
('SuperWidget', 'North America', 1501.00),
('MegaWidget', 'North America', 1500.00),
('GigaWidget', 'Europe', 3500.00),
('MegaWidget', 'South America', 1050.50);CREATE OR REPLACE TABLE sales_by_date (
prod VARCHAR(50),
sales_amt DECIMAL(10, 2),
sold_date DATE
);
INSERT INTO sales_by_date (prod, sales_amt, sold_date) VALUES
('SuperWidget', 500.25, '2025-06-10'),
('MegaWidget', 2550.50, '2025-06-11'),
('SuperWidget', 1000.75, '2025-06-12'),
('GigaWidget', 3500.00, '2025-06-15');CREATE OR REPLACE TABLE inventory (
prod VARCHAR(50),
location VARCHAR(50),
qty_avail INT,
last_updated DATE
);
INSERT INTO inventory (prod, location, qty_avail, last_updated) VALUES
('SuperWidget', 'Warehouse A', 250, '2025-06-01'),
('MegaWidget', 'Warehouse B', 120, '2025-06-01'),
('HyperWidget', 'Warehouse A', 75, '2025-06-05'),
('SuperWidget', 'Warehouse C', 150, '2025-06-20');Now, let’s build a simple query to illustrate the behavior of Flow operator:
SELECT product_name, SUM(sales_amount) as total_sales
FROM sales_by_region
WHERE region = 'North America'
GROUP BY product_name
->>
SELECT product_name, total_sales
FROM $1
ORDER BY total_sales DESC;The first query calculates the sales, and ->> pipes those results directly into the second query for sorting. There is no need to fetch results via intermediate steps.
Let’s try another one:
SHOW TABLES LIKE 'sales_by_region'
->>
SHOW TABLES LIKE 'sales_by_date'
->>
SHOW TABLES LIKE 'inventory'
->>
SELECT "created_on", "database_name", "schema_name", "name", "rows" FROM $1 UNION ALL
SELECT "created_on", "database_name", "schema_name", "name", "rows" FROM $2 UNION ALL
SELECT "created_on", "database_name", "schema_name", "name", "rows" FROM $3;In this example, multiple SHOW statements are chained together, with their results ultimately piped into a single SELECT statement using $1, $2 and $3.
Effortless data merging with UNION BY NAME
Traditionally, UNION matches columns by their position, forcing you to manually select and alias columns to line them up correctly. UNION BY NAME simplifies this error-prone process by matching columns by their name, not their position.
Why it matters
Schema evolution: Seamlessly handle tables where columns are added or reordered.
Automatic filling: Fills missing columns in one table with
NULLvalues automatically.No more manual aliasing: Avoids tedious and error-prone aliasing just to get a
UNIONto work.
Example: Consolidating product data
Imagine you're tracking product launches and inventory across two different systems. The schemas don't quite match up. Your goal is to create a consolidated timeline of all product-related events.
Let’s build a simple query to illustrate the behavior of UNION BY NAME using the same set of tables created in the previous example.
SELECT
prod,
sales_amt,
sold_date AS event_date,
'sale' AS event_type
FROM sales_by_date
UNION BY NAME
SELECT
prod,
location,
qty_avail,
last_updated AS event_date,
'inventory' AS event_type
FROM inventory
ORDER BY event_date DESC;The result is a timeline mixing sales and inventory events, with columns such as location, qty_avail and sales_amt correctly aligned and populated with NULLs where they don't exist.
Putting it all together: Flow and UNION BY NAME
Let’s imagine you need to create a consolidated alert list that highlights both high-value sales and products with high stock levels. This involves filtering data from two different tables and then combining them into a single report.
Here’s how you can achieve this using a combination of the Flow operator and UNION BY NAME:
-- Step 1: Identify all products with high-value sales (sales > $2500)
SELECT
prod,
'High-Value Sale' as flag,
sold_date as activity_date
FROM sales_by_date
WHERE sales_amt > 2500
-- Step 2: Pipe the high-value products and UNION them BY NAME with high-stock products (qty >= 150)
->>
(
SELECT * FROM $1 -- This refers to the high-value sales
UNION BY NAME
SELECT
prod,
'High-Stock Item' as flag,
last_updated as activity_date
FROM inventory
WHERE qty_avail >= 150
)
-- Step 3: Pipe the combined "alert list" into a final summary report
->>
SELECT
prod,
COUNT(*) as alert_count,
LISTAGG(DISTINCT flag, ' | ') as alert_reasons
FROM $1 -- This refers to the result of the UNION BY NAME operation
GROUP BY prod
ORDER BY alert_count DESC, prod;This powerful combination allows you to build complex data pipelines that integrate data from multiple sources and transform it efficiently, all within a clean and readable SQL structure.
To summarize
| Feature | Why it matters |
|---|---|
| ->> (Flow operator) | Cleanly chain SQL commands without needing temp tables or RESULT_SCAN. |
| UNION BY NAME | Combine tables with different schemas without tedious manual aliasing. |
Please feel free to play with these features in your environment and give us feedback. Additionally, we welcome your suggestions for innovative features you'd like us to develop next.
Author
