YQL-17269: support INTERSECT and EXCEPT without PositionalUnionAll

commit_hash:632e24794e8bcf6ef0502b7e8c031e964d28d36a

39 files changed, 1106 insertions, 40 deletions

diff --git a/yql/essentials/core/common_opt/yql_co_pgselect.h b/yql/essentials/core/common_opt/yql_co_pgselect.h
index 84995d8b36d..3cb84992af4 100644
--- a/yql/essentials/core/common_opt/yql_co_pgselect.h
+++ b/yql/essentials/core/common_opt/yql_co_pgselect.h
@@ -25,4 +25,6 @@ TExprNode::TPtr ExpandPgGrouping(const TExprNode::TPtr& node, TExprContext& ctx,
 
 TExprNode::TPtr ExpandPgIterate(const TExprNode::TPtr& node, TExprContext& ctx, TOptimizeContext& optCtx);
 
+TExprNode::TPtr CombineSetItems(TPositionHandle pos, const TExprNode::TPtr& left, const TExprNode::TPtr& right, const TStringBuf& op, TExprContext& ctx);
+
 } // namespace NYql
diff --git a/yql/essentials/core/common_opt/yql_co_simple1.cpp b/yql/essentials/core/common_opt/yql_co_simple1.cpp
index 9032e2b058e..59cd2da58f5 100644
--- a/yql/essentials/core/common_opt/yql_co_simple1.cpp
+++ b/yql/essentials/core/common_opt/yql_co_simple1.cpp
@@ -5374,6 +5374,24 @@ void RegisterCoSimpleCallables1(TCallableOptimizerMap& map) {
         return ctx.NewCallable(node->Pos(), "SqlAggregateAll", { ctx.NewCallable(node->Pos(), "UnionAll", node->ChildrenList()) });
     };
 
