The Portable SQL Core: The 80% That Runs Anywhere

Here is the part you can learn once and use everywhere. ClickHouse, Spark, and most SQL-speaking engines all take it. 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. 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. It keeps one copy of each distinct combination of the selected columns.
  • GROUP BY: buckets rows sharing the same value(s) into one group. Each aggregate (count, sum, …) then 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 cannot write xxx = NULL. (Why: NULL means “unknown.” So under SQL’s three-valued logic, any comparison with it evaluates to NULL, never TRUE, and the row is never returned. This applies even to = NULL. 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.