Query Tables in Zoho Analytics allow you to write SQL SELECT statements to define a virtual table based on one or more source tables. This gives you precise control over joins, filters, calculated columns, and data transformations that may not be possible through the standard drag-and-drop interface.

Creating a Query Table

1. Open your workspace and click New, then select Query Table.
2. In the query editor, write a SQL SELECT statement referencing your existing tables.
3. Click Run to preview the results.
4. Provide a name for the query table and click Save.

The resulting query table appears alongside your standard tables in the workspace and can be used as a data source for reports, charts, and dashboards.

Supported SQL Dialects

Zoho Analytics supports SQL syntax from a range of dialects. You can select your preferred dialect from the query editor settings:

• ANSI SQL
• Oracle
• SQL Server
• IBM DB2
• MySQL
• Sybase
• Informix
• PostgreSQL

Supported Operations

Query Tables support a comprehensive set of SQL operations:

• JOIN: Combine rows from two or more tables using INNER JOIN, LEFT JOIN, RIGHT JOIN, or FULL OUTER JOIN.
• UNION: Stack results from multiple SELECT statements, removing duplicate rows (use UNION ALL to retain duplicates).
• PIVOT: Transform row values into columns, making it easier to compare data across categories.
• Subqueries: Nest queries up to three levels deep to handle complex data requirements.
• CTEs (Common Table Expressions): Non-recursive CTEs are supported, with a maximum of three CTEs per query. Use the WITH clause to define named result sets that simplify complex queries.

Performance Considerations

Query Tables are evaluated each time a report built on them is loaded. To keep performance efficient:

• Filter data as early as possible using WHERE clauses to reduce the volume processed.
• Avoid selecting unnecessary columns in your SELECT statement.
• Keep nesting to a minimum; use three levels of subqueries only when genuinely required.
• Consider materialising frequently used query results into a standard table via scheduled imports if performance becomes a concern.