Added support for INTERSECT and EXCEPT

4 files changed, 74 insertions, 0 deletions

diff --git a/lib/arel/nodes/binary.rb b/lib/arel/nodes/binary.rb
index e72228f11c..bcd46db398 100644
--- a/lib/arel/nodes/binary.rb
+++ b/lib/arel/nodes/binary.rb
@@ -31,6 +31,8 @@ module Arel
       Or
       Union
       UnionAll
+      Intersect
+      Except
     }.each do |name|
       const_set name, Class.new(Binary)
     end
diff --git a/lib/arel/select_manager.rb b/lib/arel/select_manager.rb
index 4e1382224d..afc46a626e 100644
--- a/lib/arel/select_manager.rb
+++ b/lib/arel/select_manager.rb
@@ -145,6 +145,16 @@ module Arel
       node_class.new self.ast, other.ast
     end
 
+    def intersect other = nil
+      node_class = Nodes::Intersect
+      node_class.new self.ast, other.ast
+    end
+
+    def except other = nil
+      node_class = Nodes::Except
+      node_class.new self.ast, other.ast
+    end
+
     def with *subqueries
       if subqueries.first.is_a? Symbol
         node_class = Nodes.const_get("With#{subqueries.shift.to_s.capitalize}")
diff --git a/lib/arel/visitors/to_sql.rb b/lib/arel/visitors/to_sql.rb
index 7cb4213fbb..a395b7f765 100644
--- a/lib/arel/visitors/to_sql.rb
+++ b/lib/arel/visitors/to_sql.rb
@@ -166,6 +166,14 @@ key on UpdateManager using UpdateManager#key=
         "( #{visit o.left} UNION ALL #{visit o.right} )"
       end
 
+      def visit_Arel_Nodes_Intersect o
+        "( #{visit o.left} INTERSECT #{visit o.right} )"
+      end
+
+      def visit_Arel_Nodes_Except o
+        "( #{visit o.left} EXCEPT #{visit o.right} )"
+      end
+
       def visit_Arel_Nodes_Having o
         "HAVING #{visit o.expr}"
       end
diff --git a/test/test_select_manager.rb b/test/test_select_manager.rb
index bd96345b01..f457c55f40 100644
--- a/test/test_select_manager.rb
+++ b/test/test_select_manager.rb
@@ -213,6 +213,60 @@ module Arel
 
     end
 
+    describe 'intersect' do
+      before do
+        table = Table.new :users
+        @m1 = Arel::SelectManager.new Table.engine, table
+        @m1.project Arel.star
+        @m1.where(table[:age].gt(18))
+
+        @m2 = Arel::SelectManager.new Table.engine, table
+        @m2.project Arel.star
+        @m2.where(table[:age].lt(99))
+
+
+      end
+
+      it 'should interect two managers' do
+        # FIXME should this intersect "managers" or "statements" ?
+        # FIXME this probably shouldn't return a node
+        node = @m1.intersect @m2
+
+        # maybe FIXME: decide when wrapper parens are needed
+        node.to_sql.must_be_like %{
+          ( SELECT * FROM "users"  WHERE "users"."age" > 18 INTERSECT SELECT * FROM "users"  WHERE "users"."age" < 99 )
+        }
+      end
+
+    end
+
+    describe 'except' do
+      before do
+        table = Table.new :users
+        @m1 = Arel::SelectManager.new Table.engine, table
+        @m1.project Arel.star
+        @m1.where(table[:age].in(18..60))
+
+        @m2 = Arel::SelectManager.new Table.engine, table
+        @m2.project Arel.star
+        @m2.where(table[:age].in(40..99))
+
+
+      end
+
+      it 'should except two managers' do
+        # FIXME should this except "managers" or "statements" ?
+        # FIXME this probably shouldn't return a node
+        node = @m1.except @m2
+
+        # maybe FIXME: decide when wrapper parens are needed
+        node.to_sql.must_be_like %{
+          ( SELECT * FROM "users"  WHERE "users"."age" BETWEEN 18 AND 60 EXCEPT SELECT * FROM "users"  WHERE "users"."age" BETWEEN 40 AND 99 )
+        }
+      end
+
+    end
+
     describe 'with' do
 
       it "should support WITH RECURSIVE" do