Repository: sql-formatter-org/sql-formatter
Pull request #346 (2022-07-26 06:43:07)

Review and revise joins and set operations
Extended the `expandPhrases()` function with `{FOO | BAR}` syntax.

Used it to generate `JOIN` commands and set operations (`UNION`, `EXCEPT`, ...). Revised these lists based on the docs in wiki, which resulted in removal of support for various types of joins from many dialects. Likely these were there as a result of copy-paste creation of new dialects based on existing ones.

Notably moved `CROSS APPLY` and `OUTER APPLY` out of set operations. They work really more like joins.

Updated tests for joins and added tests for set operations, which previously had none.


Patch:
diff --git a/src/expandPhrases.ts b/src/expandPhrases.ts
index d69438ab..ae35222a 100644
--- a/src/expandPhrases.ts
+++ b/src/expandPhrases.ts
@@ -26,17 +26,19 @@ export const expandSinglePhrase = (phrase: string): string[] =>
 //   "TABLE"            --> ["TABLE"]
 //   "[TABLE]"          --> ["", "TABLE"]
 //   "[TEMP|TEMPORARY]" --> ["", "TEMP", "TEMPORARY"]
+//   "{TEMP|TEMPORARY}" --> ["TEMP", "TEMPORARY"]
 //
 type PhrasePart = string[];
 
