Mastering IF‑ELSE Logic in MaxCompute SQL: A Practical Guide

MaxCompute, Alibaba Cloud's distributed big‑data processing platform, provides a next‑generation SQL engine (MaxCompute 2.0) that enhances compiler usability and language expressiveness.

Series of articles

First – Using MaxCompute compiler errors and warnings – https://developer.aliyun.com/article/225028

Second – New basic data types and built‑in functions – https://developer.aliyun.com/article/225027

Third – Complex types – https://developer.aliyun.com/article/225026

Fourth – CTE, VALUES, SEMIJOIN – https://developer.aliyun.com/article/225025

Fifth – SELECT TRANSFORM – https://developer.aliyun.com/article/637483

Sixth – User Defined Type – https://developer.aliyun.com/article/739232

Seventh – Grouping Set, Cube and Rollup – https://developer.aliyun.com/article/741255

Eighth – Dynamic type functions – https://developer.aliyun.com/article/742555

Ninth – Script mode and parameter view – https://developer.aliyun.com/article/748754

Script mode compiles the whole script as a single job, allowing the execution plan to be inspected and optimized; it also supports IF‑ELSE branching, enabling conditional logic such as date‑based routing or table‑size‑dependent strategies.

IF‑ELSE syntax

IF (condition) BEGIN
  statement1
  statement2
  ...
END

IF (condition) BEGIN
  statements
END ELSE IF (condition2) BEGIN
  statements
END ELSE BEGIN
  statements
END

Notes

If there is only one statement between BEGIN and END, the keywords can be omitted (similar to Java’s {}).

Multiple IF‑ELSE blocks can be nested.

Condition can be a Boolean expression or a Boolean scalar subquery.

Boolean expression example

When the condition can be resolved at compile time, the engine decides the branch early. The following diagram shows that only the selected branch is executed.

Scalar subquery example

If the condition depends on a subquery result, the decision is made at runtime, causing multiple jobs to be submitted.

The job execution graph shows two separate jobs: one for evaluating the condition and another for the chosen branch.

Full example

Complex SQL with IF‑ELSE to switch logic when table b is empty:

@a := select * from ta;
@b := SELECT tx.id, ty.c1, ty.c2 FROM (SELECT * FROM foo) tx
       JOIN (SELECT * FROM bar) ty ON tx.id2 = ty.id2;
@c := select * from tc;

IF (cast((select count(*) from @b) as int) == 0) BEGIN
  @select_expr := select a.id, c.c1, c.c2 ... from @a left outer join @c on a.id = c.id;
END ELSE BEGIN
  @select_expr := select a.id, greatest(b.c1, c.c1) as c1,
                 greatest(b.c2, c.c2) as c2 ... 
                 from @a a left outer join @b on a.id = b.id
                 left outer join @c on a.id = c.id;
END;

Conclusion

MaxCompute’s IF‑ELSE statements increase programming flexibility for big‑data SQL workloads, and the platform continues to improve SQL expressiveness for future releases.