+    map["IntersectAll"] = [](const TExprNode::TPtr& node, TExprContext& ctx, TOptimizeContext& /*optCtx*/) {
+        YQL_CLOG(DEBUG, Core) << node->Content();
+        return CombineSetItems(node->Pos(), node->Child(0), node->Child(1), "intersect_all", ctx);
+    };
+    map["Intersect"] = [](const TExprNode::TPtr& node, TExprContext& ctx, TOptimizeContext& /*optCtx*/) {
+        YQL_CLOG(DEBUG, Core) << node->Content();
+        return CombineSetItems(node->Pos(), node->Child(0), node->Child(1), "intersect", ctx);
+    };
+
+    map["ExceptAll"] = [](const TExprNode::TPtr& node, TExprContext& ctx, TOptimizeContext& /*optCtx*/) {
+        YQL_CLOG(DEBUG, Core) << node->Content();
+        return CombineSetItems(node->Pos(), node->Child(0), node->Child(1), "except_all", ctx);
+    };
+    map["Except"] = [](const TExprNode::TPtr& node, TExprContext& ctx, TOptimizeContext& /*optCtx*/) {
+        YQL_CLOG(DEBUG, Core) << node->Content();
+        return CombineSetItems(node->Pos(), node->Child(0), node->Child(1), "except", ctx);
+    };
+
     map["Aggregate"] = [](const TExprNode::TPtr& node, TExprContext& ctx, TOptimizeContext& optCtx) {
         TCoAggregate self(node);
         if (self.Keys().Size() == 0 && !HasPayload(self)) {
diff --git a/yql/essentials/core/type_ann/type_ann_core.cpp b/yql/essentials/core/type_ann/type_ann_core.cpp
index 1cf269402d9..d74eb4996d8 100644
--- a/yql/essentials/core/type_ann/type_ann_core.cpp
+++ b/yql/essentials/core/type_ann/type_ann_core.cpp
@@ -12663,9 +12663,13 @@ template <NKikimr::NUdf::EDataSlot DataSlot>
         Functions["Merge"] = &ExtendWrapper;
         Functions["Extract"] = &ExtractWrapper;
         Functions["OrderedExtract"] = &ExtractWrapper;
-        Functions["UnionAll"] = &UnionAllWrapper;
-        Functions["UnionMerge"] = &UnionAllWrapper;
-        Functions["Union"] = &UnionAllWrapper;
+        Functions["UnionAll"] = &SelectOpWrapper;
+        Functions["UnionMerge"] = &SelectOpWrapper;
+        Functions["Union"] = &SelectOpWrapper;
+        Functions["IntersectAll"] = &SelectOpWrapper;
+        Functions["Intersect"] = &SelectOpWrapper;
+        Functions["ExceptAll"] = &SelectOpWrapper;
+        Functions["Except"] = &SelectOpWrapper;
         ExtFunctions["ListExtend"] = &ListExtendWrapper<false>;
         ExtFunctions["ListExtendStrict"] = &ListExtendWrapper<true>;
         Functions["ListUnionAll"] = &ListUnionAllWrapper;
diff --git a/yql/essentials/core/type_ann/type_ann_list.cpp b/yql/essentials/core/type_ann/type_ann_list.cpp
index a215fdef950..66bcedfbea1 100644
--- a/yql/essentials/core/type_ann/type_ann_list.cpp
+++ b/yql/essentials/core/type_ann/type_ann_list.cpp
@@ -2761,8 +2761,8 @@ namespace {
         return IGraphTransformer::TStatus::Ok;
     }
 
-    IGraphTransformer::TStatus UnionAllWrapper(const TExprNode::TPtr& input, TExprNode::TPtr& output, TContext& ctx) {
-        const bool checkHashes = input->IsCallable("Union");
+    IGraphTransformer::TStatus SelectOpWrapper(const TExprNode::TPtr& input, TExprNode::TPtr& output, TContext& ctx) {
+        const bool checkHashes = !input->IsCallable("UnionAll") && !input->IsCallable("UnionMerge");
         switch (input->ChildrenSize()) {
             case 0U:
                 output = ctx.Expr.NewCallable(input->Pos(), "EmptyList", {});
@@ -2770,16 +2770,35 @@ namespace {
             case 1U:
                 output = input->HeadPtr();
                 return IGraphTransformer::TStatus::Repeat;
+            case 2U:
+                break;
             default:
+                if (!input->Content().StartsWith("Union")) {
+                    ctx.Expr.AddError(TIssue(ctx.Expr.GetPosition(input->Pos()), TStringBuilder()
+                            << "Only Union, UnionAll, and UnionMerge may use more than 2 inputs. Invalid Callable: " << input->Content()));
+                    return IGraphTransformer::TStatus::Error;
+                }
                 break;
         }
 
         const bool hasEmptyLists = AnyOf(input->Children(), IsEmptyList);
         if (hasEmptyLists) {
-            auto children = input->ChildrenList();
-            EraseIf(children, IsEmptyList);
-            output = ctx.Expr.ChangeChildren(*input, std::move(children));
-            return IGraphTransformer::TStatus::Repeat;
+            if (input->Content().StartsWith("Union")) {
+                auto children = input->ChildrenList();
+                EraseIf(children, IsEmptyList);
+                output = ctx.Expr.ChangeChildren(*input, std::move(children));
+                return IGraphTransformer::TStatus::Repeat;
+            } else {
+                if (input->Content().StartsWith("Intersect")) {
+                    output = ctx.Expr.NewCallable(input->Pos(), "EmptyList", {});
+                    return IGraphTransformer::TStatus::Repeat;
+                } else if (input->Content().StartsWith("Except")) {
+                    // the first input is empty -> output is empty
+                    // the second input is empty -> output is the first input
+                    output = input->HeadPtr();
+                    return IGraphTransformer::TStatus::Repeat;
+                }
+            }
         }
 
         std::unordered_map<std::string_view, std::pair<const TTypeAnnotationNode*, ui32>> members;
diff --git a/yql/essentials/core/type_ann/type_ann_list.h b/yql/essentials/core/type_ann/type_ann_list.h
index 3e0daa98c90..e52a1a221be 100644
--- a/yql/essentials/core/type_ann/type_ann_list.h
+++ b/yql/essentials/core/type_ann/type_ann_list.h
@@ -63,7 +63,7 @@ namespace NTypeAnnImpl {
     IGraphTransformer::TStatus ChopperWrapper(const TExprNode::TPtr& input, TExprNode::TPtr& output, TContext& ctx);
     IGraphTransformer::TStatus HasItemsWrapper(const TExprNode::TPtr& input, TExprNode::TPtr& output, TContext& ctx);
     IGraphTransformer::TStatus ExtendWrapper(const TExprNode::TPtr& input, TExprNode::TPtr& output, TContext& ctx);
-    IGraphTransformer::TStatus UnionAllWrapper(const TExprNode::TPtr& input, TExprNode::TPtr& output, TContext& ctx);
+    IGraphTransformer::TStatus SelectOpWrapper(const TExprNode::TPtr& input, TExprNode::TPtr& output, TContext& ctx);
     IGraphTransformer::TStatus UnionAllPositionalWrapper(const TExprNode::TPtr& input, TExprNode::TPtr& output, TExtContext& ctx);
     template <bool IsStrict>
     IGraphTransformer::TStatus ListExtendWrapper(const TExprNode::TPtr& input, TExprNode::TPtr& output, TExtContext& ctx);
diff --git a/yql/essentials/docs/en/changelog/2025.03.md b/yql/essentials/docs/en/changelog/2025.03.md
index 58921c15055..dca83c947e2 100644
--- a/yql/essentials/docs/en/changelog/2025.03.md
+++ b/yql/essentials/docs/en/changelog/2025.03.md
@@ -5,3 +5,9 @@
 ## Changes in Digest module {#digest-module}
 
 * Added `Digest::Sha512` function
+
+## Changes in SELECT syntax
+
+* Added the ability to explicitly specify `DISTINCT` for `UNION`;
+* Implemented `INTERSECT [DISTINCT|ALL]`;
+* Implemented `EXCEPT [DISTINCT|ALL]`.
diff --git a/yql/essentials/docs/ru/changelog/2025.03.md b/yql/essentials/docs/ru/changelog/2025.03.md
index 6db8d82f522..4daa9bb2f4a 100644
--- a/yql/essentials/docs/ru/changelog/2025.03.md
+++ b/yql/essentials/docs/ru/changelog/2025.03.md
@@ -5,3 +5,9 @@
 ## Изменения в модуле Digest {#Digest-module}
 
 * Добавлена функция `Digest::Sha512`
+
+## Изменения в синтаксисе SELECT
+
+* Добавлена возможность явного указания `DISTINCT` для `UNION`;
+* Добавлена реализация `INTERSECT [DISTINCT|ALL]`;
+* Добавлена реализация `EXCEPT [DISTINCT|ALL]`.
diff --git a/yql/essentials/sql/v1/format/sql_format_ut.h b/yql/essentials/sql/v1/format/sql_format_ut.h
index b61cc593936..35e95634e63 100644
--- a/yql/essentials/sql/v1/format/sql_format_ut.h
+++ b/yql/essentials/sql/v1/format/sql_format_ut.h
@@ -1548,6 +1548,34 @@ Y_UNIT_TEST(Union) {
     setup.Run(cases);
 }
 
+Y_UNIT_TEST(Intersect) {
+    TCases cases = {
+        {"select 1 intersect all select 2 intersect select 3 intersect all select 4 intersect select 5",
+            "SELECT\n\t1\nINTERSECT ALL\nSELECT\n\t2\nINTERSECT\nSELECT\n\t3\nINTERSECT ALL\nSELECT\n\t4\nINTERSECT\nSELECT\n\t5\n;\n"},
+        {"select 1 intersect all (select 2)",
+            "SELECT\n\t1\nINTERSECT ALL\n(\n\tSELECT\n\t\t2\n);\n"},
+        {"select 1 intersect distinct select 2 intersect select 3 intersect distinct select 4 intersect select 5",
+            "SELECT\n\t1\nINTERSECT DISTINCT\nSELECT\n\t2\nINTERSECT\nSELECT\n\t3\nINTERSECT DISTINCT\nSELECT\n\t4\nINTERSECT\nSELECT\n\t5\n;\n"},
+    };
+
+    TSetup setup;
+    setup.Run(cases);
+}
+
+Y_UNIT_TEST(Except) {
+    TCases cases = {
+        {"select 1 except all select 2 except select 3 except all select 4 except select 5",
+            "SELECT\n\t1\nEXCEPT ALL\nSELECT\n\t2\nEXCEPT\nSELECT\n\t3\nEXCEPT ALL\nSELECT\n\t4\nEXCEPT\nSELECT\n\t5\n;\n"},
+        {"select 1 except all (select 2)",
+            "SELECT\n\t1\nEXCEPT ALL\n(\n\tSELECT\n\t\t2\n);\n"},
+        {"select 1 except distinct select 2 except select 3 except distinct select 4 except select 5",
+            "SELECT\n\t1\nEXCEPT DISTINCT\nSELECT\n\t2\nEXCEPT\nSELECT\n\t3\nEXCEPT DISTINCT\nSELECT\n\t4\nEXCEPT\nSELECT\n\t5\n;\n"},
+    };
+
+    TSetup setup;
+    setup.Run(cases);
+}
+
 Y_UNIT_TEST(Comment) {
     TCases cases = {
         {"/*\nmulti\nline\ncomment\n*/\npragma foo = \"true\";\npragma bar = \"1\"",
diff --git a/yql/essentials/sql/v1/select.cpp b/yql/essentials/sql/v1/select.cpp
index 5a17bba297c..a2207d9ef76 100644
--- a/yql/essentials/sql/v1/select.cpp
+++ b/yql/essentials/sql/v1/select.cpp
@@ -2807,11 +2807,12 @@ TSourcePtr BuildSelectCore(
         having, std::move(winSpecs), legacyHoppingWindowSpec, std::move(terms), distinct, std::move(without), forceWithout, selectStream, settings, std::move(uniqueSets), std::move(distinctSets));
 }
 
-class TUnion: public IRealSource {
+class TSelectOp: public IRealSource {
 public:
-    TUnion(TPosition pos, TVector<TSourcePtr>&& sources, bool quantifierAll, const TWriteSettings& settings)
+    TSelectOp(TPosition pos, TVector<TSourcePtr>&& sources, const TString& op, bool quantifierAll, const TWriteSettings& settings)
         : IRealSource(pos)
         , Sources_(std::move(sources))
+        , Operator_(op)
         , QuantifierAll_(quantifierAll)
         , Settings_(settings)
     {
@@ -2848,14 +2849,30 @@ public:
 
     TNodePtr Build(TContext& ctx) override {
         TPtr res;
-        if (QuantifierAll_) {
-            if (ctx.EmitUnionMerge) {
-                res = ctx.PositionalUnionAll ? Y("UnionMergePositional") : Y("UnionMerge");
+        if (Operator_ == "union") {
+            if (QuantifierAll_) {
+                if (ctx.EmitUnionMerge) {
+                    res = ctx.PositionalUnionAll ? Y("UnionMergePositional") : Y("UnionMerge");
+                } else {
+                    res = ctx.PositionalUnionAll ? Y("UnionAllPositional") : Y("UnionAll");
+                }
+            } else {
+                res = ctx.PositionalUnionAll ? Y("UnionPositional") : Y("Union");
+            }
+        } else if (Operator_ == "intersect") {
+            if (QuantifierAll_) {
+                res = Y("IntersectAll");
+            } else {
+                res = Y("Intersect");
+            }
+        } else if (Operator_ == "except") {
+            if (QuantifierAll_) {
+                res = Y("ExceptAll");
             } else {
-                res = ctx.PositionalUnionAll ? Y("UnionAllPositional") : Y("UnionAll");
+                res = Y("Except");
             }
         } else {
-            res = ctx.PositionalUnionAll ? Y("UnionPositional") : Y("Union");
+            Y_ABORT("Invalid operator: %s", Operator_.c_str());
         }
 
         for (auto& s: Sources_) {
@@ -2879,7 +2896,7 @@ public:
     }
 
     TNodePtr DoClone() const final {
-        return MakeIntrusive<TUnion>(Pos_, CloneContainer(Sources_), QuantifierAll_, Settings_);
+        return MakeIntrusive<TSelectOp>(Pos_, CloneContainer(Sources_), Operator_, QuantifierAll_, Settings_);
     }
 
     bool IsSelect() const override {
@@ -2896,17 +2913,19 @@ public:
 
 private:
     TVector<TSourcePtr> Sources_;
+    const TString Operator_;
     bool QuantifierAll_;
     const TWriteSettings Settings_;
 };
 
-TSourcePtr BuildUnion(
+TSourcePtr BuildSelectOp(
     TPosition pos,
     TVector<TSourcePtr>&& sources,
+    const TString& op,
     bool quantifierAll,
     const TWriteSettings& settings
 ) {
-    return new TUnion(pos, std::move(sources), quantifierAll, settings);
+    return new TSelectOp(pos, std::move(sources), op, quantifierAll, settings);
 }
 
 class TOverWindowSource: public IProxySource {
diff --git a/yql/essentials/sql/v1/source.h b/yql/essentials/sql/v1/source.h
index ac69ed2eaf3..2c42dda0877 100644
--- a/yql/essentials/sql/v1/source.h
+++ b/yql/essentials/sql/v1/source.h
@@ -246,7 +246,7 @@ namespace NSQLTranslationV1 {
     TSourcePtr BuildTableSource(TPosition pos, const TTableRef& table, const TString& label = TString());
     TSourcePtr BuildInnerSource(TPosition pos, TNodePtr node, const TString& service, const TDeferredAtom& cluster, const TString& label = TString());
     TSourcePtr BuildRefColumnSource(TPosition pos, const TString& partExpression);
-    TSourcePtr BuildUnion(TPosition pos, TVector<TSourcePtr>&& sources, bool quantifierAll, const TWriteSettings& settings);
+    TSourcePtr BuildSelectOp(TPosition pos, TVector<TSourcePtr>&& sources, const TString& op, bool quantifierAll, const TWriteSettings& settings);
     TSourcePtr BuildOverWindowSource(TPosition pos, const TString& windowName, ISource* origSource);
 
     TNodePtr BuildOrderBy(TPosition pos, const TVector<TNodePtr>& keys, const TVector<bool>& order);
diff --git a/yql/essentials/sql/v1/sql_select.cpp b/yql/essentials/sql/v1/sql_select.cpp
index 04a8b1e38af..b9be35f76f1 100644
--- a/yql/essentials/sql/v1/sql_select.cpp
+++ b/yql/essentials/sql/v1/sql_select.cpp
@@ -1368,6 +1368,7 @@ TSourcePtr TSqlSelect::Build(const TRule& node, TPosition pos, TSelectKindResult
 
     TVector<TSourcePtr> sources{ std::move(first.Source)};
     bool currentQuantifier = false;
+    TString currentSelectOperator;
 
     for (int i = 0; i < blocks.size(); ++i) {
         auto& b = blocks[i];
@@ -1389,14 +1390,16 @@ TSourcePtr TSqlSelect::Build(const TRule& node, TPosition pos, TSelectKindResult
         }
 
         auto selectOp = b.GetRule_select_op1();
-        const TString token = ToLowerUTF8(Token(selectOp.GetToken1()));
-        if (token == "union") {
+        const TString selectOperator = ToLowerUTF8(Token(selectOp.GetToken1()));
+        if (selectOperator == "union") {
             // nothing
-        } else if (token == "intersect" || token == "except") {
-            Ctx_.Error() << "INTERSECT and EXCEPT are not implemented yet";
-            return nullptr;
+        } else if (selectOperator == "intersect" || selectOperator == "except") {
+            if (!IsBackwardCompatibleFeatureAvailable(Ctx_.Settings.LangVer, MakeLangVersion(2025, 3), Ctx_.Settings.BackportMode)) {
+                Ctx_.Error() << "INTERSECT and EXCEPT are available starting from 2025.03 version";
+                return nullptr;
+            }
         } else {
-            Y_ABORT("You should change implementation according to grammar changes. Invalid token: %s", token.c_str());
+            Y_ABORT("You should change implementation according to grammar changes. Invalid token: %s", selectOperator.c_str());
         }
 
         bool quantifier = false;
@@ -1411,17 +1414,19 @@ TSourcePtr TSqlSelect::Build(const TRule& node, TPosition pos, TSelectKindResult
             }
         }
 
-        if (!second && quantifier != currentQuantifier) {
-            auto source = BuildUnion(pos, std::move(sources), currentQuantifier, {});
+        // currentSelectOperator == "" <=> second == true
+        if (!second && (currentSelectOperator != selectOperator || quantifier != currentQuantifier)) {
+            auto source = BuildSelectOp(pos, std::move(sources), currentSelectOperator, currentQuantifier, {});
             sources.clear();
             sources.emplace_back(std::move(source));
         }
 
         sources.emplace_back(std::move(next.Source));
         currentQuantifier = quantifier;
+        currentSelectOperator = selectOperator;
     }
 
-    auto result = BuildUnion(pos, std::move(sources), currentQuantifier, outermostSettings);
+    auto result = BuildSelectOp(pos, std::move(sources), currentSelectOperator, currentQuantifier, outermostSettings);
 
     if (orderBy) {
         TVector<TNodePtr> groupByExpr;
diff --git a/yql/essentials/sql/v1/sql_ut_common.h b/yql/essentials/sql/v1/sql_ut_common.h
index 8b33aa8be0d..223aaa52b3b 100644
--- a/yql/essentials/sql/v1/sql_ut_common.h
+++ b/yql/essentials/sql/v1/sql_ut_common.h
@@ -1724,6 +1724,8 @@ Y_UNIT_TEST_SUITE(SqlParsingOnly) {
         UNIT_ASSERT_NO_DIFF(Err2Str(res), "<main>:1:6: Error: BATCH UPDATE is unsupported with ON\n");
     }
 
+    // UNION
+
     Y_UNIT_TEST(UnionAllTest) {
         NYql::TAstParseResult res = SqlToYql("PRAGMA DisableEmitUnionMerge; SELECT key FROM plato.Input UNION ALL select subkey FROM plato.Input;");
         UNIT_ASSERT(res.Root);
@@ -1798,6 +1800,77 @@ Y_UNIT_TEST_SUITE(SqlParsingOnly) {
         UNIT_ASSERT_VALUES_EQUAL(3, elementStat["Union"]);
     }
 
+    // INTERSECT
+
+    Y_UNIT_TEST(IntersectAllTest) {
+        NSQLTranslation::TTranslationSettings settings;
+        settings.LangVer = 202503;
+        NYql::TAstParseResult res = SqlToYqlWithSettings("SELECT key FROM plato.Input INTERSECT ALL select subkey FROM plato.Input;", settings);
+        UNIT_ASSERT(res.Root);
+
+        TWordCountHive elementStat = {{TString("IntersectAll"), 0}};
+        VerifyProgram(res, elementStat, {});
+        UNIT_ASSERT_VALUES_EQUAL(1, elementStat["IntersectAll"]);
+    }
+
+    Y_UNIT_TEST(IntersectTest) {
+        NSQLTranslation::TTranslationSettings settings;
+        settings.LangVer = 202503;
+        NYql::TAstParseResult res = SqlToYqlWithSettings("SELECT key FROM plato.Input INTERSECT select subkey FROM plato.Input;", settings);
+        UNIT_ASSERT(res.Root);
+
+        TWordCountHive elementStat = {{TString("Intersect"), 0}};
+        VerifyProgram(res, elementStat, {});
+        UNIT_ASSERT_VALUES_EQUAL(1, elementStat["Intersect"]);
+    }
+
+    Y_UNIT_TEST(IntersectDistinctTest) {
+        NSQLTranslation::TTranslationSettings settings;
+        settings.LangVer = 202503;
+        NYql::TAstParseResult res = SqlToYqlWithSettings("SELECT key FROM plato.Input INTERSECT DISTINCT select subkey FROM plato.Input;", settings);
+        UNIT_ASSERT(res.Root);
+
+        TWordCountHive elementStat = {{TString("Intersect"), 0}};
+        VerifyProgram(res, elementStat, {});
+        UNIT_ASSERT_VALUES_EQUAL(1, elementStat["Intersect"]);
+    }
+
+    // EXCEPT
+
+    Y_UNIT_TEST(ExceptAllTest) {
+        NSQLTranslation::TTranslationSettings settings;
+        settings.LangVer = 202503;
+        NYql::TAstParseResult res = SqlToYqlWithSettings("SELECT key FROM plato.Input EXCEPT ALL select subkey FROM plato.Input;", settings);
+        UNIT_ASSERT(res.Root);
+
+        TWordCountHive elementStat = {{TString("ExceptAll"), 0}};
+        VerifyProgram(res, elementStat, {});
+        UNIT_ASSERT_VALUES_EQUAL(1, elementStat["ExceptAll"]);
+    }
+
+    Y_UNIT_TEST(ExceptTest) {
+        NSQLTranslation::TTranslationSettings settings;
+        settings.LangVer = 202503;
+        NYql::TAstParseResult res = SqlToYqlWithSettings("SELECT key FROM plato.Input EXCEPT select subkey FROM plato.Input;", settings);
+        UNIT_ASSERT(res.Root);
+
+        TWordCountHive elementStat = {{TString("Except"), 0}};
+        VerifyProgram(res, elementStat, {});
+        UNIT_ASSERT_VALUES_EQUAL(1, elementStat["Except"]);
+    }
+
+    Y_UNIT_TEST(ExceptDistinctTest) {
+        NSQLTranslation::TTranslationSettings settings;
+        settings.LangVer = 202503;
+        NYql::TAstParseResult res = SqlToYqlWithSettings("SELECT key FROM plato.Input EXCEPT DISTINCT select subkey FROM plato.Input;", settings);
+        UNIT_ASSERT(res.Root);
+
+        TWordCountHive elementStat = {{TString("Except"), 0}};
+        VerifyProgram(res, elementStat, {});
+        UNIT_ASSERT_VALUES_EQUAL(1, elementStat["Except"]);
+    }
+
+
     Y_UNIT_TEST(DeclareDecimalParameter) {
         NYql::TAstParseResult res = SqlToYql("declare $value as Decimal(22,9); select $value as cnt;");
         UNIT_ASSERT(res.Root);
diff --git a/yql/essentials/tests/sql/minirun/part1/canondata/result.json b/yql/essentials/tests/sql/minirun/part1/canondata/result.json
index 021ebed645a..27aab7bf175 100644
--- a/yql/essentials/tests/sql/minirun/part1/canondata/result.json
+++ b/yql/essentials/tests/sql/minirun/part1/canondata/result.json
@@ -426,6 +426,20 @@
             "uri": "https://{canondata_backend}/1903885/f19a3c78c5968d4ae7992179e15806b818abce10/resource.tar.gz#test.test_datetime-date_convert-default.txt-Results_/results.txt"
         }
     ],
+    "test.test[except-except_all-default.txt-Debug]": [
+        {
+            "checksum": "1bdaf8e6cdeeba7ccdbec393da4facda",
+            "size": 2108,
+            "uri": "https://{canondata_backend}/1881367/453c70881991a95154014d718c871d0afdcbf15e/resource.tar.gz#test.test_except-except_all-default.txt-Debug_/opt.yql"
+        }
+    ],
+    "test.test[except-except_all-default.txt-Results]": [
+        {
+            "checksum": "1c2de5c252530ae81b0c46bd53f8ef65",
+            "size": 3437,
+            "uri": "https://{canondata_backend}/1881367/453c70881991a95154014d718c871d0afdcbf15e/resource.tar.gz#test.test_except-except_all-default.txt-Results_/results.txt"
+        }
+    ],
     "test.test[expr-as_table_emptylist2-default.txt-Debug]": [
         {
             "checksum": "6424bb71484d775e2e9f4abcc3b852e6",
diff --git a/yql/essentials/tests/sql/minirun/part2/canondata/result.json b/yql/essentials/tests/sql/minirun/part2/canondata/result.json
index 9ab45a645fc..28b1ecec99c 100644
--- a/yql/essentials/tests/sql/minirun/part2/canondata/result.json
+++ b/yql/essentials/tests/sql/minirun/part2/canondata/result.json
@@ -461,6 +461,20 @@
             "uri": "https://{canondata_backend}/1917492/e1446481f72d95d231bd730cb8c596af631640ea/resource.tar.gz#test.test_datetime-date_types-default.txt-Results_/results.txt"
         }
     ],
+    "test.test[except-except_all_empty-default.txt-Debug]": [
+        {
+            "checksum": "e46b9bf056a427b8de96acf38e6fdc56",
+            "size": 348,
+            "uri": "https://{canondata_backend}/1942278/736d75cf62b2e931435e1042d99607804c1fe8a7/resource.tar.gz#test.test_except-except_all_empty-default.txt-Debug_/opt.yql"
+        }
+    ],
+    "test.test[except-except_all_empty-default.txt-Results]": [
+        {
+            "checksum": "d2e2d092c3bb9360e458b8d8e66baa1d",
+            "size": 1474,
+            "uri": "https://{canondata_backend}/1942278/736d75cf62b2e931435e1042d99607804c1fe8a7/resource.tar.gz#test.test_except-except_all_empty-default.txt-Results_/results.txt"
+        }
+    ],
     "test.test[expr-cast_dynumber-default.txt-Debug]": [
         {
             "checksum": "114987035ac6e54e07a57e3019c0e192",
diff --git a/yql/essentials/tests/sql/minirun/part3/canondata/result.json b/yql/essentials/tests/sql/minirun/part3/canondata/result.json
index baf4cd34051..a62c1531662 100644
--- a/yql/essentials/tests/sql/minirun/part3/canondata/result.json
+++ b/yql/essentials/tests/sql/minirun/part3/canondata/result.json
@@ -704,6 +704,20 @@
             "uri": "https://{canondata_backend}/1936273/60c880bb0a569b92424a6d5d2554b715fd342146/resource.tar.gz#test.test_in-in_with_tuple_simple-default.txt-Results_/results.txt"
         }
     ],
+    "test.test[intersect-intersect_distinct_empty-default.txt-Debug]": [
+        {
+            "checksum": "de3495bdc43ec7707722e1b9585237d0",
+            "size": 298,
+            "uri": "https://{canondata_backend}/1917492/587e362e11daf3cf997a345b018213c9e8643313/resource.tar.gz#test.test_intersect-intersect_distinct_empty-default.txt-Debug_/opt.yql"
+        }
+    ],
+    "test.test[intersect-intersect_distinct_empty-default.txt-Results]": [
+        {
+            "checksum": "36e87fce680f9d5c30c1e656e6be934b",
+            "size": 749,
+            "uri": "https://{canondata_backend}/1917492/587e362e11daf3cf997a345b018213c9e8643313/resource.tar.gz#test.test_intersect-intersect_distinct_empty-default.txt-Results_/results.txt"
+        }
+    ],
     "test.test[join-convert_check_key_mem-default.txt-Debug]": [
         {
             "checksum": "98813e43dffe60bd82a78af8e4a992ba",
diff --git a/yql/essentials/tests/sql/minirun/part4/canondata/result.json b/yql/essentials/tests/sql/minirun/part4/canondata/result.json
index 214ceb66734..653bc9a7089 100644
--- a/yql/essentials/tests/sql/minirun/part4/canondata/result.json
+++ b/yql/essentials/tests/sql/minirun/part4/canondata/result.json
@@ -814,6 +814,20 @@
             "uri": "https://{canondata_backend}/1847551/2d49108793659545d13c6e0b843e2c017302ad43/resource.tar.gz#test.test_in-in_with_nulls_and_optionals_extra-default.txt-Results_/results.txt"
         }
     ],
+    "test.test[intersect-intersect_all_empty-default.txt-Debug]": [
+        {
+            "checksum": "de3495bdc43ec7707722e1b9585237d0",
+            "size": 298,
+            "uri": "https://{canondata_backend}/1937150/92cf5fc8848ad59407c2744be1538c731bcb8de4/resource.tar.gz#test.test_intersect-intersect_all_empty-default.txt-Debug_/opt.yql"
+        }
+    ],
+    "test.test[intersect-intersect_all_empty-default.txt-Results]": [
+        {
+            "checksum": "36e87fce680f9d5c30c1e656e6be934b",
+            "size": 749,
+            "uri": "https://{canondata_backend}/1937150/92cf5fc8848ad59407c2744be1538c731bcb8de4/resource.tar.gz#test.test_intersect-intersect_all_empty-default.txt-Results_/results.txt"
+        }
+    ],
     "test.test[join-cross_join_with_lazy_list-default.txt-Debug]": [
         {
             "checksum": "8687ef86fa2f673b9760d9595f475ece",
diff --git a/yql/essentials/tests/sql/minirun/part6/canondata/result.json b/yql/essentials/tests/sql/minirun/part6/canondata/result.json
index b31a0804b64..c5d1ec58cb3 100644
--- a/yql/essentials/tests/sql/minirun/part6/canondata/result.json
+++ b/yql/essentials/tests/sql/minirun/part6/canondata/result.json
@@ -558,6 +558,20 @@
             "uri": "https://{canondata_backend}/1775059/8f98a745728bc42ccc40bc5c65b23d499853793a/resource.tar.gz#test.test_distinct-distinct_star_inmem-default.txt-Results_/results.txt"
         }
     ],
+    "test.test[except-except_distinct_empty-default.txt-Debug]": [
+        {
+            "checksum": "e46b9bf056a427b8de96acf38e6fdc56",
+            "size": 348,
+            "uri": "https://{canondata_backend}/1942278/fc503167a0e9dd2e6465bc29c2b2299043a83a6c/resource.tar.gz#test.test_except-except_distinct_empty-default.txt-Debug_/opt.yql"
+        }
+    ],
+    "test.test[except-except_distinct_empty-default.txt-Results]": [
+        {
+            "checksum": "d2e2d092c3bb9360e458b8d8e66baa1d",
+            "size": 1474,
+            "uri": "https://{canondata_backend}/1942278/fc503167a0e9dd2e6465bc29c2b2299043a83a6c/resource.tar.gz#test.test_except-except_distinct_empty-default.txt-Results_/results.txt"
+        }
+    ],
     "test.test[expr-as_table-default.txt-Debug]": [
         {
             "checksum": "8799d51aa9290bc664b44e0945b559da",
@@ -824,6 +838,20 @@
             "uri": "https://{canondata_backend}/1031349/7264f379f5ed6029cd16e136c979509c14985388/resource.tar.gz#test.test_in-large_in_YQL-19183-ansi-Results_/results.txt"
         }
     ],
+    "test.test[intersect-intersect_distinct-default.txt-Debug]": [
+        {
+            "checksum": "1528f6b2213ece6a1f4a8f32465e8dea",
+            "size": 2226,
+            "uri": "https://{canondata_backend}/1937424/51d6348179b23fc8977fc8de7b08323f6c5603f5/resource.tar.gz#test.test_intersect-intersect_distinct-default.txt-Debug_/opt.yql"
+        }
+    ],
+    "test.test[intersect-intersect_distinct-default.txt-Results]": [
+        {
+            "checksum": "3f4b92b2d6a3a7a9f18273430118c7b2",
+            "size": 2617,
+            "uri": "https://{canondata_backend}/1937424/51d6348179b23fc8977fc8de7b08323f6c5603f5/resource.tar.gz#test.test_intersect-intersect_distinct-default.txt-Results_/results.txt"
+        }
+    ],
     "test.test[json-combination/nested-default.txt-Debug]": [
         {
             "checksum": "31d64ac4278d43e44b6d9c979b636ce6",
diff --git a/yql/essentials/tests/sql/minirun/part8/canondata/result.json b/yql/essentials/tests/sql/minirun/part8/canondata/result.json
index b47658a25bd..45f139b3e50 100644
--- a/yql/essentials/tests/sql/minirun/part8/canondata/result.json
+++ b/yql/essentials/tests/sql/minirun/part8/canondata/result.json
@@ -639,6 +639,20 @@
             "uri": "https://{canondata_backend}/1903885/a240e9eb95f33f5b5688d6770bbb18aa5d19778a/resource.tar.gz#test.test_datetime-date_tz_scale-default.txt-Results_/results.txt"
         }
     ],
+    "test.test[except-except_distinct-default.txt-Debug]": [
+        {
+            "checksum": "f0505afaff2f21cf0f9bdab5842fd369",
+            "size": 2112,
+            "uri": "https://{canondata_backend}/1942278/3c1745dc69d236a46dcb2ea6952e67da86aa3e9d/resource.tar.gz#test.test_except-except_distinct-default.txt-Debug_/opt.yql"
+        }
+    ],
+    "test.test[except-except_distinct-default.txt-Results]": [
+        {
+            "checksum": "35ad547f6b9d1506b0e4a5ec10f52154",
+            "size": 2929,
+            "uri": "https://{canondata_backend}/1942278/3c1745dc69d236a46dcb2ea6952e67da86aa3e9d/resource.tar.gz#test.test_except-except_distinct-default.txt-Results_/results.txt"
+        }
+    ],
     "test.test[expr-cast_longint-default.txt-Debug]": [
         {
             "checksum": "852e514a4bf927cd6c59acf27e3cc716",
@@ -964,6 +978,20 @@
             "uri": "https://{canondata_backend}/1871182/8488be1009a783ec149801679b1d381d33cbeb2f/resource.tar.gz#test.test_in-YQL-18950-default.txt-Results_/results.txt"
         }
     ],
+    "test.test[intersect-intersect_all-default.txt-Debug]": [
+        {
+            "checksum": "4d081baeeaa6382eb5b8cde859c96164",
+            "size": 2068,
+            "uri": "https://{canondata_backend}/1775059/2b5e7aa24664d04b90cf055001d40e858a3f4532/resource.tar.gz#test.test_intersect-intersect_all-default.txt-Debug_/opt.yql"
+        }
+    ],
+    "test.test[intersect-intersect_all-default.txt-Results]": [
+        {
+            "checksum": "cbcec9c2bbeaa07a3789ddff3f12ad54",
+            "size": 3125,
+            "uri": "https://{canondata_backend}/1775059/2b5e7aa24664d04b90cf055001d40e858a3f4532/resource.tar.gz#test.test_intersect-intersect_all-default.txt-Results_/results.txt"
+        }
+    ],
     "test.test[join-left_join_predicate_push_parent_cross_join-default.txt-Debug]": [
         {
             "checksum": "23ca906191920493283354f7d5513c7a",
diff --git a/yql/essentials/tests/sql/sql2yql/canondata/result.json b/yql/essentials/tests/sql/sql2yql/canondata/result.json
index 2ddd8201e80..d533cfec5ce 100644
--- a/yql/essentials/tests/sql/sql2yql/canondata/result.json
+++ b/yql/essentials/tests/sql/sql2yql/canondata/result.json
@@ -953,9 +953,9 @@
     ],
     "test_sql2yql.test[aggregate-group_by_expr_after_where_ver]": [
         {
-            "checksum": "d7d81aab522ef18bb07e619c426594c5",
+            "checksum": "4799645fef77850f5f5f07de2d1b8bc2",
             "size": 1762,
-            "uri": "https://{canondata_backend}/1597364/99b2cf59a9975dbc2994ead01aa9dcbd784b5279/resource.tar.gz#test_sql2yql.test_aggregate-group_by_expr_after_where_ver_/sql.yql"
+            "uri": "https://{canondata_backend}/1937027/20da4ae4ed8ef86446356b94288d2f22b76637b5/resource.tar.gz#test_sql2yql.test_aggregate-group_by_expr_after_where_ver_/sql.yql"
         }
     ],
     "test_sql2yql.test[aggregate-group_by_rollup_rename]": [
@@ -2337,6 +2337,34 @@
             "uri": "https://{canondata_backend}/1942173/99e88108149e222741552e7e6cddef041d6a2846/resource.tar.gz#test_sql2yql.test_distinct-distinct_star_inmem_/sql.yql"
         }
     ],
+    "test_sql2yql.test[except-except_all]": [
+        {
+            "checksum": "432071f0d21d01d9bd0b792c77db417e",
+            "size": 3566,
+            "uri": "https://{canondata_backend}/1937424/b0b8a9defba63391d95befc1746252b73e2a04d5/resource.tar.gz#test_sql2yql.test_except-except_all_/sql.yql"
+        }
+    ],
+    "test_sql2yql.test[except-except_all_empty]": [
+        {
+            "checksum": "40a32c1c8fb5a979dea406ffe7963eeb",
+            "size": 4163,
+            "uri": "https://{canondata_backend}/1937424/b0b8a9defba63391d95befc1746252b73e2a04d5/resource.tar.gz#test_sql2yql.test_except-except_all_empty_/sql.yql"
+        }
+    ],
+    "test_sql2yql.test[except-except_distinct]": [
+        {
+            "checksum": "a73b03f897702173faee14fdea46dd85",
+            "size": 3560,
+            "uri": "https://{canondata_backend}/1937424/b0b8a9defba63391d95befc1746252b73e2a04d5/resource.tar.gz#test_sql2yql.test_except-except_distinct_/sql.yql"
+        }
+    ],
+    "test_sql2yql.test[except-except_distinct_empty]": [
+        {
+            "checksum": "601b8022980473e8eafdf9365597cf1e",
+            "size": 4154,
+            "uri": "https://{canondata_backend}/1937424/b0b8a9defba63391d95befc1746252b73e2a04d5/resource.tar.gz#test_sql2yql.test_except-except_distinct_empty_/sql.yql"
+        }
+    ],
     "test_sql2yql.test[expr-as_dict_dict_key]": [
         {
             "checksum": "9dd554b71081bd23a148f6d34f15d7ca",
@@ -3891,6 +3919,34 @@
             "uri": "https://{canondata_backend}/1942173/99e88108149e222741552e7e6cddef041d6a2846/resource.tar.gz#test_sql2yql.test_in-small_in_YQL-19183_/sql.yql"
         }
     ],
+    "test_sql2yql.test[intersect-intersect_all]": [
+        {
+            "checksum": "24cbce9982a475e5a7cb746228ef18f7",
+            "size": 3572,
+            "uri": "https://{canondata_backend}/1903885/654a5e42f38e1d04ebfd6797ed162f790c6451d8/resource.tar.gz#test_sql2yql.test_intersect-intersect_all_/sql.yql"
+        }
+    ],
+    "test_sql2yql.test[intersect-intersect_all_empty]": [
+        {
+            "checksum": "6ea841d5596fd7d558edf69fe1107a25",
+            "size": 4172,
+            "uri": "https://{canondata_backend}/1903885/654a5e42f38e1d04ebfd6797ed162f790c6451d8/resource.tar.gz#test_sql2yql.test_intersect-intersect_all_empty_/sql.yql"
+        }
+    ],
+    "test_sql2yql.test[intersect-intersect_distinct]": [
+        {
+            "checksum": "47b396e6add2c3a8d1e2efc3015a261f",
+            "size": 3566,
+            "uri": "https://{canondata_backend}/1903885/654a5e42f38e1d04ebfd6797ed162f790c6451d8/resource.tar.gz#test_sql2yql.test_intersect-intersect_distinct_/sql.yql"
+        }
+    ],
+    "test_sql2yql.test[intersect-intersect_distinct_empty]": [
+        {
+            "checksum": "8d722656304be05a941f2adfd4f32405",
+            "size": 4163,
+            "uri": "https://{canondata_backend}/1903885/654a5e42f38e1d04ebfd6797ed162f790c6451d8/resource.tar.gz#test_sql2yql.test_intersect-intersect_distinct_empty_/sql.yql"
+        }
+    ],
     "test_sql2yql.test[join-convert_check_key_mem2]": [
         {
             "checksum": "e1f4ff23a4f2c04036a52d4a5b25529c",
@@ -4010,13 +4066,6 @@
             "uri": "https://{canondata_backend}/1942525/94a477066ea16f69d4848bbe524485fc029978b8/resource.tar.gz#test_sql2yql.test_join-prune_keys_YQL-19979_/sql.yql"
         }
     ],
-    "test_sql2yql.test[join-prune_keys_YQL-19979]": [
-        {
-            "checksum": "0dad5d395f90148805e893a30f0b4963",
-            "size": 3845,
-            "uri": "https://{canondata_backend}/1942525/94a477066ea16f69d4848bbe524485fc029978b8/resource.tar.gz#test_sql2yql.test_join-prune_keys_YQL-19979_/sql.yql"
-        }
-    ],
     "test_sql2yql.test[join-yql-19192]": [
         {
             "checksum": "fffdf1cbb40643da9daf9bdf3edec121",
@@ -9222,6 +9271,26 @@
             "uri": "file://test_sql_format.test_distinct-distinct_star_inmem_/formatted.sql"
         }
     ],
+    "test_sql_format.test[except-except_all]": [
+        {
+            "uri": "file://test_sql_format.test_except-except_all_/formatted.sql"
+        }
+    ],
+    "test_sql_format.test[except-except_all_empty]": [
+        {
+            "uri": "file://test_sql_format.test_except-except_all_empty_/formatted.sql"
+        }
+    ],
+    "test_sql_format.test[except-except_distinct]": [
+        {
+            "uri": "file://test_sql_format.test_except-except_distinct_/formatted.sql"
+        }
+    ],
+    "test_sql_format.test[except-except_distinct_empty]": [
+        {
+            "uri": "file://test_sql_format.test_except-except_distinct_empty_/formatted.sql"
+        }
+    ],
     "test_sql_format.test[expr-as_dict_dict_key]": [
         {
             "uri": "file://test_sql_format.test_expr-as_dict_dict_key_/formatted.sql"
@@ -10332,6 +10401,26 @@
             "uri": "file://test_sql_format.test_in-small_in_YQL-19183_/formatted.sql"
         }
     ],
+    "test_sql_format.test[intersect-intersect_all]": [
+        {
+            "uri": "file://test_sql_format.test_intersect-intersect_all_/formatted.sql"
+        }
+    ],
+    "test_sql_format.test[intersect-intersect_all_empty]": [
+        {
+            "uri": "file://test_sql_format.test_intersect-intersect_all_empty_/formatted.sql"
+        }
+    ],
+    "test_sql_format.test[intersect-intersect_distinct]": [
+        {
+            "uri": "file://test_sql_format.test_intersect-intersect_distinct_/formatted.sql"
+        }
+    ],
+    "test_sql_format.test[intersect-intersect_distinct_empty]": [
+        {
+            "uri": "file://test_sql_format.test_intersect-intersect_distinct_empty_/formatted.sql"
+        }
+    ],
     "test_sql_format.test[join-convert_check_key_mem2]": [
         {
             "uri": "file://test_sql_format.test_join-convert_check_key_mem2_/formatted.sql"
diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_except-except_all_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_except-except_all_/formatted.sql
new file mode 100644
index 00000000000..917e3d4a593
--- /dev/null
+++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_except-except_all_/formatted.sql
@@ -0,0 +1,51 @@
+SELECT
+    *
+FROM (
+    VALUES
+        (1),
+        (1),
+        (1),
+        (2),
+        (NULL),
+        (NULL),
+        (NULL)
+) AS t (
+    x
+)
+EXCEPT ALL
+SELECT
+    *
+FROM (
+    VALUES
+        (1),
+        (NULL)
+) AS t (
+    x
+);
+
+SELECT
+    *
+FROM (
+    VALUES
+        (1, NULL),
+        (1, NULL),
+        (1, NULL),
+        (NULL, 2),
+        (NULL, NULL),
+        (NULL, NULL),
+        (NULL, NULL)
+) AS t (
+    x,
+    y
+)
+EXCEPT ALL
+SELECT
+    *
+FROM (
+    VALUES
+        (1, NULL),
+        (NULL, NULL)
+) AS t (
+    x,
+    z
+);
diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_except-except_all_empty_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_except-except_all_empty_/formatted.sql
new file mode 100644
index 00000000000..3a516c473a8
--- /dev/null
+++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_except-except_all_empty_/formatted.sql
@@ -0,0 +1,30 @@
+SELECT
+    *
+FROM
+    as_table([])
+EXCEPT ALL
+SELECT
+    *
+FROM
+    as_table([])
+;
+
+SELECT
+    *
+FROM
+    as_table([])
+EXCEPT ALL
+SELECT
+    1 AS x,
+    2 AS y
+;
+
+SELECT
+    1 AS x,
+    2 AS y
+EXCEPT ALL
+SELECT
+    *
+FROM
+    as_table([])
+;
diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_except-except_distinct_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_except-except_distinct_/formatted.sql
new file mode 100644
index 00000000000..f1aacddbb1c
--- /dev/null
+++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_except-except_distinct_/formatted.sql
@@ -0,0 +1,51 @@
+SELECT
+    *
+FROM (
+    VALUES
+        (1),
+        (1),
+        (1),
+        (2),
+        (NULL),
+        (NULL),
+        (NULL)
+) AS t (
+    x
+)
+EXCEPT DISTINCT
+SELECT
+    *
+FROM (
+    VALUES
+        (1),
+        (NULL)
+) AS t (
+    x
+);
+
+SELECT
+    *
+FROM (
+    VALUES
+        (1, NULL),
+        (1, NULL),
+        (1, NULL),
+        (NULL, 2),
+        (NULL, NULL),
+        (NULL, NULL),
+        (NULL, NULL)
+) AS t (
+    x,
+    y
+)
+EXCEPT DISTINCT
+SELECT
+    *
+FROM (
+    VALUES
+        (1, NULL),
+        (NULL, NULL)
+) AS t (
+    x,
+    z
+);
diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_except-except_distinct_empty_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_except-except_distinct_empty_/formatted.sql
new file mode 100644
index 00000000000..d5ffcca0bcf
--- /dev/null
+++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_except-except_distinct_empty_/formatted.sql
@@ -0,0 +1,30 @@
+SELECT
+    *
+FROM
+    as_table([])
+EXCEPT DISTINCT
+SELECT
+    *
+FROM
+    as_table([])
+;
+
+SELECT
+    *
+FROM
+    as_table([])
+EXCEPT DISTINCT
+SELECT
+    1 AS x,
+    2 AS y
+;
+
+SELECT
+    1 AS x,
+    2 AS y
+EXCEPT DISTINCT
+SELECT
+    *
+FROM
+    as_table([])
+;
diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_intersect-intersect_all_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_intersect-intersect_all_/formatted.sql
new file mode 100644
index 00000000000..a76f042e494
--- /dev/null
+++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_intersect-intersect_all_/formatted.sql
@@ -0,0 +1,51 @@
+SELECT
+    *
+FROM (
+    VALUES
+        (1),
+        (1),
+        (2),
+        (NULL),
+        (NULL)
+) AS t (
+    x
+)
+INTERSECT ALL
+SELECT
+    *
+FROM (
+    VALUES
+        (1),
+        (1),
+        (NULL),
+        (NULL)
+) AS t (
+    x
+);
+
+SELECT
+    *
+FROM (
+    VALUES
+        (1, NULL),
+        (1, NULL),
+        (NULL, 2),
+        (NULL, NULL),
+        (NULL, NULL)
+) AS t (
+    x,
+    y
+)
+INTERSECT ALL
+SELECT
+    *
+FROM (
+    VALUES
+        (1, NULL),
+        (1, NULL),
+        (NULL, NULL),
+        (NULL, NULL)
+) AS t (
+    x,
+    z
+);
diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_intersect-intersect_all_empty_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_intersect-intersect_all_empty_/formatted.sql
new file mode 100644
index 00000000000..c89a93a9243
--- /dev/null
+++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_intersect-intersect_all_empty_/formatted.sql
@@ -0,0 +1,30 @@
+SELECT
+    *
+FROM
+    as_table([])
+INTERSECT ALL
+SELECT
+    *
+FROM
+    as_table([])
+;
+
+SELECT
+    *
+FROM
+    as_table([])
+INTERSECT ALL
+SELECT
+    1 AS x,
+    2 AS y
+;
+
+SELECT
+    1 AS x,
+    2 AS y
+INTERSECT ALL
+SELECT
+    *
+FROM
+    as_table([])
+;
diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_intersect-intersect_distinct_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_intersect-intersect_distinct_/formatted.sql
new file mode 100644
index 00000000000..5b7d85a1f46
--- /dev/null
+++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_intersect-intersect_distinct_/formatted.sql
@@ -0,0 +1,51 @@
+SELECT
+    *
+FROM (
+    VALUES
+        (1),
+        (1),
+        (2),
+        (NULL),
+        (NULL)
+) AS t (
+    x
+)
+INTERSECT DISTINCT
+SELECT
+    *
+FROM (
+    VALUES
+        (1),
+        (1),
+        (NULL),
+        (NULL)
+) AS t (
+    x
+);
+
+SELECT
+    *
+FROM (
+    VALUES
+        (1, NULL),
+        (1, NULL),
+        (NULL, 2),
+        (NULL, NULL),
+        (NULL, NULL)
+) AS t (
+    x,
+    y
+)
+INTERSECT DISTINCT
+SELECT
+    *
+FROM (
+    VALUES
+        (1, NULL),
+        (1, NULL),
+        (NULL, NULL),
+        (NULL, NULL)
+) AS t (
+    x,
+    z
+);
diff --git a/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_intersect-intersect_distinct_empty_/formatted.sql b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_intersect-intersect_distinct_empty_/formatted.sql
new file mode 100644
index 00000000000..16623be2f3f
--- /dev/null
+++ b/yql/essentials/tests/sql/sql2yql/canondata/test_sql_format.test_intersect-intersect_distinct_empty_/formatted.sql
@@ -0,0 +1,30 @@
+SELECT
+    *
+FROM
+    as_table([])
+INTERSECT DISTINCT
+SELECT
+    *
+FROM
+    as_table([])
+;
+
+SELECT
+    *
+FROM
+    as_table([])
+INTERSECT DISTINCT
+SELECT
+    1 AS x,
+    2 AS y
+;
+
+SELECT
+    1 AS x,
+    2 AS y
+INTERSECT DISTINCT
+SELECT
+    *
+FROM
+    as_table([])
+;
diff --git a/yql/essentials/tests/sql/sql2yql/test_sql2yql.py b/yql/essentials/tests/sql/sql2yql/test_sql2yql.py
index 924d5f4181d..c8f5bbcd845 100644
--- a/yql/essentials/tests/sql/sql2yql/test_sql2yql.py
+++ b/yql/essentials/tests/sql/sql2yql/test_sql2yql.py
@@ -2,8 +2,11 @@ import os
 
 import yatest.common
 
-from test_utils import pytest_generate_tests_by_template, SQLRUN_PATH, SQL_FLAGS
+from test_utils import get_config, pytest_generate_tests_by_template, SQLRUN_PATH, SQL_FLAGS
 
+from yql_utils import get_langver
+
+DEFAULT_LANG_VER = '2025.01'
 DATA_PATH = yatest.common.source_path('yql/essentials/tests/sql/suites')
 
 
@@ -11,6 +14,25 @@ def pytest_generate_tests(metafunc):
     return pytest_generate_tests_by_template('.sql', metafunc, data_path=DATA_PATH)
 
 
+def _get_cfg_path(suite, case, data_path):
+    cfg_path = os.path.join(data_path, suite, case)
+    if os.path.exists(cfg_path + '.cfg'):
+        return ""
+    else:
+        return "default.txt"
+
+
+def _get_langver(suite, case, data_path):
+    cfg_path = _get_cfg_path(suite, case, data_path)
+    config = get_config(suite, case, cfg_path, data_path=DATA_PATH)
+
+    langver = get_langver(config)
+    if langver is None:
+        langver = DEFAULT_LANG_VER
+
+    return langver
+
+
 def get_sql2yql_cmd(suite, case, case_file, out_dir, ansi_lexer, test_format, test_double_format):
     cmd = [
         SQLRUN_PATH,
@@ -50,6 +72,8 @@ def get_sql2yql_cmd(suite, case, case_file, out_dir, ansi_lexer, test_format, te
     if SQL_FLAGS:
         cmd.append('--flags=%s' % ','.join(SQL_FLAGS))
 
+    cmd.append('--langver=%s' % _get_langver(suite, case, data_path=DATA_PATH))
+
     return cmd
 
 
diff --git a/yql/essentials/tests/sql/suites/except/default.cfg b/yql/essentials/tests/sql/suites/except/default.cfg
new file mode 100644
index 00000000000..cd964fef9e8
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/except/default.cfg
@@ -0,0 +1 @@
+langver 2025.03
diff --git a/yql/essentials/tests/sql/suites/except/except_all.sql b/yql/essentials/tests/sql/suites/except/except_all.sql
new file mode 100644
index 00000000000..917e3d4a593
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/except/except_all.sql
@@ -0,0 +1,51 @@
+SELECT
+    *
+FROM (
+    VALUES
+        (1),
+        (1),
+        (1),
+        (2),
+        (NULL),
+        (NULL),
+        (NULL)
+) AS t (
+    x
+)
+EXCEPT ALL
+SELECT
+    *
+FROM (
+    VALUES
+        (1),
+        (NULL)
+) AS t (
+    x
+);
+
+SELECT
+    *
+FROM (
+    VALUES
+        (1, NULL),
+        (1, NULL),
+        (1, NULL),
+        (NULL, 2),
+        (NULL, NULL),
+        (NULL, NULL),
+        (NULL, NULL)
+) AS t (
+    x,
+    y
+)
+EXCEPT ALL
+SELECT
+    *
+FROM (
+    VALUES
+        (1, NULL),
+        (NULL, NULL)
+) AS t (
+    x,
+    z
+);
diff --git a/yql/essentials/tests/sql/suites/except/except_all_empty.sql b/yql/essentials/tests/sql/suites/except/except_all_empty.sql
new file mode 100644
index 00000000000..3a516c473a8
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/except/except_all_empty.sql
@@ -0,0 +1,30 @@
+SELECT
+    *
+FROM
+    as_table([])
+EXCEPT ALL
+SELECT
+    *
+FROM
+    as_table([])
+;
+
+SELECT
+    *
+FROM
+    as_table([])
+EXCEPT ALL
+SELECT
+    1 AS x,
+    2 AS y
+;
+
+SELECT
+    1 AS x,
+    2 AS y
+EXCEPT ALL
+SELECT
+    *
+FROM
+    as_table([])
+;
diff --git a/yql/essentials/tests/sql/suites/except/except_distinct.sql b/yql/essentials/tests/sql/suites/except/except_distinct.sql
new file mode 100644
index 00000000000..f1aacddbb1c
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/except/except_distinct.sql
@@ -0,0 +1,51 @@
+SELECT
+    *
+FROM (
+    VALUES
+        (1),
+        (1),
+        (1),
+        (2),
+        (NULL),
+        (NULL),
+        (NULL)
+) AS t (
+    x
+)
+EXCEPT DISTINCT
+SELECT
+    *
+FROM (
+    VALUES
+        (1),
+        (NULL)
+) AS t (
+    x
+);
+
+SELECT
+    *
+FROM (
+    VALUES
+        (1, NULL),
+        (1, NULL),
+        (1, NULL),
+        (NULL, 2),
+        (NULL, NULL),
+        (NULL, NULL),
+        (NULL, NULL)
+) AS t (
+    x,
+    y
+)
+EXCEPT DISTINCT
+SELECT
+    *
+FROM (
+    VALUES
+        (1, NULL),
+        (NULL, NULL)
+) AS t (
+    x,
+    z
+);
diff --git a/yql/essentials/tests/sql/suites/except/except_distinct_empty.sql b/yql/essentials/tests/sql/suites/except/except_distinct_empty.sql
new file mode 100644
index 00000000000..d5ffcca0bcf
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/except/except_distinct_empty.sql
@@ -0,0 +1,30 @@
+SELECT
+    *
+FROM
+    as_table([])
+EXCEPT DISTINCT
+SELECT
+    *
+FROM
+    as_table([])
+;
+
+SELECT
+    *
+FROM
+    as_table([])
+EXCEPT DISTINCT
+SELECT
+    1 AS x,
+    2 AS y
+;
+
+SELECT
+    1 AS x,
+    2 AS y
+EXCEPT DISTINCT
+SELECT
+    *
+FROM
+    as_table([])
+;
diff --git a/yql/essentials/tests/sql/suites/intersect/default.cfg b/yql/essentials/tests/sql/suites/intersect/default.cfg
new file mode 100644
index 00000000000..cd964fef9e8
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/intersect/default.cfg
@@ -0,0 +1 @@
+langver 2025.03
diff --git a/yql/essentials/tests/sql/suites/intersect/intersect_all.sql b/yql/essentials/tests/sql/suites/intersect/intersect_all.sql
new file mode 100644
index 00000000000..a76f042e494
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/intersect/intersect_all.sql
@@ -0,0 +1,51 @@
+SELECT
+    *
+FROM (
+    VALUES
+        (1),
+        (1),
+        (2),
+        (NULL),
+        (NULL)
+) AS t (
+    x
+)
+INTERSECT ALL
+SELECT
+    *
+FROM (
+    VALUES
+        (1),
+        (1),
+        (NULL),
+        (NULL)
+) AS t (
+    x
+);
+
+SELECT
+    *
+FROM (
+    VALUES
+        (1, NULL),
+        (1, NULL),
+        (NULL, 2),
+        (NULL, NULL),
+        (NULL, NULL)
+) AS t (
+    x,
+    y
+)
+INTERSECT ALL
+SELECT
+    *
+FROM (
+    VALUES
+        (1, NULL),
+        (1, NULL),
+        (NULL, NULL),
+        (NULL, NULL)
+) AS t (
+    x,
+    z
+);
diff --git a/yql/essentials/tests/sql/suites/intersect/intersect_all_empty.sql b/yql/essentials/tests/sql/suites/intersect/intersect_all_empty.sql
new file mode 100644
index 00000000000..c89a93a9243
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/intersect/intersect_all_empty.sql
@@ -0,0 +1,30 @@
+SELECT
+    *
+FROM
+    as_table([])
+INTERSECT ALL
+SELECT
+    *
+FROM
+    as_table([])
+;
+
+SELECT
+    *
+FROM
+    as_table([])
+INTERSECT ALL
+SELECT
+    1 AS x,
+    2 AS y
+;
+
+SELECT
+    1 AS x,
+    2 AS y
+INTERSECT ALL
+SELECT
+    *
+FROM
+    as_table([])
+;
diff --git a/yql/essentials/tests/sql/suites/intersect/intersect_distinct.sql b/yql/essentials/tests/sql/suites/intersect/intersect_distinct.sql
new file mode 100644
index 00000000000..5b7d85a1f46
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/intersect/intersect_distinct.sql
@@ -0,0 +1,51 @@
+SELECT
+    *
+FROM (
+    VALUES
+        (1),
+        (1),
+        (2),
+        (NULL),
+        (NULL)
+) AS t (
+    x
+)
+INTERSECT DISTINCT
+SELECT
+    *
+FROM (
+    VALUES
+        (1),
+        (1),
+        (NULL),
+        (NULL)
+) AS t (
+    x
+);
+
+SELECT
+    *
+FROM (
+    VALUES
+        (1, NULL),
+        (1, NULL),
+        (NULL, 2),
+        (NULL, NULL),
+        (NULL, NULL)
+) AS t (
+    x,
+    y
+)
+INTERSECT DISTINCT
+SELECT
+    *
+FROM (
+    VALUES
+        (1, NULL),
+        (1, NULL),
+        (NULL, NULL),
+        (NULL, NULL)
+) AS t (
+    x,
+    z
+);
diff --git a/yql/essentials/tests/sql/suites/intersect/intersect_distinct_empty.sql b/yql/essentials/tests/sql/suites/intersect/intersect_distinct_empty.sql
new file mode 100644
index 00000000000..16623be2f3f
--- /dev/null
+++ b/yql/essentials/tests/sql/suites/intersect/intersect_distinct_empty.sql
@@ -0,0 +1,30 @@
+SELECT
+    *
+FROM
+    as_table([])
+INTERSECT DISTINCT
+SELECT
+    *
+FROM
+    as_table([])
+;
+
+SELECT
+    *
+FROM
+    as_table([])
+INTERSECT DISTINCT
+SELECT
+    1 AS x,
+    2 AS y
+;
+
+SELECT
+    1 AS x,
+    2 AS y
+INTERSECT DISTINCT
+SELECT
+    *
+FROM
+    as_table([])
+;
diff --git a/yql/essentials/tools/sql2yql/sql2yql.cpp b/yql/essentials/tools/sql2yql/sql2yql.cpp
index 7ac7615fb40..03198612fdb 100644
--- a/yql/essentials/tools/sql2yql/sql2yql.cpp
+++ b/yql/essentials/tools/sql2yql/sql2yql.cpp
@@ -204,11 +204,20 @@ int BuildAST(int argc, char* argv[]) {
     TString queryString;
     ui16 syntaxVersion;
     TString outFileNameFormat;
+    NYql::TLangVersion langVer;
     THashMap<TString, TString> clusterMapping;
     clusterMapping["plato"] = NYql::YtProviderName;
     clusterMapping["pg_catalog"] = NYql::PgProviderName;
     clusterMapping["information_schema"] = NYql::PgProviderName;
 
+    auto langVerHandler = [&langVer](const TString& str) {
+        if (str == "unknown") {
+            langVer = NYql::UnknownLangVersion;
+        } else if (!NYql::ParseLangVersion(str, langVer)) {
+            throw yexception() << "Failed to parse language version: " << str;
+        }
+    };
+
     THashSet<TString> flags;
 
     opts.AddLongOption('o', "output", "save output to file").RequiredArgument("file").StoreResult(&outFileName);
@@ -234,6 +243,7 @@ int BuildAST(int argc, char* argv[]) {
     opts.AddLongOption("test-lexers", "check other lexers").NoArgument();
     opts.AddLongOption("test-complete", "check completion engine").NoArgument();
     opts.AddLongOption("format-output", "Saves formatted query to it").RequiredArgument("format-output").StoreResult(&outFileNameFormat);
+    opts.AddLongOption("langver", "Set current language version").Optional().RequiredArgument("VER").Handler1T<TString>(langVerHandler);
     opts.SetFreeArgDefaultTitle("query file");
     opts.AddHelpOption();
 
@@ -310,6 +320,7 @@ int BuildAST(int argc, char* argv[]) {
             google::protobuf::Arena arena;
             NSQLTranslation::TTranslationSettings settings;
             settings.Arena = &arena;
+            settings.LangVer = langVer;
             settings.ClusterMapping = clusterMapping;
             settings.Flags = flags;
             settings.SyntaxVersion = syntaxVersion;