Code
SQL Query
Runs a SQL statement over the incoming data using an in-memory SQLite engine and returns the result as the node's rows.
When to use it
Use it when a join, a grouped aggregate or a filter is easier to express as one SQL statement than as a chain of separate nodes.
In a pipeline
File InputMySQL DatabaseSQL QueryExport CSV
Settings
| Setting | Type | Required | Default or hint |
|---|---|---|---|
| SQL Query | textarea | Yes | SELECT * FROM table1 t1 LEFT JOIN table2 t2 ON t1.id = t2.id |
Example
Join orders to customers
- SQL Query=SELECT c.Name, SUM(o.Total) AS Revenue FROM Orders o JOIN Customers c ON c.Id = o.CustomerId GROUP BY c.Name
| CustomerId | Total |
|---|---|
| 1 | 120 |
| 2 | 80 |
| 1 | 40 |
| Name | Revenue |
|---|---|
| Acme Ltd | 160 |
| Borne SA | 80 |
Each connected input becomes a table named after that node's label, so nodes labelled Orders and Customers are queried by those names.
Worth knowing
- Rename the incoming nodes to short names without spaces; anything that is not a letter, digit or underscore becomes an underscore.
- Every column is loaded as text, so cast before arithmetic, for example CAST(Total AS REAL).
- Only the first result set is returned, and duplicate column names in it are given a _2 suffix.
- An empty query passes the incoming rows through untouched.