SQL Basics — learning.lab

ClickHouse's query language is SQL, and if you've used Postgres or MySQL, most of what follows will look familiar. This page is the minimum vocabulary — databases, tables, inserting rows, selecting rows — plus the handful of places where ClickHouse's dialect quietly disagrees with what you'd expect from an OLTP database. Everything after this page assumes you're comfortable with what's here.

Databases and tables

A ClickHouse server holds multiple databases, each holding multiple tables — same nesting as Postgres or MySQL.

CREATE DATABASE IF NOT EXISTS learning;

SHOW DATABASES;
SHOW TABLES FROM learning;
DESCRIBE TABLE learning.events;  -- columns, types, codecs

The .env file from Docker & Setup already creates a learning database for you on first start, so in practice you'll mostly skip straight to creating tables inside it.

Creating a table

Every CREATE TABLE needs a column list, a ENGINE, and — for the engine family this whole module is built around — an ORDER BY:

CREATE TABLE learning.events
(
  user_id    UInt64,
  event      String,
  event_time DateTime
)
ENGINE = MergeTree
ORDER BY (user_id, event_time);

UInt64, String, and DateTime are three of ClickHouse's basic types — the full type system (signed/unsigned widths, LowCardinality, Nullable, arrays) is its own page: Data Types. What ENGINE and ORDER BY actually do is covered next, in MergeTree Engine and ORDER BY & Primary Keys.

Inserting rows

INSERT INTO learning.events (user_id, event, event_time) VALUES
  (102, 'click', '2026-08-05 10:01:00'),
  (204, 'view',  '2026-08-05 10:02:00'),
  (102, 'click', '2026-08-05 10:07:00');

VALUES is the simplest form. Real pipelines almost never use it row-by-row like this — see Insert & Export Formats for the formats you'd actually use, and Async Inserts & Batching for why single-row inserts specifically are a trap.

Selecting rows

Filtering, sorting, and limiting all read the way they do everywhere else:

SELECT user_id, event, event_time
FROM learning.events
WHERE event = 'click'
ORDER BY event_time DESC
LIMIT 10;

Aggregating

GROUP BY plus an aggregate function is the query shape ClickHouse is actually built to make fast over billions of rows — this is the workload described in Why ClickHouse:

SELECT
  user_id,
  count()          AS events,
  max(event_time)  AS last_seen
FROM learning.events
GROUP BY user_id
ORDER BY events DESC;

count() with no argument and no DISTINCT counts rows in the group; ClickHouse also accepts the standard count(*) spelling. sum(), avg(), min(), and max() all behave as expected. Far more specialized aggregate functions exist — that's its own page: Aggregate Combinators.

Where the dialect disagrees with what you'd expect

No auto-increment primary key. There's no SERIAL / AUTO_INCREMENT. IDs are generated by the application, or with a function like generateUUIDv4(), before the row is inserted.
Columns aren't nullable by default. A plain String column can never hold NULL — you have to opt in with Nullable(String), and doing so has a real storage and performance cost. Details in Data Types.
UPDATE and DELETE aren't the lightweight statements you're used to. They exist as ALTER TABLE ... UPDATE/DELETE mutations — async, heavyweight, rewrite-the-part operations, not row-level edits. Covered in Mutations (UPDATE / DELETE).
No foreign keys, no multi-table transactions. Joins work (Joins), but nothing enforces referential integrity, and there's no BEGIN/COMMIT/ROLLBACK spanning tables.

Common mistake

Because DELETE and UPDATE are expensive mutations rather than cheap row edits, reaching for them the way you would in Postgres — to fix a handful of rows here and there — is a common first mistake. If you find yourself doing it often, that usually means the schema or the pipeline needs to change, not that you need a faster mutation.

That's the whole vocabulary this module assumes going forward. Next up: MergeTree Engine — the table engine every CREATE TABLE above was quietly already using.