-const REQUIRED_PART = /[^[\]]+/y;
-const OPTIONAL_PART = /\[.*?\]/y;
+const REQUIRED_PART = /[^[\]{}]+/y;
+const REQUIRED_BLOCK = /\{.*?\}/y;
+const OPTIONAL_BLOCK = /\[.*?\]/y;
 
 const parsePhrase = (text: string): PhrasePart[] => {
   let index = 0;
   const result: PhrasePart[] = [];
   while (index < text.length) {
-    // Match everything else outside of "[...]" blocks
+    // Match everything else outside of "[...]" or "{...}" blocks
     REQUIRED_PART.lastIndex = index;
     const requiredMatch = REQUIRED_PART.exec(text);
     if (requiredMatch) {
@@ -45,18 +47,30 @@ const parsePhrase = (text: string): PhrasePart[] => {
     }
 
     // Match "[...]" block
-    OPTIONAL_PART.lastIndex = index;
-    const optionalMatch = OPTIONAL_PART.exec(text);
-    if (optionalMatch) {
-      const choices = optionalMatch[0]
+    OPTIONAL_BLOCK.lastIndex = index;
+    const optionalBlockMatch = OPTIONAL_BLOCK.exec(text);
+    if (optionalBlockMatch) {
+      const choices = optionalBlockMatch[0]
         .slice(1, -1)
         .split('|')
         .map(s => s.trim());
       result.push(['', ...choices]);
-      index += optionalMatch[0].length;
+      index += optionalBlockMatch[0].length;
     }
 
-    if (!requiredMatch && !optionalMatch) {
+    // Match "{...}" block
+    REQUIRED_BLOCK.lastIndex = index;
+    const requiredBlockMatch = REQUIRED_BLOCK.exec(text);
+    if (requiredBlockMatch) {
+      const choices = requiredBlockMatch[0]
+        .slice(1, -1)
+        .split('|')
+        .map(s => s.trim());
+      result.push(choices);
+      index += requiredBlockMatch[0].length;
+    }
+
+    if (!requiredMatch && !optionalBlockMatch && !requiredBlockMatch) {
       throw new Error(`Unbalanced parenthesis in: ${text}`);
     }
   }
diff --git a/src/languages/bigquery/bigquery.formatter.ts b/src/languages/bigquery/bigquery.formatter.ts
index d7e85d34..06565a1d 100644
--- a/src/languages/bigquery/bigquery.formatter.ts
+++ b/src/languages/bigquery/bigquery.formatter.ts
@@ -106,15 +106,15 @@ const reservedCommands = expandPhrases([
 ]);
 
 const reservedBinaryCommands = expandPhrases([
-  'INTERSECT [ALL | DISTINCT]',
-  'UNION [ALL | DISTINCT]',
-  'EXCEPT [ALL | DISTINCT]',
+  'UNION {ALL | DISTINCT}',
+  'EXCEPT DISTINCT',
+  'INTERSECT DISTINCT',
 ]);
 
 const reservedJoins = expandPhrases([
-  '[LEFT | RIGHT | FULL] [OUTER] JOIN',
-  'INNER JOIN',
-  'CROSS JOIN',
+  'JOIN',
+  '{LEFT | RIGHT | FULL} [OUTER] JOIN',
+  '{INNER | CROSS} JOIN',
 ]);
 
 // https://cloud.google.com/bigquery/docs/reference/#standard-sql-reference
diff --git a/src/languages/db2/db2.formatter.ts b/src/languages/db2/db2.formatter.ts
index 81de72b4..9ee41b7e 100644
--- a/src/languages/db2/db2.formatter.ts
+++ b/src/languages/db2/db2.formatter.ts
@@ -1,3 +1,4 @@
+import { expandPhrases } from 'src/expandPhrases';
 import Formatter from 'src/formatter/Formatter';
 import Tokenizer from 'src/lexer/Tokenizer';
 import { functions } from './db2.functions';
@@ -140,30 +141,13 @@ const reservedCommands = [
   'WITH',
 ];
 
-const reservedBinaryCommands = [
-  'INTERSECT',
-  'INTERSECT ALL',
-  'INTERSECT DISTINCT',
-  'UNION',
-  'UNION ALL',
-  'UNION DISTINCT',
-  'EXCEPT',
-  'EXCEPT ALL',
-  'EXCEPT DISTINCT',
-];
+const reservedBinaryCommands = expandPhrases(['UNION [ALL]', 'EXCEPT [ALL]', 'INTERSECT [ALL]']);
 
-const reservedJoins = [
+const reservedJoins = expandPhrases([
   'JOIN',
-  'INNER JOIN',
-  'LEFT JOIN',
-  'LEFT OUTER JOIN',
-  'RIGHT JOIN',
-  'RIGHT OUTER JOIN',
-  'FULL JOIN',
-  'FULL OUTER JOIN',
-  'CROSS JOIN',
-  'NATURAL JOIN',
-];
+  '{LEFT | RIGHT | FULL} [OUTER] JOIN',
+  '{INNER | CROSS} JOIN',
+]);
 
 // https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_72/db2/rbafzintro.htm
 export default class Db2Formatter extends Formatter {
diff --git a/src/languages/hive/hive.formatter.ts b/src/languages/hive/hive.formatter.ts
index 7e75de23..a849539d 100644
--- a/src/languages/hive/hive.formatter.ts
+++ b/src/languages/hive/hive.formatter.ts
@@ -1,3 +1,4 @@
+import { expandPhrases } from 'src/expandPhrases';
 import Formatter from 'src/formatter/Formatter';
 import Tokenizer from 'src/lexer/Tokenizer';
 import { functions } from './hive.functions';
@@ -42,26 +43,15 @@ const reservedCommands = [
   'ROW FORMAT',
 ];
 
-const reservedBinaryCommands = [
-  'INTERSECT',
-  'INTERSECT ALL',
-  'INTERSECT DISTINCT',
-  'UNION',
-  'UNION ALL',
-  'UNION DISTINCT',
-];
+const reservedBinaryCommands = expandPhrases(['UNION [ALL | DISTINCT]']);
 
-const reservedJoins = [
+const reservedJoins = expandPhrases([
   'JOIN',
-  'INNER JOIN',
-  'LEFT JOIN',
-  'LEFT OUTER JOIN',
-  'RIGHT JOIN',
-  'RIGHT OUTER JOIN',
-  'FULL JOIN',
-  'FULL OUTER JOIN',
-  'CROSS JOIN',
-];
+  '{LEFT | RIGHT | FULL} [OUTER] JOIN',
+  '{INNER | CROSS} JOIN',
+  // non-standard joins
+  'LEFT SEMI JOIN',
+]);
 
 // https://cwiki.apache.org/confluence/display/Hive/LanguageManual
 export default class HiveFormatter extends Formatter {
diff --git a/src/languages/mariadb/mariadb.formatter.ts b/src/languages/mariadb/mariadb.formatter.ts
index e9733a23..4edc902a 100644
--- a/src/languages/mariadb/mariadb.formatter.ts
+++ b/src/languages/mariadb/mariadb.formatter.ts
@@ -1,3 +1,4 @@
+import { expandPhrases } from 'src/expandPhrases';
 import Formatter from 'src/formatter/Formatter';
 import Tokenizer from 'src/lexer/Tokenizer';
 import { EOF_TOKEN, isToken, type Token, TokenType } from 'src/lexer/token';
@@ -223,37 +224,22 @@ const reservedCommands = [
   'WHERE',
 ];
 
-const reservedBinaryCommands = [
-  'INTERSECT',
-  'INTERSECT ALL',
-  'INTERSECT DISTINCT',
-  'UNION',
-  'UNION ALL',
-  'UNION DISTINCT',
-  'EXCEPT',
-  'EXCEPT ALL',
-  'EXCEPT DISTINCT',
-  'MINUS',
-  'MINUS ALL',
-  'MINUS DISTINCT',
-];
+const reservedBinaryCommands = expandPhrases([
+  'UNION [ALL | DISTINCT]',
+  'EXCEPT [ALL | DISTINCT]',
+  'INTERSECT [ALL | DISTINCT]',
+  'MINUS [ALL | DISTINCT]',
+]);
 
-const reservedJoins = [
+const reservedJoins = expandPhrases([
   'JOIN',
-  'INNER JOIN',
-  'LEFT JOIN',
-  'LEFT OUTER JOIN',
-  'RIGHT JOIN',
-  'RIGHT OUTER JOIN',
-  'CROSS JOIN',
+  '{LEFT | RIGHT} [OUTER] JOIN',
+  '{INNER | CROSS} JOIN',
   'NATURAL JOIN',
+  'NATURAL {LEFT | RIGHT} [OUTER] JOIN',
   // non-standard joins
   'STRAIGHT_JOIN',
-  'NATURAL LEFT JOIN',
-  'NATURAL LEFT OUTER JOIN',
-  'NATURAL RIGHT JOIN',
-  'NATURAL RIGHT OUTER JOIN',
-];
+]);
 
 // For reference: https://mariadb.com/kb/en/sql-statements-structure/
 export default class MariaDbFormatter extends Formatter {
diff --git a/src/languages/mysql/mysql.formatter.ts b/src/languages/mysql/mysql.formatter.ts
index da167dbe..d4166e54 100644
--- a/src/languages/mysql/mysql.formatter.ts
+++ b/src/languages/mysql/mysql.formatter.ts
@@ -1,3 +1,4 @@
+import { expandPhrases } from 'src/expandPhrases';
 import Formatter from 'src/formatter/Formatter';
 import Tokenizer from 'src/lexer/Tokenizer';
 import { EOF_TOKEN, isToken, type Token, TokenType } from 'src/lexer/token';
@@ -196,34 +197,18 @@ const reservedCommands = [
   'PARTITION BY',
 ];
 
-const reservedBinaryCommands = [
-  'INTERSECT',
-  'INTERSECT ALL',
-  'INTERSECT DISTINCT',
-  'UNION',
-  'UNION ALL',
-  'UNION DISTINCT',
-  'EXCEPT',
-  'EXCEPT ALL',
-  'EXCEPT DISTINCT',
-];
+const reservedBinaryCommands = expandPhrases(['UNION [ALL | DISTINCT]']);
 
-const reservedJoins = [
+const reservedJoins = expandPhrases([
   'JOIN',
-  'INNER JOIN',
-  'LEFT JOIN',
-  'LEFT OUTER JOIN',
-  'RIGHT JOIN',
-  'RIGHT OUTER JOIN',
-  'CROSS JOIN',
+  '{LEFT | RIGHT} [OUTER] JOIN',
+  '{INNER | CROSS} JOIN',
   'NATURAL JOIN',
+  'NATURAL {LEFT | RIGHT} [OUTER] JOIN',
+  'NATURAL INNER JOIN',
   // non-standard joins
   'STRAIGHT_JOIN',
-  'NATURAL LEFT JOIN',
-  'NATURAL LEFT OUTER JOIN',
-  'NATURAL RIGHT JOIN',
-  'NATURAL RIGHT OUTER JOIN',
-];
+]);
 
 // https://dev.mysql.com/doc/refman/8.0/en/
 export default class MySqlFormatter extends Formatter {
diff --git a/src/languages/n1ql/n1ql.formatter.ts b/src/languages/n1ql/n1ql.formatter.ts
index 80fc8c21..8c26591e 100644
--- a/src/languages/n1ql/n1ql.formatter.ts
+++ b/src/languages/n1ql/n1ql.formatter.ts
@@ -1,3 +1,4 @@
+import { expandPhrases } from 'src/expandPhrases';
 import Formatter from 'src/formatter/Formatter';
 import Tokenizer from 'src/lexer/Tokenizer';
 import { functions } from './n1ql.functions';
@@ -64,29 +65,9 @@ const reservedCommands = [
   'PARTITION BY',
 ];
 
-const reservedBinaryCommands = [
-  'INTERSECT',
-  'INTERSECT ALL',
-  'INTERSECT DISTINCT',
-  'UNION',
-  'UNION ALL',
-  'UNION DISTINCT',
-  'EXCEPT',
-  'EXCEPT ALL',
-  'EXCEPT DISTINCT',
-  'MINUS',
-  'MINUS ALL',
-  'MINUS DISTINCT',
-];
+const reservedBinaryCommands = expandPhrases(['UNION [ALL]', 'EXCEPT [ALL]', 'INTERSECT [ALL]']);
 
-const reservedJoins = [
-  'JOIN',
-  'INNER JOIN',
-  'LEFT JOIN',
-  'LEFT OUTER JOIN',
-  'RIGHT JOIN',
-  'RIGHT OUTER JOIN',
-];
+const reservedJoins = expandPhrases(['JOIN', '{LEFT | RIGHT} [OUTER] JOIN', 'INNER JOIN']);
 
 // For reference: http://docs.couchbase.com.s3-website-us-west-1.amazonaws.com/server/6.0/n1ql/n1ql-language-reference/index.html
 export default class N1qlFormatter extends Formatter {
diff --git a/src/languages/plsql/plsql.formatter.ts b/src/languages/plsql/plsql.formatter.ts
index ab3f747e..46310208 100644
--- a/src/languages/plsql/plsql.formatter.ts
+++ b/src/languages/plsql/plsql.formatter.ts
@@ -1,3 +1,4 @@
+import { expandPhrases } from 'src/expandPhrases';
 import Formatter from 'src/formatter/Formatter';
 import Tokenizer from 'src/lexer/Tokenizer';
 import { EOF_TOKEN, isReserved, isToken, type Token, TokenType } from 'src/lexer/token';
@@ -40,37 +41,18 @@ const reservedCommands = [
   'WITH',
 ];
 
-const reservedBinaryCommands = [
-  // set booleans
-  'INTERSECT',
-  'INTERSECT ALL',
-  'INTERSECT DISTINCT',
-  'UNION',
-  'UNION ALL',
-  'UNION DISTINCT',
-  'EXCEPT',
-  'EXCEPT ALL',
-  'EXCEPT DISTINCT',
-  'MINUS',
-  'MINUS ALL',
-  'MINUS DISTINCT',
-  // apply
-  'CROSS APPLY',
-  'OUTER APPLY',
-];
+const reservedBinaryCommands = expandPhrases(['UNION [ALL]', 'EXCEPT', 'INTERSECT']);
 
-const reservedJoins = [
+const reservedJoins = expandPhrases([
   'JOIN',
-  'INNER JOIN',
-  'LEFT JOIN',
-  'LEFT OUTER JOIN',
-  'RIGHT JOIN',
-  'RIGHT OUTER JOIN',
-  'FULL JOIN',
-  'FULL OUTER JOIN',
-  'CROSS JOIN',
+  '{LEFT | RIGHT | FULL} [OUTER] JOIN',
+  '{INNER | CROSS} JOIN',
   'NATURAL JOIN',
-];
+  'NATURAL INNER JOIN',
+  'NATURAL {LEFT | RIGHT | FULL} [OUTER] JOIN',
+  // non-standard joins
+  '{CROSS | OUTER} APPLY',
+]);
 
 export default class PlSqlFormatter extends Formatter {
   static operators = [
diff --git a/src/languages/postgresql/postgresql.formatter.ts b/src/languages/postgresql/postgresql.formatter.ts
index 6f0c208c..42426815 100644
--- a/src/languages/postgresql/postgresql.formatter.ts
+++ b/src/languages/postgresql/postgresql.formatter.ts
@@ -1,3 +1,4 @@
+import { expandPhrases } from 'src/expandPhrases';
 import Formatter from 'src/formatter/Formatter';
 import Tokenizer from 'src/lexer/Tokenizer';
 import { functions } from './postgresql.functions';
@@ -206,33 +207,20 @@ const reservedCommands = [
   'PARTITION BY',
 ];
 
-const reservedBinaryCommands = [
-  'INTERSECT',
-  'INTERSECT ALL',
-  'INTERSECT DISTINCT',
-  'UNION',
-  'UNION ALL',
-  'UNION DISTINCT',
-  'EXCEPT',
-  'EXCEPT ALL',
-  'EXCEPT DISTINCT',
-  'MINUS',
-  'MINUS ALL',
-  'MINUS DISTINCT',
-];
+const reservedBinaryCommands = expandPhrases([
+  'UNION [ALL | DISTINCT]',
+  'EXCEPT [ALL | DISTINCT]',
+  'INTERSECT [ALL | DISTINCT]',
+]);
 
-const reservedJoins = [
+const reservedJoins = expandPhrases([
   'JOIN',
-  'INNER JOIN',
-  'LEFT JOIN',
-  'LEFT OUTER JOIN',
-  'RIGHT JOIN',
-  'RIGHT OUTER JOIN',
-  'FULL JOIN',
-  'FULL OUTER JOIN',
-  'CROSS JOIN',
+  '{LEFT | RIGHT | FULL} [OUTER] JOIN',
+  '{INNER | CROSS} JOIN',
   'NATURAL JOIN',
-];
+  'NATURAL INNER JOIN',
+  'NATURAL {LEFT | RIGHT | FULL} [OUTER] JOIN',
+]);
 
 const binaryOperators = [
   // Math Operators
diff --git a/src/languages/redshift/redshift.formatter.ts b/src/languages/redshift/redshift.formatter.ts
index 2fd76174..7b757dc2 100644
--- a/src/languages/redshift/redshift.formatter.ts
+++ b/src/languages/redshift/redshift.formatter.ts
@@ -1,3 +1,4 @@
+import { expandPhrases } from 'src/expandPhrases';
 import Formatter from 'src/formatter/Formatter';
 import Tokenizer from 'src/lexer/Tokenizer';
 import { functions } from './redshift.functions';
@@ -105,30 +106,16 @@ const reservedCommands = [
   'SET SCHEMA', // verify
 ];
 
-const reservedBinaryCommands = [
-  'INTERSECT',
-  'INTERSECT ALL',
-  'INTERSECT DISTINCT',
-  'UNION',
-  'UNION ALL',
-  'UNION DISTINCT',
-  'EXCEPT',
-  'EXCEPT ALL',
-  'EXCEPT DISTINCT',
-];
+const reservedBinaryCommands = expandPhrases(['UNION [ALL]', 'EXCEPT', 'INTERSECT', 'MINUS']);
 
-const reservedJoins = [
+const reservedJoins = expandPhrases([
   'JOIN',
-  'INNER JOIN',
-  'LEFT JOIN',
-  'LEFT OUTER JOIN',
-  'RIGHT JOIN',
-  'RIGHT OUTER JOIN',
-  'FULL JOIN',
-  'FULL OUTER JOIN',
-  'CROSS JOIN',
+  '{LEFT | RIGHT | FULL} [OUTER] JOIN',
+  '{INNER | CROSS} JOIN',
   'NATURAL JOIN',
-];
+  'NATURAL INNER JOIN',
+  'NATURAL {LEFT | RIGHT | FULL} [OUTER] JOIN',
+]);
 
 // https://docs.aws.amazon.com/redshift/latest/dg/cm_chap_SQLCommandRef.html
 export default class RedshiftFormatter extends Formatter {
diff --git a/src/languages/spark/spark.formatter.ts b/src/languages/spark/spark.formatter.ts
index 4c07dadb..a9d8334f 100644
--- a/src/languages/spark/spark.formatter.ts
+++ b/src/languages/spark/spark.formatter.ts
@@ -1,3 +1,4 @@
+import { expandPhrases } from 'src/expandPhrases';
 import Formatter from 'src/formatter/Formatter';
 import Tokenizer from 'src/lexer/Tokenizer';
 import { EOF_TOKEN, isToken, type Token, TokenType } from 'src/lexer/token';
@@ -80,54 +81,23 @@ const reservedCommands = [
   'WINDOW',
 ];
 
-const reservedBinaryCommands = [
-  // set booleans
-  'INTERSECT',
-  'INTERSECT ALL',
-  'INTERSECT DISTINCT',
-  'UNION',
-  'UNION ALL',
-  'UNION DISTINCT',
-  'EXCEPT',
-  'EXCEPT ALL',
-  'EXCEPT DISTINCT',
-  'MINUS',
-  'MINUS ALL',
-  'MINUS DISTINCT',
-  // apply
-  'CROSS APPLY',
-  'OUTER APPLY',
-];
+const reservedBinaryCommands = expandPhrases([
+  'UNION [ALL | DISTINCT]',
+  'EXCEPT [ALL | DISTINCT]',
+  'INTERSECT [ALL | DISTINCT]',
+]);
 
-const reservedJoins = [
+const reservedJoins = expandPhrases([
   'JOIN',
-  'INNER JOIN',
-  'LEFT JOIN',
-  'LEFT OUTER JOIN',
-  'RIGHT JOIN',
-  'RIGHT OUTER JOIN',
-  'FULL JOIN',
-  'FULL OUTER JOIN',
-  'CROSS JOIN',
+  '{LEFT | RIGHT | FULL} [OUTER] JOIN',
+  '{INNER | CROSS} JOIN',
   'NATURAL JOIN',
-  // non-standard-joins
-  'ANTI JOIN',
-  'SEMI JOIN',
-  'LEFT ANTI JOIN',
-  'LEFT SEMI JOIN',
-  'RIGHT OUTER JOIN',
-  'RIGHT SEMI JOIN',
-  'NATURAL ANTI JOIN',
-  'NATURAL FULL OUTER JOIN',
   'NATURAL INNER JOIN',
-  'NATURAL LEFT ANTI JOIN',
-  'NATURAL LEFT OUTER JOIN',
-  'NATURAL LEFT SEMI JOIN',
-  'NATURAL OUTER JOIN',
-  'NATURAL RIGHT OUTER JOIN',
-  'NATURAL RIGHT SEMI JOIN',
-  'NATURAL SEMI JOIN',
-];
+  'NATURAL {LEFT | RIGHT | FULL} [OUTER] JOIN',
+  // non-standard-joins
+  '[LEFT] {ANTI | SEMI} JOIN',
+  'NATURAL [LEFT] {ANTI | SEMI} JOIN',
+]);
 
 // http://spark.apache.org/docs/latest/sql-programming-guide.html
 export default class SparkFormatter extends Formatter {
diff --git a/src/languages/sql/sql.formatter.ts b/src/languages/sql/sql.formatter.ts
index e9fb06a8..59c1dcf7 100644
--- a/src/languages/sql/sql.formatter.ts
+++ b/src/languages/sql/sql.formatter.ts
@@ -1,3 +1,4 @@
+import { expandPhrases } from 'src/expandPhrases';
 import Formatter from 'src/formatter/Formatter';
 import Tokenizer from 'src/lexer/Tokenizer';
 import { functions } from './sql.functions';
@@ -34,30 +35,20 @@ const reservedCommands = [
   'PARTITION BY',
 ];
 
-const reservedBinaryCommands = [
-  'INTERSECT',
-  'INTERSECT ALL',
-  'INTERSECT DISTINCT',
-  'UNION',
-  'UNION ALL',
-  'UNION DISTINCT',
-  'EXCEPT',
-  'EXCEPT ALL',
-  'EXCEPT DISTINCT',
-];
+const reservedBinaryCommands = expandPhrases([
+  'UNION [ALL | DISTINCT]',
+  'EXCEPT [ALL | DISTINCT]',
+  'INTERSECT [ALL | DISTINCT]',
+]);
 
-const reservedJoins = [
+const reservedJoins = expandPhrases([
   'JOIN',
-  'INNER JOIN',
-  'LEFT JOIN',
-  'LEFT OUTER JOIN',
-  'RIGHT JOIN',
-  'RIGHT OUTER JOIN',
-  'FULL JOIN',
-  'FULL OUTER JOIN',
-  'CROSS JOIN',
+  '{LEFT | RIGHT | FULL} [OUTER] JOIN',
+  '{INNER | CROSS} JOIN',
   'NATURAL JOIN',
-];
+  'NATURAL INNER JOIN',
+  'NATURAL {LEFT | RIGHT | FULL} [OUTER] JOIN',
+]);
 
 export default class SqlFormatter extends Formatter {
   static operators = [];
diff --git a/src/languages/sqlite/sqlite.formatter.ts b/src/languages/sqlite/sqlite.formatter.ts
index 8e0a9486..9b2d77a2 100644
--- a/src/languages/sqlite/sqlite.formatter.ts
+++ b/src/languages/sqlite/sqlite.formatter.ts
@@ -1,3 +1,4 @@
+import { expandPhrases } from 'src/expandPhrases';
 import Formatter from 'src/formatter/Formatter';
 import Tokenizer from 'src/lexer/Tokenizer';
 import { functions } from './sqlite.functions';
@@ -35,31 +36,17 @@ const reservedCommands = [
   'PARTITION BY',
 ];
 
-const reservedBinaryCommands = [
-  'INTERSECT',
-  'INTERSECT ALL',
-  'INTERSECT DISTINCT',
-  'UNION',
-  'UNION ALL',
-  'UNION DISTINCT',
-  'EXCEPT',
-  'EXCEPT ALL',
-  'EXCEPT DISTINCT',
-];
+const reservedBinaryCommands = expandPhrases(['UNION [ALL]', 'EXCEPT', 'INTERSECT']);
 
 // joins - https://www.sqlite.org/syntax/join-operator.html
-const reservedJoins = [
+const reservedJoins = expandPhrases([
   'JOIN',
-  'LEFT JOIN',
-  'LEFT OUTER JOIN',
-  'INNER JOIN',
-  'CROSS JOIN',
+  '{LEFT | RIGHT | FULL} [OUTER] JOIN',
+  '{INNER | CROSS} JOIN',
   'NATURAL JOIN',
-  'NATURAL LEFT JOIN',
-  'NATURAL LEFT OUTER JOIN',
   'NATURAL INNER JOIN',
-  'NATURAL CROSS JOIN',
-];
+  'NATURAL {LEFT | RIGHT | FULL} [OUTER] JOIN',
+]);
 
 export default class SqliteFormatter extends Formatter {
   // https://www.sqlite.org/lang_expr.html
diff --git a/src/languages/trino/trino.formatter.ts b/src/languages/trino/trino.formatter.ts
index a7939cf4..28af7209 100644
--- a/src/languages/trino/trino.formatter.ts
+++ b/src/languages/trino/trino.formatter.ts
@@ -1,3 +1,4 @@
+import { expandPhrases } from 'src/expandPhrases';
 import Formatter from 'src/formatter/Formatter';
 import Tokenizer from 'src/lexer/Tokenizer';
 import { functions } from './trino.functions';
@@ -100,39 +101,21 @@ const reservedCommands = [
 
 // https://github.com/trinodb/trino/blob/432d2897bdef99388c1a47188743a061c4ac1f34/core/trino-parser/src/main/antlr4/io/trino/sql/parser/SqlBase.g4#L231-L235
 // https://github.com/trinodb/trino/blob/432d2897bdef99388c1a47188743a061c4ac1f34/core/trino-parser/src/main/antlr4/io/trino/sql/parser/SqlBase.g4#L288-L291
-const reservedBinaryCommands = [
-  // set booleans
-  'INTERSECT',
-  'INTERSECT ALL',
-  'INTERSECT DISTINCT',
-  'UNION',
-  'UNION ALL',
-  'UNION DISTINCT',
-  'EXCEPT',
-  'EXCEPT ALL',
-  'EXCEPT DISTINCT',
-];
+const reservedBinaryCommands = expandPhrases([
+  'UNION [ALL | DISTINCT]',
+  'EXCEPT [ALL | DISTINCT]',
+  'INTERSECT [ALL | DISTINCT]',
+]);
 
 // https://github.com/trinodb/trino/blob/432d2897bdef99388c1a47188743a061c4ac1f34/core/trino-parser/src/main/antlr4/io/trino/sql/parser/SqlBase.g4#L299-L313
-const reservedJoins = [
+const reservedJoins = expandPhrases([
   'JOIN',
-  'INNER JOIN',
-  'LEFT JOIN',
-  'LEFT OUTER JOIN',
-  'RIGHT JOIN',
-  'RIGHT OUTER JOIN',
-  'FULL JOIN',
-  'FULL OUTER JOIN',
-  'CROSS JOIN',
+  '{LEFT | RIGHT | FULL} [OUTER] JOIN',
+  '{INNER | CROSS} JOIN',
   'NATURAL JOIN',
   'NATURAL INNER JOIN',
-  'NATURAL LEFT JOIN',
-  'NATURAL LEFT OUTER JOIN',
-  'NATURAL RIGHT JOIN',
-  'NATURAL RIGHT OUTER JOIN',
-  'NATURAL FULL JOIN',
-  'NATURAL FULL OUTER JOIN',
-];
+  'NATURAL {LEFT | RIGHT | FULL} [OUTER] JOIN',
+]);
 
 export default class TrinoFormatter extends Formatter {
   // https://trino.io/docs/current/functions/list.html#id1
diff --git a/src/languages/tsql/tsql.formatter.ts b/src/languages/tsql/tsql.formatter.ts
index f5fd690c..aac7378e 100644
--- a/src/languages/tsql/tsql.formatter.ts
+++ b/src/languages/tsql/tsql.formatter.ts
@@ -1,3 +1,4 @@
+import { expandPhrases } from 'src/expandPhrases';
 import Formatter from 'src/formatter/Formatter';
 import Tokenizer from 'src/lexer/Tokenizer';
 import { functions } from './tsql.functions';
@@ -188,32 +189,15 @@ const reservedCommands = [
   'PARTITION BY',
 ];
 
-const reservedBinaryCommands = [
-  'INTERSECT',
-  'INTERSECT ALL',
-  'INTERSECT DISTINCT',
-  'UNION',
-  'UNION ALL',
-  'UNION DISTINCT',
-  'EXCEPT',
-  'EXCEPT ALL',
-  'EXCEPT DISTINCT',
-  'MINUS',
-  'MINUS ALL',
-  'MINUS DISTINCT',
-];
+const reservedBinaryCommands = expandPhrases(['UNION [ALL]', 'EXCEPT', 'INTERSECT']);
 
-const reservedJoins = [
+const reservedJoins = expandPhrases([
   'JOIN',
-  'INNER JOIN',
-  'LEFT JOIN',
-  'LEFT OUTER JOIN',
-  'RIGHT JOIN',
-  'RIGHT OUTER JOIN',
-  'FULL JOIN',
-  'FULL OUTER JOIN',
-  'CROSS JOIN',
-];
+  '{LEFT | RIGHT | FULL} [OUTER] JOIN',
+  '{INNER | CROSS} JOIN',
+  // non-standard joins
+  '{CROSS | OUTER} APPLY',
+]);
 
 // https://docs.microsoft.com/en-us/sql/t-sql/language-reference?view=sql-server-ver15
 export default class TSqlFormatter extends Formatter {


Test patch:
diff --git a/test/behavesLikeMariaDbFormatter.ts b/test/behavesLikeMariaDbFormatter.ts
index eb4e7fc8..5284ea5e 100644
--- a/test/behavesLikeMariaDbFormatter.ts
+++ b/test/behavesLikeMariaDbFormatter.ts
@@ -6,7 +6,6 @@ import behavesLikeSqlFormatter from './behavesLikeSqlFormatter';
 import supportsCreateTable from './features/createTable';
 import supportsAlterTable from './features/alterTable';
 import supportsBetween from './features/between';
-import supportsJoin from './features/join';
 import supportsConstraints from './features/constraints';
 import supportsDeleteFrom from './features/deleteFrom';
 import supportsComments from './features/comments';
@@ -27,16 +26,6 @@ export default function behavesLikeMariaDbFormatter(format: FormatFn) {
   supportsAlterTable(format);
   supportsDeleteFrom(format);
   supportsBetween(format);
-  supportsJoin(format, {
-    without: ['FULL'],
-    additionally: [
-      'STRAIGHT_JOIN',
-      'NATURAL LEFT JOIN',
-      'NATURAL LEFT OUTER JOIN',
-      'NATURAL RIGHT JOIN',
-      'NATURAL RIGHT OUTER JOIN',
-    ],
-  });
   supportsParams(format, { positional: true });
 
   it('allows $ character as part of identifiers', () => {
diff --git a/test/bigquery.test.ts b/test/bigquery.test.ts
index 2fea8f31..ead2cf41 100644
--- a/test/bigquery.test.ts
+++ b/test/bigquery.test.ts
@@ -16,6 +16,7 @@ import supportsComments from './features/comments';
 import supportsIdentifiers from './features/identifiers';
 import supportsParams from './options/param';
 import supportsWindow from './features/window';
+import supportsSetOperations from './features/setOperations';
 
 describe('BigQueryFormatter', () => {
   const language = 'bigquery';
@@ -29,7 +30,13 @@ describe('BigQueryFormatter', () => {
   supportsIdentifiers(format, ['``']);
   supportsArrayLiterals(format);
   supportsBetween(format);
-  supportsJoin(format, { without: ['NATURAL JOIN'] });
+  supportsJoin(format, { without: ['NATURAL'] });
+  supportsSetOperations(format, [
+    'UNION ALL',
+    'UNION DISTINCT',
+    'EXCEPT DISTINCT',
+    'INTERSECT DISTINCT',
+  ]);
   supportsOperators(format, BigQueryFormatter.operators);
   supportsParams(format, { positional: true, named: ['@'], quoted: ['@``'] });
   supportsWindow(format);
diff --git a/test/db2.test.ts b/test/db2.test.ts
index 7cefec61..974dcd05 100644
--- a/test/db2.test.ts
+++ b/test/db2.test.ts
@@ -16,6 +16,7 @@ import supportsDeleteFrom from './features/deleteFrom';
 import supportsComments from './features/comments';
 import supportsIdentifiers from './features/identifiers';
 import supportsParams from './options/param';
+import supportsSetOperations from './features/setOperations';
 
 describe('Db2Formatter', () => {
   const language = 'db2';
@@ -32,7 +33,15 @@ describe('Db2Formatter', () => {
   supportsBetween(format);
   supportsSchema(format);
   supportsOperators(format, Db2Formatter.operators);
-  supportsJoin(format, { supportsUsing: false });
+  supportsJoin(format, { without: ['NATURAL'], supportsUsing: false });
+  supportsSetOperations(format, [
+    'UNION',
+    'UNION ALL',
+    'EXCEPT',
+    'EXCEPT ALL',
+    'INTERSECT',
+    'INTERSECT ALL',
+  ]);
   supportsParams(format, { positional: true, named: [':'] });
 
   it('formats FETCH FIRST like LIMIT', () => {
diff --git a/test/features/join.ts b/test/features/join.ts
index 91260a36..21205b91 100644
--- a/test/features/join.ts
+++ b/test/features/join.ts
@@ -6,43 +6,34 @@ interface Options {
   without?: string[];
   additionally?: string[];
   supportsUsing?: boolean;
+  supportsApply?: boolean;
 }
 
 export default function supportsJoin(
   format: FormatFn,
-  { without, additionally, supportsUsing = true }: Options = {}
+  { without, additionally, supportsUsing = true, supportsApply }: Options = {}
 ) {
   const unsupportedJoinRegex = without ? new RegExp(without.join('|'), 'u') : /^whateve_!%&$/u;
   const isSupportedJoin = (join: string) => !unsupportedJoinRegex.test(join);
 
-  ['CROSS JOIN', 'NATURAL JOIN'].filter(isSupportedJoin).forEach(join => {
-    it(`supports ${join}`, () => {
-      const result = format(`SELECT * FROM tbl1 ${join} tbl2`);
-      expect(result).toBe(dedent`
-        SELECT
-          *
-        FROM
-          tbl1
-          ${join} tbl2
-      `);
-    });
-  });
-
-  // <join> ::= [ <join type> ] JOIN
-  //
-  // <join type> ::= INNER | <outer join type> [ OUTER ]
-  //
-  // <outer join type> ::= LEFT | RIGHT | FULL
-
   [
     'JOIN',
     'INNER JOIN',
+    'CROSS JOIN',
     'LEFT JOIN',
     'LEFT OUTER JOIN',
     'RIGHT JOIN',
     'RIGHT OUTER JOIN',
     'FULL JOIN',
     'FULL OUTER JOIN',
+    'NATURAL JOIN',
+    'NATURAL INNER JOIN',
+    'NATURAL LEFT JOIN',
+    'NATURAL LEFT OUTER JOIN',
+    'NATURAL RIGHT JOIN',
+    'NATURAL RIGHT OUTER JOIN',
+    'NATURAL FULL JOIN',
+    'NATURAL FULL OUTER JOIN',
     ...(additionally || []),
   ]
     .filter(isSupportedJoin)
@@ -91,4 +82,20 @@ export default function supportsJoin(
       `);
     });
   }
+
+  if (supportsApply) {
+    ['CROSS APPLY', 'OUTER APPLY'].forEach(apply => {
+      // TODO: improve formatting of custom functions
+      it(`supports ${apply}`, () => {
+        const result = format(`SELECT * FROM customers ${apply} fn(customers.id)`);
+        expect(result).toBe(dedent`
+          SELECT
+            *
+          FROM
+            customers
+            ${apply} fn (customers.id)
+        `);
+      });
+    });
+  }
 }
diff --git a/test/features/setOperations.ts b/test/features/setOperations.ts
new file mode 100644
index 00000000..70abfb1c
--- /dev/null
+++ b/test/features/setOperations.ts
@@ -0,0 +1,36 @@
+import dedent from 'dedent-js';
+
+import { FormatFn } from 'src/sqlFormatter';
+
+export const standardSetOperations = [
+  'UNION',
+  'UNION ALL',
+  'UNION DISTINCT',
+  'EXCEPT',
+  'EXCEPT ALL',
+  'EXCEPT DISTINCT',
+  'INTERSECT',
+  'INTERSECT ALL',
+  'INTERSECT DISTINCT',
+];
+
+export default function supportsSetOperations(
+  format: FormatFn,
+  operations: string[] = standardSetOperations
+) {
+  operations.forEach(op => {
+    it(`formats ${op}`, () => {
+      expect(format(`SELECT * FROM foo ${op} SELECT * FROM bar;`)).toBe(dedent`
+        SELECT
+          *
+        FROM
+          foo
+        ${op}
+        SELECT
+          *
+        FROM
+          bar;
+      `);
+    });
+  });
+}
diff --git a/test/hive.test.ts b/test/hive.test.ts
index 216e5428..47a9b6e8 100644
--- a/test/hive.test.ts
+++ b/test/hive.test.ts
@@ -16,6 +16,7 @@ import supportsArrayAndMapAccessors from './features/arrayAndMapAccessors';
 import supportsComments from './features/comments';
 import supportsIdentifiers from './features/identifiers';
 import supportsWindow from './features/window';
+import supportsSetOperations from './features/setOperations';
 
 describe('HiveFormatter', () => {
   const language = 'hive';
@@ -29,7 +30,12 @@ describe('HiveFormatter', () => {
   supportsIdentifiers(format, ['``']);
   supportsBetween(format);
   supportsSchema(format);
-  supportsJoin(format, { without: ['NATURAL JOIN'], supportsUsing: false });
+  supportsJoin(format, {
+    without: ['NATURAL'],
+    additionally: ['LEFT SEMI JOIN'],
+    supportsUsing: false,
+  });
+  supportsSetOperations(format, ['UNION', 'UNION ALL', 'UNION DISTINCT']);
   supportsOperators(format, HiveFormatter.operators);
   supportsArrayAndMapAccessors(format);
   supportsWindow(format);
diff --git a/test/mariadb.test.ts b/test/mariadb.test.ts
index 5523ac4e..53eb2b17 100644
--- a/test/mariadb.test.ts
+++ b/test/mariadb.test.ts
@@ -2,8 +2,10 @@ import { format as originalFormat, FormatFn } from 'src/sqlFormatter';
 import MariaDbFormatter from 'src/languages/mariadb/mariadb.formatter';
 import behavesLikeMariaDbFormatter from './behavesLikeMariaDbFormatter';
 
+import supportsJoin from './features/join';
 import supportsOperators from './features/operators';
 import supportsReturning from './features/returning';
+import supportsSetOperations, { standardSetOperations } from './features/setOperations';
 
 describe('MariaDbFormatter', () => {
   const language = 'mariadb';
@@ -11,6 +13,11 @@ describe('MariaDbFormatter', () => {
 
   behavesLikeMariaDbFormatter(format);
 
+  supportsJoin(format, {
+    without: ['FULL', 'NATURAL INNER JOIN'],
+    additionally: ['STRAIGHT_JOIN'],
+  });
+  supportsSetOperations(format, [...standardSetOperations, 'MINUS', 'MINUS ALL', 'MINUS DISTINCT']);
   supportsOperators(format, MariaDbFormatter.operators, ['AND', 'OR', 'XOR']);
   supportsReturning(format);
 });
diff --git a/test/mysql.test.ts b/test/mysql.test.ts
index dbc1dff2..75392b55 100644
--- a/test/mysql.test.ts
+++ b/test/mysql.test.ts
@@ -4,8 +4,10 @@ import { format as originalFormat, FormatFn } from 'src/sqlFormatter';
 import MySqlFormatter from 'src/languages/mysql/mysql.formatter';
 import behavesLikeMariaDbFormatter from './behavesLikeMariaDbFormatter';
 
+import supportsJoin from './features/join';
 import supportsOperators from './features/operators';
 import supportsWindow from './features/window';
+import supportsSetOperations from './features/setOperations';
 
 describe('MySqlFormatter', () => {
   const language = 'mysql';
@@ -13,6 +15,11 @@ describe('MySqlFormatter', () => {
 
   behavesLikeMariaDbFormatter(format);
 
+  supportsJoin(format, {
+    without: ['FULL'],
+    additionally: ['STRAIGHT_JOIN'],
+  });
+  supportsSetOperations(format, ['UNION', 'UNION ALL', 'UNION DISTINCT']);
   supportsOperators(format, MySqlFormatter.operators, ['AND', 'OR', 'XOR']);
   supportsWindow(format);
 
diff --git a/test/n1ql.test.ts b/test/n1ql.test.ts
index 404e01fc..f7cf143a 100644
--- a/test/n1ql.test.ts
+++ b/test/n1ql.test.ts
@@ -17,6 +17,7 @@ import supportsComments from './features/comments';
 import supportsIdentifiers from './features/identifiers';
 import supportsParams from './options/param';
 import supportsWindow from './features/window';
+import supportsSetOperations from './features/setOperations';
 
 describe('N1qlFormatter', () => {
   const language = 'n1ql';
@@ -33,6 +34,14 @@ describe('N1qlFormatter', () => {
   supportsArrayAndMapAccessors(format);
   supportsArrayLiterals(format);
   supportsJoin(format, { without: ['FULL', 'CROSS', 'NATURAL'], supportsUsing: false });
+  supportsSetOperations(format, [
+    'UNION',
+    'UNION ALL',
+    'EXCEPT',
+    'EXCEPT ALL',
+    'INTERSECT',
+    'INTERSECT ALL',
+  ]);
   supportsReturning(format);
   supportsParams(format, { positional: true, numbered: ['$'], named: ['$'] });
   supportsWindow(format);
diff --git a/test/options/indentStyle.ts b/test/options/indentStyle.ts
index 4eaba09f..b3fae3cb 100644
--- a/test/options/indentStyle.ts
+++ b/test/options/indentStyle.ts
@@ -60,7 +60,7 @@ export default function supportsIndentStyle(format: FormatFn) {
           dedent`
             SELECT *
             FROM a
-            UNION DISTINCT
+            UNION ALL
             SELECT *
             FROM b
             LEFT OUTER JOIN c;
@@ -70,7 +70,7 @@ export default function supportsIndentStyle(format: FormatFn) {
       ).toBe(dedent`
         SELECT    *
         FROM      a
-        UNION     DISTINCT
+        UNION ALL
         SELECT    *
         FROM      b
         LEFT      OUTER JOIN c;
@@ -147,7 +147,7 @@ export default function supportsIndentStyle(format: FormatFn) {
           dedent`
             SELECT *
             FROM a
-            UNION DISTINCT
+            UNION ALL
             SELECT *
             FROM b
             LEFT OUTER JOIN c;
@@ -158,7 +158,7 @@ export default function supportsIndentStyle(format: FormatFn) {
         [
           '   SELECT *',
           '     FROM a',
-          '    UNION DISTINCT',
+          'UNION ALL',
           '   SELECT *',
           '     FROM b',
           '     LEFT OUTER JOIN c;',
diff --git a/test/plsql.test.ts b/test/plsql.test.ts
index b85c7225..85b8cb34 100644
--- a/test/plsql.test.ts
+++ b/test/plsql.test.ts
@@ -18,6 +18,7 @@ import supportsDeleteFrom from './features/deleteFrom';
 import supportsComments from './features/comments';
 import supportsIdentifiers from './features/identifiers';
 import supportsParams from './options/param';
+import supportsSetOperations from './features/setOperations';
 
 describe('PlSqlFormatter', () => {
   const language = 'plsql';
@@ -35,7 +36,8 @@ describe('PlSqlFormatter', () => {
   supportsBetween(format);
   supportsSchema(format);
   supportsOperators(format, PlSqlFormatter.operators, ['AND', 'OR', 'XOR']);
-  supportsJoin(format);
+  supportsJoin(format, { supportsApply: true });
+  supportsSetOperations(format, ['UNION', 'UNION ALL', 'EXCEPT', 'INTERSECT']);
   supportsReturning(format);
   supportsParams(format, { numbered: [':'], named: [':'] });
 
@@ -114,20 +116,6 @@ describe('PlSqlFormatter', () => {
     `);
   });
 
-  // TODO: improve formatting of custom functions
-  it('formats SELECT query with CROSS APPLY', () => {
-    const result = format('SELECT a, b FROM t CROSS APPLY fn(t.id)');
-    expect(result).toBe(dedent`
-      SELECT
-        a,
-        b
-      FROM
-        t
-      CROSS APPLY
-      fn (t.id)
-    `);
-  });
-
   it('formats simple SELECT with national characters', () => {
     const result = format("SELECT N'value'");
     expect(result).toBe(dedent`
@@ -136,19 +124,6 @@ describe('PlSqlFormatter', () => {
     `);
   });
 
-  it('formats SELECT query with OUTER APPLY', () => {
-    const result = format('SELECT a, b FROM t OUTER APPLY fn(t.id)');
-    expect(result).toBe(dedent`
-      SELECT
-        a,
-        b
-      FROM
-        t
-      OUTER APPLY
-      fn (t.id)
-    `);
-  });
-
   it('formats Oracle recursive sub queries', () => {
     const result = format(`
       WITH t1(id, parent_id) AS (
@@ -160,7 +135,7 @@ describe('PlSqlFormatter', () => {
           tab1
         WHERE
           parent_id IS NULL
-        MINUS
+        UNION
           -- Recursive member.
         SELECT
           t2.id,
@@ -185,7 +160,7 @@ describe('PlSqlFormatter', () => {
             tab1
           WHERE
             parent_id IS NULL
-          MINUS
+          UNION
           -- Recursive member.
           SELECT
             t2.id,
@@ -223,7 +198,7 @@ describe('PlSqlFormatter', () => {
           tab1
         WHERE
           parent_id IS NULL
-        MINUS
+        UNION
           -- Recursive member.
         SELECT
           t2.id,
@@ -248,7 +223,7 @@ describe('PlSqlFormatter', () => {
             tab1
           WHERE
             parent_id IS NULL
-          MINUS
+          UNION
           -- Recursive member.
           SELECT
             t2.id,
diff --git a/test/postgresql.test.ts b/test/postgresql.test.ts
index a4afae77..28207d6f 100644
--- a/test/postgresql.test.ts
+++ b/test/postgresql.test.ts
@@ -19,6 +19,7 @@ import supportsIdentifiers from './features/identifiers';
 import supportsParams from './options/param';
 import supportsArrayAndMapAccessors from './features/arrayAndMapAccessors';
 import supportsWindow from './features/window';
+import supportsSetOperations from './features/setOperations';
 
 describe('PostgreSqlFormatter', () => {
   const language = 'postgresql';
@@ -40,6 +41,7 @@ describe('PostgreSqlFormatter', () => {
     PostgreSqlFormatter.operators.filter(op => op !== '::')
   );
   supportsJoin(format);
+  supportsSetOperations(format);
   supportsReturning(format);
   supportsParams(format, { numbered: ['$'] });
   supportsWindow(format);
diff --git a/test/redshift.test.ts b/test/redshift.test.ts
index a25d164d..21008ee0 100644
--- a/test/redshift.test.ts
+++ b/test/redshift.test.ts
@@ -15,6 +15,7 @@ import supportsDeleteFrom from './features/deleteFrom';
 import supportsComments from './features/comments';
 import supportsIdentifiers from './features/identifiers';
 import supportsParams from './options/param';
+import supportsSetOperations from './features/setOperations';
 
 describe('RedshiftFormatter', () => {
   const language = 'redshift';
@@ -31,6 +32,7 @@ describe('RedshiftFormatter', () => {
   supportsSchema(format);
   supportsOperators(format, RedshiftFormatter.operators);
   supportsJoin(format);
+  supportsSetOperations(format, ['UNION', 'UNION ALL', 'EXCEPT', 'INTERSECT', 'MINUS']);
   supportsParams(format, { numbered: ['$'] });
 
   it('formats LIMIT', () => {
diff --git a/test/spark.test.ts b/test/spark.test.ts
index d7cb0c56..39c4ae04 100644
--- a/test/spark.test.ts
+++ b/test/spark.test.ts
@@ -14,6 +14,7 @@ import supportsStrings from './features/strings';
 import supportsArrayAndMapAccessors from './features/arrayAndMapAccessors';
 import supportsComments from './features/comments';
 import supportsIdentifiers from './features/identifiers';
+import supportsSetOperations from './features/setOperations';
 
 describe('SparkFormatter', () => {
   const language = 'spark';
@@ -31,24 +32,19 @@ describe('SparkFormatter', () => {
   supportsArrayAndMapAccessors(format);
   supportsJoin(format, {
     additionally: [
+      // non-standard anti-join:
       'ANTI JOIN',
-      'SEMI JOIN',
       'LEFT ANTI JOIN',
-      'LEFT SEMI JOIN',
-      'RIGHT OUTER JOIN',
-      'RIGHT SEMI JOIN',
       'NATURAL ANTI JOIN',
-      'NATURAL FULL OUTER JOIN',
-      'NATURAL INNER JOIN',
       'NATURAL LEFT ANTI JOIN',
-      'NATURAL LEFT OUTER JOIN',
-      'NATURAL LEFT SEMI JOIN',
-      'NATURAL OUTER JOIN',
-      'NATURAL RIGHT OUTER JOIN',
-      'NATURAL RIGHT SEMI JOIN',
+      // non-standard semi-join
+      'SEMI JOIN',
+      'LEFT SEMI JOIN',
       'NATURAL SEMI JOIN',
+      'NATURAL LEFT SEMI JOIN',
     ],
   });
+  supportsSetOperations(format);
 
   it('formats basic WINDOW clause', () => {
     const result = format(`SELECT * FROM tbl WINDOW win1, WINDOW win2, WINDOW win3;`);
diff --git a/test/sql.test.ts b/test/sql.test.ts
index a27ed6af..c943695b 100644
--- a/test/sql.test.ts
+++ b/test/sql.test.ts
@@ -17,6 +17,7 @@ import supportsComments from './features/comments';
 import supportsIdentifiers from './features/identifiers';
 import supportsParams from './options/param';
 import supportsWindow from './features/window';
+import supportsSetOperations from './features/setOperations';
 
 describe('SqlFormatter', () => {
   const language = 'sql';
@@ -33,6 +34,7 @@ describe('SqlFormatter', () => {
   supportsBetween(format);
   supportsSchema(format);
   supportsJoin(format);
+  supportsSetOperations(format);
   supportsOperators(format, SqlFormatter.operators);
   supportsParams(format, { positional: true });
   supportsWindow(format);
diff --git a/test/sqlite.test.ts b/test/sqlite.test.ts
index 76726c0c..38df20ca 100644
--- a/test/sqlite.test.ts
+++ b/test/sqlite.test.ts
@@ -17,6 +17,7 @@ import supportsComments from './features/comments';
 import supportsIdentifiers from './features/identifiers';
 import supportsParams from './options/param';
 import supportsWindow from './features/window';
+import supportsSetOperations from './features/setOperations';
 
 describe('SqliteFormatter', () => {
   const language = 'sqlite';
@@ -32,10 +33,8 @@ describe('SqliteFormatter', () => {
   supportsIdentifiers(format, [`""`, '``', '[]']);
   supportsBetween(format);
   supportsSchema(format);
-  supportsJoin(format, {
-    without: ['FULL', 'RIGHT'],
-    additionally: ['NATURAL LEFT JOIN', 'NATURAL LEFT OUTER JOIN'],
-  });
+  supportsJoin(format);
+  supportsSetOperations(format, ['UNION', 'UNION ALL', 'EXCEPT', 'INTERSECT']);
   supportsOperators(format, SqliteFormatter.operators);
   supportsParams(format, { positional: true, numbered: ['?'], named: [':', '$', '@'] });
   supportsWindow(format);
diff --git a/test/trino.test.ts b/test/trino.test.ts
index 0bbbadbe..22a8833c 100644
--- a/test/trino.test.ts
+++ b/test/trino.test.ts
@@ -15,6 +15,7 @@ import supportsArrayAndMapAccessors from './features/arrayAndMapAccessors';
 import supportsComments from './features/comments';
 import supportsIdentifiers from './features/identifiers';
 import supportsParams from './options/param';
+import supportsSetOperations from './features/setOperations';
 
 describe('TrinoFormatter', () => {
   const language = 'trino';
@@ -30,17 +31,8 @@ describe('TrinoFormatter', () => {
   supportsOperators(format, TrinoFormatter.operators, ['AND', 'OR']);
   supportsArrayLiterals(format);
   supportsArrayAndMapAccessors(format);
-  supportsJoin(format, {
-    additionally: [
-      'NATURAL INNER JOIN',
-      'NATURAL LEFT JOIN',
-      'NATURAL LEFT OUTER JOIN',
-      'NATURAL RIGHT JOIN',
-      'NATURAL RIGHT OUTER JOIN',
-      'NATURAL FULL JOIN',
-      'NATURAL FULL OUTER JOIN',
-    ],
-  });
+  supportsJoin(format);
+  supportsSetOperations(format);
   supportsParams(format, { positional: true });
 
   it('formats SET SESSION', () => {
diff --git a/test/tsql.test.ts b/test/tsql.test.ts
index d896a728..b9697596 100644
--- a/test/tsql.test.ts
+++ b/test/tsql.test.ts
@@ -17,6 +17,7 @@ import supportsComments from './features/comments';
 import supportsIdentifiers from './features/identifiers';
 import supportsParams from './options/param';
 import supportsWindow from './features/window';
+import supportsSetOperations from './features/setOperations';
 
 describe('TSqlFormatter', () => {
   const language = 'tsql';
@@ -36,7 +37,8 @@ describe('TSqlFormatter', () => {
     format,
     TSqlFormatter.operators.filter(op => op !== '::')
   );
-  supportsJoin(format, { without: ['NATURAL'], supportsUsing: false });
+  supportsJoin(format, { without: ['NATURAL'], supportsUsing: false, supportsApply: true });
+  supportsSetOperations(format, ['UNION', 'UNION ALL', 'EXCEPT', 'INTERSECT']);
   supportsParams(format, { named: ['@'], quoted: ['@""', '@[]'] });
   supportsWindow(format);
 
@@ -54,18 +56,6 @@ describe('TSqlFormatter', () => {
     `);
   });
 
-  it('formats SELECT query with CROSS JOIN', () => {
-    const result = format('SELECT a, b FROM t CROSS JOIN t2 on t.id = t2.id_t');
-    expect(result).toBe(dedent`
-      SELECT
-        a,
-        b
-      FROM
-        t
-        CROSS JOIN t2 on t.id = t2.id_t
-    `);
-  });
-
   it('recognizes @, $, # as part of identifiers', () => {
     const result = format('SELECT from@bar, where#to, join$me FROM tbl;');
     expect(result).toBe(dedent`
diff --git a/test/unit/expandPhrases.test.ts b/test/unit/expandPhrases.test.ts
index f559fdfd..b505758e 100644
--- a/test/unit/expandPhrases.test.ts
+++ b/test/unit/expandPhrases.test.ts
@@ -32,7 +32,7 @@ describe('expandSinglePhrase()', () => {
     ]);
   });
 
-  it('expands expression with [multi|choice|block]', () => {
+  it('expands expression with optional [multi|choice|block]', () => {
     expect(expandSinglePhrase('CREATE [TEMP|TEMPORARY|VIRTUAL] TABLE')).toEqual([
       'CREATE TABLE',
       'CREATE TEMP TABLE',
@@ -41,6 +41,18 @@ describe('expandSinglePhrase()', () => {
     ]);
   });
 
+  it('removes braces around {mandatory} {block}', () => {
+    expect(expandSinglePhrase('CREATE {TEMP} {TABLE}')).toEqual(['CREATE TEMP TABLE']);
+  });
+
+  it('expands expression with mandatory {multi|choice|block}', () => {
+    expect(expandSinglePhrase('CREATE {TEMP|TEMPORARY|VIRTUAL} TABLE')).toEqual([
+      'CREATE TEMP TABLE',
+      'CREATE TEMPORARY TABLE',
+      'CREATE VIRTUAL TABLE',
+    ]);
+  });
+
   it('throws error when encountering unbalanced ][-braces', () => {
     expect(() => expandSinglePhrase('CREATE [TABLE')).toThrowErrorMatchingInlineSnapshot(
       `"Unbalanced parenthesis in: CREATE [TABLE"`
@@ -49,4 +61,13 @@ describe('expandSinglePhrase()', () => {
       `"Unbalanced parenthesis in: CREATE TABLE]"`
     );
   });
+
+  it('throws error when encountering unbalanced }{-braces', () => {
+    expect(() => expandSinglePhrase('CREATE {TABLE')).toThrowErrorMatchingInlineSnapshot(
+      `"Unbalanced parenthesis in: CREATE {TABLE"`
+    );
+    expect(() => expandSinglePhrase('CREATE TABLE}')).toThrowErrorMatchingInlineSnapshot(
+      `"Unbalanced parenthesis in: CREATE TABLE}"`
+    );
+  });
 });