SQL queries

The SQL panel runs SELECT queries over a CSV or TSV file. Use it to filter rows, compute totals, group records, and reshape a table without a spreadsheet. The queries run on your device. The SQL panel is a Pro feature. Free users get one free try of it per day.

The engine reads one table: the CSV file open in the editor. It supports a practical subset of SQL. This page lists the statements, clauses, functions, and limits, with examples.

Open the SQL panel

The SQL panel is available when a CSV or TSV file is open.

Open the panel in one of two ways:

1. In the toolbar, select SQL (in the Transform group).
2. Open the Command Palette with Cmd+K (Mac) or Ctrl+K (Windows, Linux), then run Query / Transform.

The panel title is SQL Query. A Pro badge marks the toolbar button for free users.

Run a query

Type a query in the input field. The engine evaluates it as you type. The result shows as a table. An error shows below the input field.

The panel also helps you build a query:

• Example buttons insert common queries, such as GROUP BY and COUNT(*).
• A guide lists query patterns, some based on your own columns.
• Recent shows queries you ran while the panel is open.

The panel keeps your last query for the session, so it returns when you reopen the panel. Prettify does not save queries to disk.

Select Copy to copy the result.

Apply a result to the document

The result can replace your current document.

1. Run a query that returns the rows you want.
2. Select Apply as new data, or press Cmd+Enter (Mac) or Ctrl+Enter (Windows, Linux).
3. Confirm Replace current data? in the dialog.

The CSV layout, such as the delimiter and the column order, is kept. Use History to go back to an earlier version.

Statements and clauses

The engine supports SELECT only. A query that starts with another word, such as UPDATE or DELETE, returns an error.

A query uses these clauses, in this order:

SELECT [DISTINCT] columns
[FROM table]
[WHERE conditions]
[GROUP BY columns]
[HAVING conditions]
[ORDER BY columns [ASC | DESC]]
[LIMIT n [OFFSET m]]

FROM is optional. There is one table: the open CSV. Any name after FROM is accepted and ignored.

Clause	Purpose
SELECT *	Return every column, in the file's column order.
SELECT a, b	Return named columns, in the listed order.
DISTINCT	Remove duplicate rows.
WHERE	Keep rows that match a condition.
GROUP BY	Group rows for aggregates. Accepts several columns.
HAVING	Keep groups that match a condition.
ORDER BY	Sort by one or more columns. DESC reverses order.
LIMIT n	Return at most n rows.
OFFSET m	Skip the first m rows.

Column names match without regard to letter case. Put text values in single quotes, for example 'Engineering'.

Examples

Every example below uses this file:

name,age,department,salary,active
Alice,30,Engineering,185000,true
Bob,25,Engineering,170000,false
Carol,35,Design,125000,true

Select columns, filter, sort, and limit:

SELECT name, salary WHERE department = 'Engineering' ORDER BY salary DESC

Top rows by a number:

SELECT * WHERE age > 25 ORDER BY salary DESC LIMIT 2

Unique values:

SELECT DISTINCT department

Count all rows:

SELECT COUNT(*)

Group and count:

SELECT department, COUNT(*) GROUP BY department

Group, filter the groups, and order:

SELECT department, COUNT(*) GROUP BY department HAVING COUNT(*) > 1 ORDER BY department

Totals per group:

SELECT department, AVG(salary), MIN(salary), MAX(salary) GROUP BY department

Pattern match (case-insensitive):

SELECT * WHERE name LIKE '%a%'

Range and list:

SELECT * WHERE salary BETWEEN 120000 AND 180000

SELECT * WHERE department IN ('Engineering', 'Design')

Scalar subquery:

SELECT * WHERE salary > (SELECT AVG(salary))

Compute a value per row, and a total over rows:

SELECT name, salary / 12 AS monthly WHERE salary * 0.1 > 15000

SELECT department, SUM(salary) / COUNT(*) AS average GROUP BY department

Transform text and branch:

SELECT name, CASE WHEN age > 30 THEN 'senior' ELSE 'junior' END AS level

Pagination:

SELECT * LIMIT 10 OFFSET 5

Aggregates

The engine supports COUNT, SUM, AVG, MIN, and MAX. Use them with GROUP BY, or on their own to aggregate the whole table.

• COUNT(*) counts rows.
• COUNT(column) counts non-empty values.
• COUNT(DISTINCT column) counts unique non-empty values.
• SUM, AVG, MIN, and MAX read numbers from a column.
• An aggregate can take an expression, such as SUM(price * quantity).
• An expression can combine aggregates, such as ROUND(AVG(salary) / 1000, 1).

Without an alias, an aggregate's output column keeps its text, such as COUNT(*) or AVG(salary). Add AS name to rename it.

Functions

Use these functions in the SELECT list.

Group	Functions
Text	UPPER(x), LOWER(x), TRIM(x), LENGTH(x), SUBSTR(x, start [, length]) (also SUBSTRING), CONCAT(a, b, ...), REPLACE(x, find, replace)
Numeric	ROUND(x [, decimals]), ABS(x), CEIL(x), FLOOR(x)
Null handling	COALESCE(a, b, ...) returns the first value that is not empty.
Branch	CASE WHEN condition THEN value [...] [ELSE value] END

SUBSTR counts the start position from 1.

Arithmetic

Use +, -, *, /, and % in the SELECT list, in WHERE and HAVING, and inside an aggregate. * and / come before + and -. Use parentheses to change the order.

• A cell is read as a number with parseFloat.
• An empty cell, a cell that is not a number, or a division by zero gives an empty result.
• Without an alias, the output column keeps the expression text, such as price * quantity.

Column names

Column names match without regard to letter case. A column name can contain spaces, such as unit price. To use such a name in arithmetic, put it in double quotes: "unit price" * quantity. A name in single quotes is a text value.

ORDER BY accepts a column that the SELECT list does not include, an alias, or a column position, such as ORDER BY 2.

Conditions

WHERE supports these operators:

Operator	Meaning
=, !=, <>	Equal or not equal, compared as text.
>, >=, <, <=	Compared as numbers.
LIKE, NOT LIKE	Pattern match. % is any run of characters. _ is one character. The match is case-insensitive and covers the whole value.
IN (a, b, ...), NOT IN	Value is one of a list, or of a subquery's first column.
BETWEEN x AND y	Value is in a range, inclusive.
IS NULL, IS NOT NULL	Empty or not empty.
NOT	Invert a condition.
AND, OR	Combine conditions. AND comes before OR.
( )	Group conditions, such as (a > 1 OR b = 'x') AND c = 'y'.

A LIKE pattern treats regex metacharacters as literal text.

HAVING and CASE WHEN support the same conditions as WHERE. In HAVING, the left side can be an aggregate, an alias, or a group column.

The left side of a condition is a column or an expression. The right side is a value: a column name there is read as text, so WHERE start = end compares start with the text end.

Limits

The engine is built for single-table CSV work. These SQL features are not supported:

• JOIN, UNION, INTERSECT, EXCEPT, and more than one table.
• Column-to-column comparison in a condition.
• Window functions (OVER), common table expressions (WITH), and a subquery in FROM.
• More than one statement in a query.

A query that uses one of these shows an error. So does a column the file does not have, a function the engine does not know, or a condition it cannot read. The engine never drops part of a query and shows the rest.

A subquery is supported as a value on the right side of a condition, and as the list of IN.

CSV cells are text. The engine reads a number with parseFloat for numeric comparison and for SUM, AVG, MIN, and MAX.

Related pages

• CSV in Prettify
• Transforms
• Conversion
• Views
• Pro features
• CSV Viewer tool