Standard SQL and Legacy SQL are two different dialects of SQL supported by Google BigQuery, each with its own syntax, features, and capabilities.
Here are the main differences between Standard SQL and Legacy SQL:
Syntax
- Standard SQL follows the ANSI SQL standard more closely and has a syntax that is more similar to other SQL database systems. It uses keywords like SELECT, FROM, WHERE, GROUP BY, ORDER BY, etc., in a familiar way.
- Legacy SQL has a syntax that is unique to BigQuery and may not be familiar to users accustomed to standard SQL syntax. For example, Legacy SQL uses the SELECT statement but also has some unique clauses like FLATTEN, TABLE_QUERY, etc.
Features
- Standard SQL provides a richer set of SQL features and functions, including support for window functions, common table expressions (CTEs), arrays, structs, and more advanced analytics functions.
- Legacy SQL has a more limited feature set compared to Standard SQL. Some advanced SQL features and functions available in Standard SQL may not be supported or have different implementations in Legacy SQL.
Compatibility
- Standard SQL is the preferred and recommended SQL dialect for new queries and projects in BigQuery. It offers better compatibility with other SQL database systems and is more aligned with industry standards.
- Legacy SQL is supported for backward compatibility with existing queries and projects. However, Google encourages users to migrate to Standard SQL for better functionality and performance.
Performance
- Standard SQL queries may benefit from improved performance optimizations and query execution plans compared to Legacy SQL queries.
- Legacy SQL queries may have limitations in terms of performance optimization and may not take advantage of newer enhancements and optimizations in BigQuery.
In summary, Standard SQL offers a more modern, feature-rich, and standard-compliant SQL dialect compared to Legacy SQL. It is recommended for new projects and queries, while Legacy SQL is supported for backward compatibility with existing projects.