Data Querying

Apache Doris supports standard SQL with strong MySQL compatibility, and on top of that provides high-performance join queries, a rich set of analytic functions, semi-structured data access, and user-defined functions. Choose the documentation that matches your scenario below.

SQL Compatibility

MySQL Compatibility

A quick reference to the differences between Doris and MySQL: covers data types, DDL/DML syntax, SQL functions, and key SQL Mode differences.

Multi-Table Joins

Joins (JOIN)

JOIN types such as INNER / LEFT / RIGHT / FULL / SEMI / ANTI, along with four distributed JOIN implementations: Broadcast, Shuffle, Bucket Shuffle, and Colocate.

ASOF JOIN for Time-Series Nearest Match

For each row in the left table, find the nearest row in the right table along a time direction, enabling time-series alignment without window functions.

Subqueries

Syntax, restrictions, and Mark Join handling for scalar, non-scalar, correlated, and uncorrelated subqueries.

Common Table Expressions (CTE)

Define temporary result sets with the WITH clause, with support for nested and recursive CTEs, suitable for hierarchy traversal, graph traversal, and similar scenarios.

Aggregation and Analytics

Multi-Dimensional Aggregation Analytics

Use ROLLUP, CUBE, and GROUPING SETS to perform multi-dimensional aggregation analytics in a single SQL statement.

Analytic Functions (Window Functions)

Use the OVER clause to partition and window result sets, supporting ranking, cumulative sums, moving averages, year-over-year and quarter-over-quarter comparisons, and more.

Semi-Structured and Complex Data

Querying Complex Types

Query complex types such as Array, Map, Struct, and JSON, and process semi-structured data with dedicated SQL functions.

Column-to-Row (Lateral View)

Use LATERAL VIEW with generator functions such as EXPLODE to expand a single row into multiple rows, enabling column-to-row SQL queries.

User-Defined Functions (UDF)

Java UDF / UDAF / UDWF / UDTF

Write UDF/UDAF/UDWF/UDTF user-defined functions in Java, including type mapping, registration syntax, best practices, and examples.

Python UDF / UDAF / UDWF / UDTF

Write UDF/UDAF/UDTF in Python: covers creation, vectorization, environment configuration, and common troubleshooting.

Alias Functions

Register a new signature for a function or expression fragment with CREATE ALIAS FUNCTION to improve migration compatibility and simplify writing complex queries.