---
title: SQL reference
meta:
    description: SQL reference for Tinybird
---

# SQL reference

Tinybird supports the following statements, data types, and functions in queries.

## SQL statements

The only statement you can use in Tinybird's queries is `SELECT`. The SQL clauses for `SELECT` are fully supported.

All other SQL statements are handled by Tinybird's features.

## Data types

Tinybird supports a variety of data types to store and process different kinds of information efficiently. Data types define the kind of values that can be stored in a column and determine how those values can be used in queries and operations. See [Data types](/sql-reference/data-types).
 
{% snippet title="ingest-types" /%}

{% callout type="caution" %}
The `JSON` data type is in private beta. If you are interested in using this type, contact Tinybird at <support@tinybird.co> or in the [Community Slack](/community).
{% /callout %}

## Table engines

Table engines are a crucial component of Tinybird's data sources, defining how data is stored, indexed, and accessed. Each table engine is optimized for specific use cases, such as handling large volumes of data, providing high-speed read and write operations, or supporting complex queries and transactions.

Tinybird supports a variety of table engines, including:

- [MergeTree](/sql-reference/engines/mergetree): A general-purpose engine for storing and querying large datasets.
- [AggregatingMergeTree](/sql-reference/engines/aggregatingmergetree): Suitable for aggregating data and reducing storage volume.
- [ReplacingMergeTree](/sql-reference/engines/replacingmergetree): Ideal for deduplicating rows and removing duplicate entries.
- [SummingMergeTree](/sql-reference/engines/summingmergetree): Optimized for summarizing rows and reducing storage volume.
- [CollapsingMergeTree](/sql-reference/engines/collapsingmergetree): Designed for collapsing rows and deleting old object states in the background.
- [VersionedCollapsingMergeTree](/sql-reference/engines/versionedcollapsingmergetree): Allows for collapsing rows and deleting old object states in the background, with support for versioning.
- [Null](/sql-reference/engines/null): A special engine for not storing values.

Choosing the right table engine for your data source is essential for optimal performance, data integrity, and query efficiency.

## Functions

Tinybird provides a comprehensive set of built-in functions to help you transform and analyze your data effectively. These functions can be broadly categorized into:

- Aggregate functions: Perform calculations across rows and return a single value, like `count()`, `sum()`, `avg()`.
- String functions: Manipulate and analyze text data with operations like substring, concatenation, pattern matching.
- Date and time functions: Work with temporal data through date arithmetic, formatting, and time window operations.
- Mathematical functions: Handle numerical computations and transformations.
- Type conversion functions: Convert between different data types safely.
- Array functions: Operate on array columns with filtering, mapping, and reduction operations.
- Conditional functions: Implement if-then-else logic and case statements.
- Window functions: Perform calculations across a set of rows related to the current row.

See [Functions](/sql-reference/functions).

### Private beta

Tinybird supports the following table functions upon request:

- `mysql`
- `url`

## Settings

Tinybird supports the following settings:

- `aggregate_functions_null_for_empty`
- `join_use_nulls`
- `group_by_use_nulls`
- `join_algorithm`
- `date_time_output_format`
- `max_threads`
- `max_memory_usage`
- `max_execution_time`
- `use_skip_indexes`
- `optimize_move_to_prewhere`
- `query_plan_optimize_lazy_materialization`
- `min_bytes_to_use_direct_io`
- `enable_filesystem_cache`
- `use_query_cache`

You can use the settings by adding `SETTINGS=<value>` to the final node of your Pipe. For example:

```sql
SELECT id, country_id, name as country_name
FROM events e
LEFT JOIN country c ON e.country_id = c.id 
SETTINGS join_use_nulls = 1
```