Query Function in Sheets Versus SQL and What Maps to What
Compare the Google Sheets QUERY function with SQL: select, where, group by, order by, label and limit side by side, what QUERY lacks, and when to use which.
People who know SQL meet the Google Sheets QUERY function with relief and then irritation. It looks like SQL, it takes select and where and group by, and then it refuses a join, rejects a column name and drops values from a column for no visible reason. It is its own language, called the Google Visualization API Query Language, borrowed from charting. This article maps the common clauses to SQL, shows what is missing and says when to use each. It is a companion to Google Sheets alongside databases and uses the same ideas as translating Excel formulas to SQL.
The shape of the function
=QUERY(A1:E100, "select B, sum(D) where E = 'active' group by B order by sum(D) desc", 1)The three arguments are the data range, the query string and the number of header rows. The result is a table that spills into the cells below and to the right, and it recalculates when the source range changes.
The header argument matters: with 1, the first row is treated as headers and excluded from the data, and the result gets a header row of its own. If you pass the wrong number, or leave it out and let Sheets guess, numeric columns with a header may be read as text. Pass it explicitly every time.
Clause by clause
Suppose a range A1:E100 holds suppliers with columns code, name, country, spend and status.
| Task | QUERY | SQL |
|---|---|---|
| Choose columns | select A, B, D | SELECT code, name, spend FROM suppliers |
| Filter | where E = 'active' and D > 1000 | WHERE status = 'active' AND spend > 1000 |
| Sort | order by D desc | ORDER BY spend DESC |
| Limit | limit 10 | LIMIT 10 |
| Aggregate | select C, sum(D) group by C | SELECT country, sum(spend) ... GROUP BY country |
| Rename | label sum(D) 'Total spend' | sum(spend) AS "Total spend" |
| Pivot | select C, sum(D) group by C pivot E | needs conditional aggregation |
| Skip rows | offset 5 | OFFSET 5 |
| Text match | where B contains 'ltd' | WHERE name ILIKE '%ltd%' |
| Starts with | where B starts with 'A' | WHERE name LIKE 'A%' |
| Format | format D '#,##0.00' | formatting is the client's job |
Columns are named by their letter in the range (A, B, ...) when the data is a plain range. Inside an array built with { }, or when the data comes from another function, they are Col1, Col2 and so on. You cannot use the header text as a name, which makes queries harder to read than their SQL equivalents; keep a note of what each letter means.
A subtle difference: strings use single quotes, as in SQL, but the whole query string is in double quotes, so a literal containing a double quote needs escaping, and a cell reference inside the string must be concatenated:
=QUERY(A1:E100, "select A, D where C = '" & G1 & "'", 1)If G1 holds user text with a single quote, the query breaks or does something unintended. This is a small injection risk inside a sheet. Where the value is not trusted, clean it first with SUBSTITUTE(G1, "'", ""), or validate it against a list.
Dates and types
SQL infers types from column definitions. QUERY infers them from the data, per column, and the inference decides what you can do.
Dates need a literal with the date keyword in yyyy-mm-dd form:
=QUERY(A1:E100, "select * where D >= date '2026-01-01' and D < date '2026-02-01'", 1)Comparing a date column with a plain string fails. Booleans use true and false. Numbers are numbers.
The trap is the mixed column. If a column contains mostly numbers and a few text entries, QUERY treats it as numeric and returns the text cells as blanks. No error. The values vanish from the result, and a sum quietly omits them. The same happens the other way around. Before relying on a query, check the column: =COUNTA(D2:D100) against =COUNT(D2:D100) shows how many cells are not numbers. Clean the column or convert it first (detecting mixed types in an Excel column shows the same problem in workbooks).
What QUERY cannot do
The gaps are what push people toward a database.
No joins. A query reads one range. To combine two tables you build an array with
{A1:C100, F1:H100}side by side or useVLOOKUPandXLOOKUP-style functions first, and then query the combined range. It works for tidy cases and gets clumsy fast.No subqueries and no common table expressions. Chain queries by feeding the output range of one into another.
No window functions. There is no running total or rank in the query language; you do those with formulas or array functions.
**No
having.** Filter on an aggregate by wrapping the result in a secondQUERY.Limited functions. Scalar functions are few:
upper,lower,year,month,dayOfWeek,now,toDateand a handful of others. There are no string functions likesubstringorregexp.**No
select distinct.** Usegroup byor theUNIQUEfunction.No inserts or updates. It is read only.
For comparison, the same business question as a database query, with a join and a rank, is routine in SQL:
SELECT s.country, s.name, sum(p.amount) AS spend,
rank() OVER (PARTITION BY s.country ORDER BY sum(p.amount) DESC) AS rnk
FROM suppliers s
JOIN payments p ON p.supplier_id = s.id
WHERE s.status = 'active'
GROUP BY s.country, s.name;There is no direct QUERY equivalent, and building one in a sheet means helper columns and several formulas.
Pivot and label
Two clauses have no standard SQL equivalent. pivot turns the distinct values of a column into columns:
=QUERY(A1:E100, "select C, sum(D) group by C pivot E", 1)For countries as rows and statuses as columns, the cells hold spend. It replaces a pivot table for simple cases. In SQL you would write conditional aggregation, sum(spend) FILTER (WHERE status = 'active'), with one expression per column, which needs the values in advance (pivoting rows to columns in PostgreSQL shows that).
label renames output columns and format sets number formats, because the output is destined for a display:
=QUERY(A1:E100, "select C, sum(D) group by C label sum(D) 'Total spend' format sum(D) '#,##0'", 1)Performance and size
QUERY is fine for hundreds or a few thousand rows. It recalculates whenever its inputs change, and several queries over big ranges, chained together, make a sheet slow. Using whole-column references, like A:E, makes it scan the entire grid; give it the real range or use a bounded dynamic range. When a query result feeds a chart or another query, you have a dependency graph you can no longer see, which is how sheets become hard to maintain.
A database does the same work in milliseconds with indexes and a plan you can inspect. If the data starts in a database, run the query there and send the result to the sheet (publishing database results to a sheet automatically).
Choosing between them
Use QUERY when the data is already in the sheet, the question is a quick slice, the result is for human eyes and the people who maintain it know the sheet. A filtered view for a manager, a top ten, a quick pivot of last month's costs. It keeps everything in one place and needs no tooling.
Use SQL when the question needs a join, when the data is large, when the query must be repeatable and reviewed, when the logic needs to be shared across reports or when the data is in a database anyway. Version control for the query, tests against fixtures and a plan you can read all exist on that side (version controlling the query behind an Excel report).
When QUERY is the wrong tool and SQL is needed
QUERY is wrong when a silent type coercion could change a number that matters, because it does not tell you when a value disappears. It is wrong when the formula text is the only documentation of a business rule, and when the result feeds something that has legal or financial weight. Also, do not build a reporting system out of chained QUERY formulas across tabs. Move the logic to the database, keep the sheet as the display, and use QUERY only for the last-mile filtering that readers do themselves. The Google Visualization API Query Language reference documents the full grammar, and the Sheets function list entry for QUERY gives examples.
