--- title: "SQL for CSV: Query Your Data with SELECT | Prettify Docs" description: Query a CSV file in Prettify with SQL. Use SELECT, WHERE, GROUP BY, ORDER BY, aggregates, and functions. See the supported syntax and limits. url: https://prettify.tools/docs/sql --- [Docs](https://prettify.tools/docs) / Queries # SQL queries Last updated: October 16, 2026 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](https://prettify.tools/docs/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: ```sql 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: ```csv 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: ```sql SELECT name, salary WHERE department = 'Engineering' ORDER BY salary DESC ``` Top rows by a number: ```sql SELECT * WHERE age > 25 ORDER BY salary DESC LIMIT 2 ``` Unique values: ```sql SELECT DISTINCT department ``` Count all rows: ```sql SELECT COUNT(*) ``` Group and count: ```sql SELECT department, COUNT(*) GROUP BY department ``` Group, filter the groups, and order: ```sql SELECT department, COUNT(*) GROUP BY department HAVING COUNT(*) > 1 ORDER BY department ``` Totals per group: ```sql SELECT department, AVG(salary), MIN(salary), MAX(salary) GROUP BY department ``` Pattern match (case-insensitive): ```sql SELECT * WHERE name LIKE '%a%' ``` Range and list: ```sql SELECT * WHERE salary BETWEEN 120000 AND 180000 ``` ```sql SELECT * WHERE department IN ('Engineering', 'Design') ``` Scalar subquery: ```sql SELECT * WHERE salary > (SELECT AVG(salary)) ``` Compute a value per row, and a total over rows: ```sql SELECT name, salary / 12 AS monthly WHERE salary * 0.1 > 15000 ``` ```sql SELECT department, SUM(salary) / COUNT(*) AS average GROUP BY department ``` Transform text and branch: ```sql SELECT name, CASE WHEN age > 30 THEN 'senior' ELSE 'junior' END AS level ``` Pagination: ```sql 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](https://prettify.tools/docs/csv) - [Transforms](https://prettify.tools/docs/transforms) - [Conversion](https://prettify.tools/docs/conversion) - [Views](https://prettify.tools/docs/views) - [Pro features](https://prettify.tools/docs/pro) - [CSV Viewer tool](https://prettify.tools/csv-viewer) ## Try it online Tool pages for this topic. Each one runs in your browser. - [SQL Playground](https://prettify.tools/sql-playground) - [CSV Viewer](https://prettify.tools/csv-viewer) * [Previousjq queries](https://prettify.tools/docs/jq) * [Next XPath queries](https://prettify.tools/docs/xpath) ## Structured data ```json { "@context": "https://schema.org", "@type": "SoftwareApplication", "name": "Prettify", "url": "https://prettify.tools", "description": "Multi-format data viewer, editor, and query engine for JSON, YAML, CSV, XML, Mermaid, Markdown, MDX, HTML, and SVG", "applicationCategory": "DeveloperApplication", "operatingSystem": "Web, macOS, Windows, Linux", "offers": [ { "@type": "Offer", "price": "0", "priceCurrency": "USD", "description": "Free tier with core features" }, { "@type": "Offer", "price": "19.99", "priceCurrency": "USD", "description": "Pro: lifetime access to all features" } ], "publisher": { "@id": "https://prettify.tools/#organization" } } { "@context": "https://schema.org", "@type": "Organization", "@id": "https://prettify.tools/#organization", "name": "Prettify", "legalName": "Off Leash Labs", "url": "https://prettify.tools/", "logo": { "@type": "ImageObject", "url": "https://prettify.tools/icon-512.png", "width": 512, "height": 512 }, "contactPoint": { "@type": "ContactPoint", "contactType": "customer support", "email": "offleashlabs@gmail.com", "availableLanguage": "English" } } ```