Trino: The Definitive Guide

Chapter 9. Advanced SQL

While you can certainly achieve a lot with the power of SQL statements, as covered in Chapter 8, you are really only scratching the surface of what you can do with more complex processing with queries in Trino. In this chapter, you are going to cover more advanced features such as functions, operators, and other features.

Functions and Operators Introduction

So far, you’ve learned about the basics, including catalogs, schemas, tables, data types, and various SQL statements. This knowledge is useful when querying data from one or more tables in one or more catalogs in Trino. In the examples, we focused mostly on writing the queries by using the data from the different attributes, or columns, in the table.

SQL functions and operators exist to enable more complex and comprehensive SQL queries. In this chapter, we focus on the functions and operators supported by Trino and provide examples of their use.

Functions and operators in SQL are internally equivalent. Functions generally use the syntax form function_name(function_arg1, …) and operators use a different syntax, similar to operators in programming languages and mathematics. Operators are a syntactic abbreviation and improvement for commonly used functions. An example of an equivalent operator and function are the || operator and the concat() function. Both are used to concatenate strings.

Operators in SQL and in Trino come in two general types:

Binary operators

A binary operator takes the input of ...