Query Syntax This page provides reference information for the query syntax in TiDB Cloud Lake. Each component can be used individually or combined to build powerful queries.
Core Query Components Component Description SELECT Retrieve data from tables - the foundation of all queries FROM / JOIN Specify data sources and combine multiple tables WHERE Filter rows based on conditions GROUP BY Group rows and perform aggregations (SUM, COUNT, AVG, etc.) HAVING Filter grouped results ORDER BY Sort query results LIMIT / TOP Restrict the number of rows returned
Advanced Features Component Description WITH (CTE) Define reusable query blocks for complex logic PIVOT Convert rows to columns (wide format) UNPIVOT Convert columns to rows (long format) QUALIFY Filter rows after window function calculations VALUES Create inline temporary data sets
Time Travel & Streaming Component Description AT Query data at a specific point in time CHANGES Track insertions, updates, and deletions WITH CONSUME Process streaming data with offset management WITH STREAM HINTS Optimize stream processing behavior
Query Execution Component Description Settings Configure query optimization and execution parameters
Query Structure A typical TiDB Cloud Lake query follows this structure:
[WITH cte_expressions]
SELECT [TOP n] columns
FROM table
[JOIN other_tables]
[WHERE conditions]
[GROUP BY columns]
[HAVING group_conditions]
[QUALIFY window_conditions]
[ORDER BY columns]
[LIMIT n]