The Portable SQL Core: The 80% That Works Everywhere

Let’s pull out the part you can learn once and use everywhere — ClickHouse, Spark, and most SQL-speaking engines all take this. Everything else is engine-specific syntax on top.

Aggregation

• count(*) / sum() / avg() / min() / max() — the core aggregates.
  • Except for count(*), all of these ignore NULL values. → count(*) vs count(col)
  • Think of * as a whitelist — meaning it “includes NULL values.”
• CASE WHEN ... THEN ... ELSE ... END — the portable conditional.

Text

• upper() / lower() / trim() — text normalization.
• length(str) — character length.

Clause

• Some things are too basic to cover here, like SELECT/FROM/WHERE
• GROUP BY / HAVING / DISTINCT
  • DISTINCT: removes duplicate rows from the result, keeping one copy of each distinct combination of the selected columns.
  • GROUP BY: buckets rows sharing the same value(s) into one group, so each aggregate (count, sum, …) runs per group instead of over the whole table.
  • HAVING: filters the result after GROUP BY has already grouped the data.

Predicate

• IN (...) / BETWEEN / LIKE, ILIKE / IS NULL
  • BETWEEN: inclusive on both ends — works for numbers, text, and dates.
  • IS NULL: you can’t write xxx = NULL. (Why: NULL means “unknown,” so under SQL’s three-valued logic any comparison with it — including = NULL — evaluates to NULL, never TRUE, and the row is never returned; use IS NULL / IS NOT NULL instead.)
  • String-matching details for LIKE / ILIKE → LIKE / ILIKE substring search

---

References

• ClickHouse SQL Reference — https://clickhouse.com/docs/en/sql-reference
• Spark SQL Built-in Functions — https://spark.apache.org/docs/latest/api/sql/

Related: back to the Cross-Engine DB Query overview, or dig into count(*) vs count(col) and LIKE / ILIKE substring search.