Repository: kayak/pypika
Pull request #447 (2020-06-16 11:11:05)

Changes to include Set Operations
- Intersect
- Minus
- Except

- Unit tests for Intersect, Minus, Except Operations
- Fix for existing issues in Union unit tests

https://github.com/kayak/pypika/issues/250

coveralls: 
[![Coverage Status](https://coveralls.io/builds/31482524/badge)](https://coveralls.io/builds/31482524)

Coverage remained the same at ?% when pulling **3b721eb144ef27fe65377eb23e33eff8e77f2313 on DimpleK26:SET-OPERATIONS** into **cb28b93e9c1159e490c3559e8467c7cfebb0ec5a on kayak:master**.


Patch:
diff --git a/README.rst b/README.rst
index 29f135b..2a81fba 100644
--- a/README.rst
+++ b/README.rst
@@ -508,7 +508,7 @@ Unions
 
 Both ``UNION`` and ``UNION ALL`` are supported. ``UNION DISTINCT`` is synonomous with "UNION`` so and |Brand| does not
 provide a separate function for it.  Unions require that queries have the same number of ``SELECT`` clauses so
-trying to cast a unioned query to string with through a ``UnionException`` if the column sizes are mismatched.
+trying to cast a unioned query to string with through a ``SetOperationException`` if the column sizes are mismatched.
 
 To create a union query, use either the ``Query.union()`` method or `+` operator with two query instances. For a
 union all, use ``Query.union_all()`` or the `*` operator.
@@ -526,6 +526,82 @@ union all, use ``Query.union_all()`` or the `*` operator.
 
     SELECT "created_time","foo","bar" FROM "provider_a" UNION SELECT "created_time","fiz","buz" FROM "provider_b"
 
+Intersect
+"""""""""
+
+``INTERSECT`` is supported. Intersects require that queries have the same number of ``SELECT`` clauses so
+trying to cast a intersected query to string with through a ``SetOperationException`` if the column sizes are mismatched.
+
+To create a intersect query, use the ``Query.intersect()`` method.
+
+.. code-block:: python
+
+    provider_a, provider_b = Tables('provider_a', 'provider_b')
+    q = Query.from_(provider_a).select(
+        provider_a.created_time, provider_a.foo, provider_a.bar
+    )
+    r = Query.from_(provider_b).select(
+        provider_b.created_time, provider_b.fiz, provider_b.buz
+    )
+    intersected_query = q.intersect(r)
+
+.. code-block:: sql
+
+    SELECT "created_time","foo","bar" FROM "provider_a" INTERSECT SELECT "created_time","fiz","buz" FROM "provider_b"
+
+Minus
+"""""
+
+``MINUS`` is supported. Minus require that queries have the same number of ``SELECT`` clauses so
+trying to cast a minus query to string with through a ``SetOperationException`` if the column sizes are mismatched.
+
+To create a minus query, use either the ``Query.minus()`` method or `-` operator with two query instances.
+
+.. code-block:: python
+
+    provider_a, provider_b = Tables('provider_a', 'provider_b')
+    q = Query.from_(provider_a).select(
+        provider_a.created_time, provider_a.foo, provider_a.bar
+    )
+    r = Query.from_(provider_b).select(
+        provider_b.created_time, provider_b.fiz, provider_b.buz
+    )
+    minus_query = q.minus(r)
+
+    (or)
+
+    minus_query = Query.from_(provider_a).select(
+        provider_a.created_time, provider_a.foo, provider_a.bar
+    ) - Query.from_(provider_b).select(
+        provider_b.created_time, provider_b.fiz, provider_b.buz
+    )
+
+.. code-block:: sql
+
+    SELECT "created_time","foo","bar" FROM "provider_a" MINUS SELECT "created_time","fiz","buz" FROM "provider_b"
+
+EXCEPT
+""""""
+
+``EXCEPT`` is supported. Minus require that queries have the same number of ``SELECT`` clauses so
+trying to cast a except query to string with through a ``SetOperationException`` if the column sizes are mismatched.
+
+To create a except query, use the ``Query.except_of()`` method.
+
+.. code-block:: python
+
+    provider_a, provider_b = Tables('provider_a', 'provider_b')
+    q = Query.from_(provider_a).select(
+        provider_a.created_time, provider_a.foo, provider_a.bar
+    )
+    r = Query.from_(provider_b).select(
+        provider_b.created_time, provider_b.fiz, provider_b.buz
+    )
+    minus_query = q.except_of(r)
+
+.. code-block:: sql
+
+    SELECT "created_time","foo","bar" FROM "provider_a" EXCEPT SELECT "created_time","fiz","buz" FROM "provider_b"
 
 Date, Time, and Intervals
 """""""""""""""""""""""""
diff --git a/pypika/__init__.py b/pypika/__init__.py
index a826760..fe7b9a8 100644
--- a/pypika/__init__.py
+++ b/pypika/__init__.py
@@ -88,10 +88,10 @@ from pypika.utils import (
     JoinException,
     QueryException,
     RollupException,
-    UnionException,
+    SetOperationException,
     FunctionException,
 )
 
 __author__ = "Timothy Heys"
 __email__ = "theys@kayak.com"
-__version__ = "0.37.10"
+__version__ = "0.37.7"
diff --git a/pypika/dialects.py b/pypika/dialects.py
index 65ef870..4a18ad0 100644
--- a/pypika/dialects.py
+++ b/pypika/dialects.py
@@ -49,7 +49,7 @@ class MySQLQueryBuilder(QueryBuilder):
 
     def __init__(self, **kwargs: Any) -> None:
         super().__init__(
-              dialect=Dialects.MYSQL, wrap_union_queries=False, **kwargs
+              dialect=Dialects.MYSQL, wrap_set_operation_queries=False, **kwargs
         )
         self._duplicate_updates = []
         self._ignore_duplicates = False
@@ -617,7 +617,7 @@ class ClickHouseQuery(Query):
     """
     @classmethod
     def _builder(cls, **kwargs: Any) -> QueryBuilder:
-        return QueryBuilder(dialect=Dialects.CLICKHOUSE, wrap_union_queries=False, as_keyword=True, **kwargs)
+        return QueryBuilder(dialect=Dialects.CLICKHOUSE, wrap_set_operation_queries=False, as_keyword=True, **kwargs)
 
 
 class SQLLiteValueWrapper(ValueWrapper):
diff --git a/pypika/enums.py b/pypika/enums.py
index cf907dd..b80445c 100644
--- a/pypika/enums.py
+++ b/pypika/enums.py
@@ -58,9 +58,12 @@ class JoinType(Enum):
     cross = "CROSS"
 
 
-class UnionType(Enum):
-    distinct = ""
-    all = " ALL"
+class SetOperation(Enum):
+    union = "UNION"
+    union_all = "UNION ALL"
+    intersect = "INTERSECT"
+    except_of = "EXCEPT"
+    minus = "MINUS"
 
 
 class DatePart(Enum):
diff --git a/pypika/queries.py b/pypika/queries.py
index 65dfd71..56db694 100644
--- a/pypika/queries.py
+++ b/pypika/queries.py
@@ -10,7 +10,7 @@ from typing import (
     Tuple as TypedTuple,
 )
 
-from pypika.enums import JoinType, UnionType, Dialects
+from pypika.enums import JoinType, SetOperation, Dialects
 from pypika.terms import (
     ArithmeticExpression,
     EmptyCriterion,
@@ -29,7 +29,7 @@ from pypika.utils import (
     JoinException,
     QueryException,
     RollupException,
-    UnionException,
+    SetOperationException,
     builder,
     format_alias_sql,
     format_quotes,
@@ -409,19 +409,21 @@ class Query:
         return make_tables(*names, **kwargs)
 
 
-class _UnionQuery(Selectable, Term):
+class _SetOperation(Selectable, Term):
     """
-    A Query class wrapper for a Union query, whether DISTINCT or ALL.
+    A Query class wrapper for a all set operations, Union DISTINCT or ALL, Intersect, Except or Minus
 
-    Created via the functions `Query.union` or `Query.union_all`, this class should not be instantiated directly.
+    Created via the functions `Query.union`,`Query.union_all`,`Query.intersect`, `Query.except_of`,`Query.minus`.
+
+    This class should not be instantiated directly.
     """
 
     def __init__(
-          self, base_query: "QueryBuilder", union_query: "QueryBuilder", union_type: UnionType, alias: Optional[str] = None, wrapper_cls: Type[ValueWrapper] = ValueWrapper,
+          self, base_query: "QueryBuilder", set_operation_query: "QueryBuilder", set_operation: SetOperation, alias: Optional[str] = None, wrapper_cls: Type[ValueWrapper] = ValueWrapper,
     ):
         super().__init__(alias)
         self.base_query = base_query
-        self._unions = [(union_type, union_query)]
+        self._set_operation = [(set_operation, set_operation_query)]
         self._orderbys = []
 
         self._limit = None
@@ -430,7 +432,7 @@ class _UnionQuery(Selectable, Term):
         self._wrapper_cls = wrapper_cls
 
     @builder
-    def orderby(self, *fields: Field, **kwargs: Any) -> "_UnionQuery":
+    def orderby(self, *fields: Field, **kwargs: Any) -> "_SetOperation":
         for field in fields:
             field = (
                 Field(field, table=self.base_query._from[0])
@@ -441,32 +443,47 @@ class _UnionQuery(Selectable, Term):
             self._orderbys.append((field, kwargs.get("order")))
 
     @builder
-    def limit(self, limit: int) -> "_UnionQuery":
+    def limit(self, limit: int) -> "_SetOperation":
         self._limit = limit
 
     @builder
-    def offset(self, offset: int) -> "_UnionQuery":
+    def offset(self, offset: int) -> "_SetOperation":
         self._offset = offset
 
     @builder
-    def union(self, other: Selectable) -> "_UnionQuery":
-        self._unions.append((UnionType.distinct, other))
+    def union(self, other: Selectable) -> "_SetOperation":
+        self._set_operation.append((SetOperation.union, other))
+
+    @builder
+    def union_all(self, other: Selectable) -> "_SetOperation":
+        self._set_operation.append((SetOperation.union_all, other))
+
+    @builder
+    def intersect(self, other: Selectable) -> "_SetOperation":
+        self._set_operation.append((SetOperation.intersect, other))
+
+    @builder
+    def except_of(self, other: Selectable) -> "_SetOperation":
+        self._set_operation.append((SetOperation.except_of, other))
 
     @builder
-    def union_all(self, other: Selectable) -> "_UnionQuery":
-        self._unions.append((UnionType.all, other))
+    def minus(self, other: Selectable) -> "_SetOperation":
+        self._set_operation.append((SetOperation.minus, other))
 
-    def __add__(self, other: Selectable) -> "_UnionQuery":
+    def __add__(self, other: Selectable) -> "_SetOperation":
         return self.union(other)
 
-    def __mul__(self, other: Selectable) -> "_UnionQuery":
+    def __mul__(self, other: Selectable) -> "_SetOperation":
         return self.union_all(other)
 
+    def __sub__(self, other: "QueryBuilder") -> "_SetOperation":
+        return self.minus(other)
+
     def __str__(self) -> str:
         return self.get_sql()
 
     def get_sql(self, with_alias: bool = False, subquery: bool = False, **kwargs: Any) -> str:
-        union_template = " UNION{type} {union}"
+        set_operation_template = " {type} {query_string}"
 
         kwargs.setdefault("dialect", self.base_query.dialect)
         # This initializes the quote char based on the base query, which could be a dialect specific query class
@@ -474,25 +491,25 @@ class _UnionQuery(Selectable, Term):
         kwargs.setdefault("quote_char", self.base_query.QUOTE_CHAR)
 
         base_querystring = self.base_query.get_sql(
-              subquery=self.base_query.wrap_union_queries, **kwargs
+              subquery=self.base_query.wrap_set_operation_queries, **kwargs
         )
 
         querystring = base_querystring
-        for union_type, union_query in self._unions:
-            union_querystring = union_query.get_sql(
-                  subquery=self.base_query.wrap_union_queries, **kwargs
+        for set_operation, set_operation_query in self._set_operation:
+            set_operation_querystring = set_operation_query.get_sql(
+                  subquery=self.base_query.wrap_set_operation_queries, **kwargs
             )
 
-            if len(self.base_query._selects) != len(union_query._selects):
-                raise UnionException(
-                      "Queries must have an equal number of select statements in a union."
-                      "\n\nMain Query:\n{query1}\n\nUnion Query:\n{query2}".format(
-                            query1=base_querystring, query2=union_querystring
+            if len(self.base_query._selects) != len(set_operation_query._selects):
+                raise SetOperationException(
+                      "Queries must have an equal number of select statements in a set operation."
+                      "\n\nMain Query:\n{query1}\n\nSet Operations Query:\n{query2}".format(
+                            query1=base_querystring, query2=set_operation_querystring
                       )
                 )
 
-            querystring += union_template.format(
-                  type=union_type.value, union=union_querystring
+            querystring += set_operation_template.format(
+                  type=set_operation.value, query_string=set_operation_querystring
             )
 
         if self._orderbys:
@@ -560,7 +577,7 @@ class QueryBuilder(Selectable, Term):
     def __init__(
           self,
           dialect: Optional[Dialects] = None,
-          wrap_union_queries: bool = True,
+          wrap_set_operation_queries: bool = True,
           wrapper_cls: Type[ValueWrapper] = ValueWrapper,
           immutable: bool = True,
           as_keyword: bool = False,
@@ -605,7 +622,7 @@ class QueryBuilder(Selectable, Term):
 
         self.dialect = dialect
         self.as_keyword = as_keyword
-        self.wrap_union_queries = wrap_union_queries
+        self.wrap_set_operation_queries = wrap_set_operation_queries
 
         self._wrapper_cls = wrapper_cls
 
@@ -647,7 +664,7 @@ class QueryBuilder(Selectable, Term):
         )
 
         if (
-              isinstance(selectable, (QueryBuilder, _UnionQuery))
+              isinstance(selectable, (QueryBuilder, _SetOperation))
               and selectable.alias is None
         ):
             if isinstance(selectable, QueryBuilder):
@@ -942,26 +959,40 @@ class QueryBuilder(Selectable, Term):
         self._offset = offset
 
     @builder
-    def union(self, other: "QueryBuilder") -> _UnionQuery:
-        return _UnionQuery(
-              self, other, UnionType.distinct, wrapper_cls=self._wrapper_cls
-        )
+    def union(self, other: "QueryBuilder") -> _SetOperation:
+        return _SetOperation(
+              self, other, SetOperation.union, wrapper_cls=self._wrapper_cls)
+
+    @builder
+    def union_all(self, other: "QueryBuilder") -> _SetOperation:
+        return _SetOperation(self, other, SetOperation.union_all, wrapper_cls=self._wrapper_cls)
+
+    @builder
+    def intersect(self, other: "QueryBuilder") -> _SetOperation:
+        return _SetOperation(self, other, SetOperation.intersect, wrapper_cls=self._wrapper_cls)
+
+    @builder
+    def except_of(self, other: "QueryBuilder") -> _SetOperation:
+        return _SetOperation(self, other, SetOperation.except_of, wrapper_cls=self._wrapper_cls)
 
     @builder
-    def union_all(self, other: "QueryBuilder") -> _UnionQuery:
-        return _UnionQuery(self, other, UnionType.all, wrapper_cls=self._wrapper_cls)
+    def minus(self, other: "QueryBuilder") -> _SetOperation:
+        return _SetOperation(self, other, SetOperation.minus, wrapper_cls=self._wrapper_cls)
 
     @builder
     def set(self, field: Union[Field, str], value: Any) -> "QueryBuilder":
         field = Field(field) if not isinstance(field, Field) else field
         self._updates.append((field, self._wrapper_cls(value)))
 
-    def __add__(self, other: "QueryBuilder") -> _UnionQuery:
+    def __add__(self, other: "QueryBuilder") -> _SetOperation:
         return self.union(other)
 
-    def __mul__(self, other: "QueryBuilder") -> _UnionQuery:
+    def __mul__(self, other: "QueryBuilder") -> _SetOperation:
         return self.union_all(other)
 
+    def __sub__(self, other: "QueryBuilder") -> _SetOperation:
+        return self.minus(other)
+
     @builder
     def slice(self, slice: slice) -> "QueryBuilder":
         self._offset = slice.start
diff --git a/pypika/terms.py b/pypika/terms.py
index d5d850e..f16143e 100644
--- a/pypika/terms.py
+++ b/pypika/terms.py
@@ -454,9 +454,6 @@ class EmptyCriterion:
     is_aggregate = None
     tables_ = set()
 
-    def fields_(self) -> Set["Field"]:
-        return set()
-
     def __and__(self, other: Any) -> Any:
         return other
 
@@ -558,7 +555,7 @@ class Tuple(Criterion):
 
     @property
     def is_aggregate(self) -> bool:
-        return resolve_is_aggregate([val.is_aggregate for val in self.values])
+        return all([value.is_aggregate for value in self.values])
 
     @builder
     def replace_table(self, current_table: Optional["Table"], new_table: Optional["Table"]) -> "Tuple":
@@ -977,9 +974,9 @@ class Case(Term):
 
     @property
     def is_aggregate(self) -> Optional[bool]:
-        # True if all criterions/cases are True or None. None all cases are None. Otherwise, False
+        # True if all cases are True or None. None all cases are None. Otherwise, False
         return resolve_is_aggregate(
-              [criterion.is_aggregate or term.is_aggregate for criterion, term in self._cases]
+              [term.is_aggregate for _, term in self._cases]
               + [self._else.is_aggregate if self._else else None]
         )
 
diff --git a/pypika/utils.py b/pypika/utils.py
index 71e4c0f..7ce46f7 100644
--- a/pypika/utils.py
+++ b/pypika/utils.py
@@ -20,7 +20,7 @@ class JoinException(Exception):
     pass
 
 
-class UnionException(Exception):
+class SetOperationException(Exception):
     pass
 
 
diff --git a/setup.cfg b/setup.cfg
index 4047fbd..48b88b9 100644
--- a/setup.cfg
+++ b/setup.cfg
@@ -1,5 +1,5 @@
 [bumpversion]
-current_version = 0.37.10
+current_version = 0.37.7
 commit = True
 tag = True
 


Test patch:
diff --git a/pypika/tests/test_aggregate.py b/pypika/tests/test_aggregate.py
index 72b1769..44a7112 100644
--- a/pypika/tests/test_aggregate.py
+++ b/pypika/tests/test_aggregate.py
@@ -53,15 +53,6 @@ class IsAggregateTests(unittest.TestCase):
         v = 1 / fn.Sum(Field("foo"))
         self.assertTrue(v.is_aggregate)
 
-    def test__agg_case_criterion_is_aggregate(self):
-        v = (
-            Case()
-            .when(fn.Sum(Field("foo")) > 666, 'More than 666')
-            .else_('Less than 666')
-        )
-
-        self.assertTrue(v.is_aggregate)
-
     def test__agg_case_is_aggregate(self):
         v = (
             Case()
@@ -106,7 +97,7 @@ class IsAggregateTests(unittest.TestCase):
 
         self.assertFalse(v.is_aggregate)
 
-    def test__case_with_single_aggregate_field_in_one_criterion_is_aggregate(self):
+    def test__case_with_single_aggregate_field_is_not_aggregate(self):
         v = (
             Case()
             .when(Field("foo") == 1, 1)
@@ -114,7 +105,12 @@ class IsAggregateTests(unittest.TestCase):
             .else_(3)
         )
 
-        self.assertTrue(v.is_aggregate)
+        self.assertFalse(v.is_aggregate)
+
+    def test__case_all_constants_is_aggregate_none(self):
+        v = Case().when(True, 1).when(False, 2).else_(3)
+
+        self.assertIsNone(v.is_aggregate)
 
     def test__non_aggregate_function_with_aggregated_arg(self):
         t = Table("abc")
diff --git a/pypika/tests/test_criterions.py b/pypika/tests/test_criterions.py
index 8fcbc1e..4d35653 100644
--- a/pypika/tests/test_criterions.py
+++ b/pypika/tests/test_criterions.py
@@ -763,13 +763,6 @@ class AnyTests(unittest.TestCase):
         self.assertEqual(str(crit), '"a" OR "b" OR "c" OR "d"')
 
 
-class EmptyCriterionTests(unittest.TestCase):
-    def test_fields_(self):
-        empty_criterion = EmptyCriterion()
-
-        self.assertEqual(len(empty_criterion.fields_()), 0)
-
-
 class AllTests(unittest.TestCase):
     def test_zero_args_returns_empty_criterion(self):
         crit = Criterion.all()
diff --git a/pypika/tests/test_joins.py b/pypika/tests/test_joins.py
index b35edcc..5ceb305 100644
--- a/pypika/tests/test_joins.py
+++ b/pypika/tests/test_joins.py
@@ -9,7 +9,7 @@ from pypika import (
     Query,
     Table,
     Tables,
-    UnionException,
+    SetOperationException,
     functions as fn,
 )
 
@@ -780,46 +780,93 @@ class UnionTests(unittest.TestCase):
             str(query1.union_all(query2)),
         )
 
+    def test_union_all_multiple(self):
+        table3, table4 = Tables("hij", "lmn")
+        query1 = Query.from_(self.table1).select(self.table1.foo)
+        query2 = Query.from_(self.table2).select(self.table2.bar)
+        query3 = Query.from_(table3).select(table3.baz)
+        query4 = Query.from_(table4).select(table4.faz)
+
+        self.assertEqual(
+            '(SELECT "foo" FROM "abc") UNION ALL '
+            '(SELECT "bar" FROM "efg") UNION ALL '
+            '(SELECT "baz" FROM "hij") UNION ALL '
+            '(SELECT "faz" FROM "lmn")',
+            str(query1 * query2 * query3 * query4),
+        )
+        self.assertEqual(
+            '(SELECT "foo" FROM "abc") UNION ALL '
+            '(SELECT "bar" FROM "efg") UNION ALL '
+            '(SELECT "baz" FROM "hij") UNION ALL '
+            '(SELECT "faz" FROM "lmn")',
+            str(query1.union_all(query2).union_all(query3).union_all(query4)),
+        )
+
     def test_union_with_order_by(self):
         query1 = Query.from_(self.table1).select(self.table1.foo.as_("a"))
         query2 = Query.from_(self.table2).select(self.table2.bar.as_("a"))
 
-        union_query = (query1 + query2).orderby(query1.field("a"))
+        union_query = str((query1 + query2).orderby(query1.field("a")))
+        union_all_query = str((query1 * query2).orderby(query1.field("a")))
 
         self.assertEqual(
             '(SELECT "foo" "a" FROM "abc") '
-            "UNION ALL "
+            "UNION "
             '(SELECT "bar" "a" FROM "efg") '
             'ORDER BY "a"',
             union_query,
         )
 
+        self.assertEqual(
+            '(SELECT "foo" "a" FROM "abc") '
+            "UNION ALL "
+            '(SELECT "bar" "a" FROM "efg") '
+            'ORDER BY "a"',
+            union_all_query,
+        )
+
     def test_union_with_order_by_use_union_query_field(self):
         query1 = Query.from_(self.table1).select(self.table1.foo.as_("a"))
         query2 = Query.from_(self.table2).select(self.table2.bar.as_("a"))
 
         union_query = query1 + query2
-        union_query = union_query.orderby(union_query.field("a"))
+        union_query = str(union_query.orderby(union_query.field("a")))
+        union_all_query = query1 * query2
+        union_all_query = str(union_all_query.orderby(union_all_query.field("a")))
 
         self.assertEqual(
             '(SELECT "foo" "a" FROM "abc") '
-            "UNION ALL "
+            "UNION "
             '(SELECT "bar" "a" FROM "efg") '
             'ORDER BY "a"',
             union_query,
         )
+        self.assertEqual(
+            '(SELECT "foo" "a" FROM "abc") '
+            "UNION ALL "
+            '(SELECT "bar" "a" FROM "efg") '
+            'ORDER BY "a"',
+            union_all_query,
+        )
 
     def test_union_with_order_by_with_aliases(self):
-        query1 = Query.from_(self.table1).select(self.table1.foo.as_("a")).as_("a")
-        query2 = Query.from_(self.table2).select(self.table2.bar.as_("a")).as_("b")
-
-        union_query = (query1 + query2).orderby(query1.field("a"))
+        query1 = Query.from_(self.table1.as_("a")).select(self.table1.foo.as_("a"))
+        query2 = Query.from_(self.table2.as_("b")).select(self.table2.bar.as_("a"))
 
+        union_all_query = str((query1 * query2).orderby(query1.field("a")))
+        union_query = str((query1 + query2).orderby(query1.field("a")))
         self.assertEqual(
-            '(SELECT "foo" "a" FROM "abc") "a" '
-            "UNION ALL "
-            '(SELECT "bar" "a" FROM "efg") "b" '
-            'ORDER BY "a"."a"',
+            '(SELECT "foo" "a" FROM "abc" "a")'
+            " UNION ALL "
+            '(SELECT "bar" "a" FROM "efg" "b")'
+            ' ORDER BY "a"',
+            union_all_query,
+        )
+        self.assertEqual(
+            '(SELECT "foo" "a" FROM "abc" "a")'
+            " UNION "
+            '(SELECT "bar" "a" FROM "efg" "b")'
+            ' ORDER BY "a"',
             union_query,
         )
 
@@ -829,20 +876,29 @@ class UnionTests(unittest.TestCase):
 
         union_query = (query1 + query2).as_("x")
         union_query = union_query.orderby(union_query.field("a"))
+        union_all_query = (query1 * query2).as_("x")
+        union_all_query = union_all_query.orderby(union_all_query.field("a"))
 
+        self.assertEqual(
+            '(SELECT "foo" "a" FROM "abc") '
+            "UNION "
+            '(SELECT "bar" "a" FROM "efg") '
+            'ORDER BY "x"."a"',
+            str(union_query),
+        )
         self.assertEqual(
             '(SELECT "foo" "a" FROM "abc") '
             "UNION ALL "
             '(SELECT "bar" "a" FROM "efg") '
-            'ORDER BY "a"',
-            union_query,
+            'ORDER BY "x"."a"',
+            str(union_all_query),
         )
 
     def test_require_equal_number_of_fields(self):
         query1 = Query.from_(self.table1).select(self.table1.foo)
         query2 = Query.from_(self.table2).select(self.table2.fiz, self.table2.buz)
 
-        with self.assertRaises(UnionException):
+        with self.assertRaises(SetOperationException):
             str(query1 + query2)
 
     def test_mysql_query_does_not_wrap_unioned_queries_with_params(self):
@@ -916,3 +972,299 @@ class UpdateQueryJoinTests(unittest.TestCase):
             'WHERE "b"."foo"=1',
             str(q),
         )
+
+
+class IntersectTests(unittest.TestCase):
+    table1, table2 = Tables("abc", "efg")
+
+    def test_intersect(self):
+        query1 = Query.from_(self.table1).select(self.table1.foo)
+        query2 = Query.from_(self.table2).select(self.table2.bar)
+
+        self.assertEqual(
+            '(SELECT "foo" FROM "abc") INTERSECT (SELECT "bar" FROM "efg")',
+            str(query1.intersect(query2)),
+        )
+
+    def test_intersect_multiple(self):
+        table3, table4 = Tables("hij", "lmn")
+        query1 = Query.from_(self.table1).select(self.table1.foo)
+        query2 = Query.from_(self.table2).select(self.table2.bar)
+        query3 = Query.from_(table3).select(table3.baz)
+        query4 = Query.from_(table4).select(table4.faz)
+
+        self.assertEqual(
+            '(SELECT "foo" FROM "abc") INTERSECT '
+            '(SELECT "bar" FROM "efg") INTERSECT '
+            '(SELECT "baz" FROM "hij") INTERSECT '
+            '(SELECT "faz" FROM "lmn")',
+            str(query1.intersect(query2).intersect(query3).intersect(query4)),
+        )
+
+    def test_intersect_with_order_by(self):
+        query1 = Query.from_(self.table1).select(self.table1.foo.as_("a"))
+        query2 = Query.from_(self.table2).select(self.table2.bar.as_("a"))
+
+        intersect_query = str((query1.intersect(query2)).orderby(query1.field("a")))
+
+        self.assertEqual(
+            '(SELECT "foo" "a" FROM "abc") '
+            "INTERSECT "
+            '(SELECT "bar" "a" FROM "efg") '
+            'ORDER BY "a"',
+            intersect_query,
+        )
+
+    def test_intersect_with_limit(self):
+        query1 = Query.from_(self.table1).select(self.table1.foo.as_("a"))
+        query2 = Query.from_(self.table2).select(self.table2.bar.as_("a"))
+
+        intersect_query = str((query1.intersect(query2)).limit(10))
+
+        self.assertEqual(
+            '(SELECT "foo" "a" FROM "abc") '
+            "INTERSECT "
+            '(SELECT "bar" "a" FROM "efg") '
+            'LIMIT 10',
+            intersect_query,
+        )
+
+    def test_intersect_with_offset(self):
+        query1 = Query.from_(self.table1).select(self.table1.foo.as_("a"))
+        query2 = Query.from_(self.table2).select(self.table2.bar.as_("a"))
+
+        intersect_query = str((query1.intersect(query2)).offset(10))
+
+        self.assertEqual(
+            '(SELECT "foo" "a" FROM "abc") '
+            "INTERSECT "
+            '(SELECT "bar" "a" FROM "efg") '
+            'OFFSET 10',
+            intersect_query,
+        )
+
+    def test_require_equal_number_of_fields_intersect(self):
+        query1 = Query.from_(self.table1).select(self.table1.foo)
+        query2 = Query.from_(self.table2).select(self.table2.fiz, self.table2.buz)
+
+        with self.assertRaises(SetOperationException):
+            str(query1.intersect(query2))
+
+    def test_mysql_query_does_not_wrap_intersected_queries_with_params(self):
+        query1 = MySQLQuery.from_(self.table1).select(self.table1.foo)
+        query2 = Query.from_(self.table2).select(self.table2.bar)
+
+        self.assertEqual(
+            "SELECT `foo` FROM `abc` INTERSECT SELECT `bar` FROM `efg`",
+            str(query1.intersect(query2)),
+        )
+
+    def test_intersect_as_subquery(self):
+        abc, efg = Tables("abc", "efg")
+        hij = Query.from_(abc).select(abc.t).intersect(Query.from_(efg).select(efg.t))
+        q = Query.from_(hij).select(fn.Avg(hij.t))
+
+        self.assertEqual(
+            'SELECT AVG("sq0"."t") FROM ((SELECT "t" FROM "abc") INTERSECT (SELECT "t" FROM "efg")) "sq0"',
+            str(q),
+        )
+
+    def test_intersect_with_no_quote_char(self):
+        abc, efg = Tables("abc", "efg")
+        hij = Query.from_(abc).select(abc.t).intersect(Query.from_(efg).select(efg.t))
+        q = Query.from_(hij).select(fn.Avg(hij.t))
+
+        self.assertEqual(
+            "SELECT AVG(sq0.t) FROM ((SELECT t FROM abc) INTERSECT (SELECT t FROM efg)) sq0",
+            q.get_sql(quote_char=None),
+        )
+
+
+class MinusTests(unittest.TestCase):
+    table1, table2 = Tables("abc", "efg")
+
+    def test_minus(self):
+        query1 = Query.from_(self.table1).select(self.table1.foo)
+        query2 = Query.from_(self.table2).select(self.table2.bar)
+
+        self.assertEqual(
+            '(SELECT "foo" FROM "abc") MINUS (SELECT "bar" FROM "efg")',
+            str(query1.minus(query2))
+        )
+
+        self.assertEqual(
+            '(SELECT "foo" FROM "abc") MINUS (SELECT "bar" FROM "efg")',
+            str(query1 - query2),
+        )
+
+    def test_minus_multiple(self):
+        table3, table4 = Tables("hij", "lmn")
+        query1 = Query.from_(self.table1).select(self.table1.foo)
+        query2 = Query.from_(self.table2).select(self.table2.bar)
+        query3 = Query.from_(table3).select(table3.baz)
+        query4 = Query.from_(table4).select(table4.faz)
+
+        self.assertEqual(
+            '(SELECT "foo" FROM "abc") MINUS '
+            '(SELECT "bar" FROM "efg") MINUS '
+            '(SELECT "baz" FROM "hij") MINUS '
+            '(SELECT "faz" FROM "lmn")',
+            str(query1 - query2 - query3 - query4),
+        )
+
+        self.assertEqual(
+            '(SELECT "foo" FROM "abc") MINUS '
+            '(SELECT "bar" FROM "efg") MINUS '
+            '(SELECT "baz" FROM "hij") MINUS '
+            '(SELECT "faz" FROM "lmn")',
+            str(query1.minus(query2).minus(query3).minus(query4)),
+        )
+
+    def test_minus_with_order_by(self):
+        query1 = Query.from_(self.table1).select(self.table1.foo.as_("a"))
+        query2 = Query.from_(self.table2).select(self.table2.bar.as_("a"))
+
+        minus_query = str(query1.minus(query2).orderby(query1.field("a")))
+
+        self.assertEqual(
+            '(SELECT "foo" "a" FROM "abc") '
+            "MINUS "
+            '(SELECT "bar" "a" FROM "efg") '
+            'ORDER BY "a"',
+            minus_query
+        )
+
+    def test_minus_query_with_order_by_use_minus_query_field(self):
+        query1 = Query.from_(self.table1).select(self.table1.foo.as_("a"))
+        query2 = Query.from_(self.table2).select(self.table2.bar.as_("a"))
+
+        minus_query = query1.minus(query2)
+        minus_query = str(minus_query.orderby(minus_query.field("b")))
+
+        self.assertEqual(
+            '(SELECT "foo" "a" FROM "abc") '
+            "MINUS "
+            '(SELECT "bar" "a" FROM "efg") '
+            'ORDER BY "b"',
+            minus_query,
+        )
+
+    def test_require_equal_number_of_fields(self):
+        query1 = Query.from_(self.table1).select(self.table1.foo)
+        query2 = Query.from_(self.table2).select(self.table2.fiz, self.table2.buz)
+
+        with self.assertRaises(SetOperationException):
+            str(query1.minus(query2))
+
+    def test_mysql_query_does_not_wrap_minus_queries_with_params(self):
+        query1 = MySQLQuery.from_(self.table1).select(self.table1.foo)
+        query2 = Query.from_(self.table2).select(self.table2.bar)
+
+        self.assertEqual(
+            "SELECT `foo` FROM `abc` MINUS SELECT `bar` FROM `efg`",
+            str(query1 - query2),
+        )
+
+    def test_minus_as_subquery(self):
+        abc, efg = Tables("abc", "efg")
+        hij = Query.from_(abc).select(abc.t).minus(Query.from_(efg).select(efg.t))
+        q = Query.from_(hij).select(fn.Avg(hij.t))
+
+        self.assertEqual(
+            'SELECT AVG("sq0"."t") FROM ((SELECT "t" FROM "abc") MINUS (SELECT "t" FROM "efg")) "sq0"',
+            str(q),
+        )
+
+    def test_minus_with_no_quote_char(self):
+        abc, efg = Tables("abc", "efg")
+        hij = Query.from_(abc).select(abc.t).minus(Query.from_(efg).select(efg.t))
+        q = Query.from_(hij).select(fn.Avg(hij.t))
+
+        self.assertEqual(
+            "SELECT AVG(sq0.t) FROM ((SELECT t FROM abc) MINUS (SELECT t FROM efg)) sq0",
+            q.get_sql(quote_char=None),
+        )
+
+
+class ExceptOfTests(unittest.TestCase):
+    table1, table2 = Tables("abc", "efg")
+
+    def test_except(self):
+        query1 = Query.from_(self.table1).select(self.table1.foo)
+        query2 = Query.from_(self.table2).select(self.table2.bar)
+
+        self.assertEqual(
+            '(SELECT "foo" FROM "abc") EXCEPT (SELECT "bar" FROM "efg")',
+            str(query1.except_of(query2))
+        )
+
+    def test_except_multiple(self):
+        table3, table4 = Tables("hij", "lmn")
+        query1 = Query.from_(self.table1).select(self.table1.foo)
+        query2 = Query.from_(self.table2).select(self.table2.bar)
+        query3 = Query.from_(table3).select(table3.baz)
+        query4 = Query.from_(table4).select(table4.faz)
+
+        self.assertEqual(
+            '(SELECT "foo" FROM "abc") EXCEPT '
+            '(SELECT "bar" FROM "efg") EXCEPT '
+            '(SELECT "baz" FROM "hij") EXCEPT '
+            '(SELECT "faz" FROM "lmn")',
+            str(query1.except_of(query2).except_of(query3).except_of(query4)),
+        )
+
+    def test_except_with_order_by(self):
+        query1 = Query.from_(self.table1).select(self.table1.foo.as_("a"))
+        query2 = Query.from_(self.table2).select(self.table2.bar.as_("a"))
+
+        except_query = str(query1.except_of(query2).orderby(query1.field("a")))
+
+        self.assertEqual(
+            '(SELECT "foo" "a" FROM "abc") '
+            "EXCEPT "
+            '(SELECT "bar" "a" FROM "efg") '
+            'ORDER BY "a"',
+            except_query
+        )
+
+    def test_except_query_with_order_by_use_minus_query_field(self):
+        query1 = Query.from_(self.table1).select(self.table1.foo.as_("a"))
+        query2 = Query.from_(self.table2).select(self.table2.bar.as_("a"))
+
+        except_query = query1.except_of(query2)
+        except_query = str(except_query.orderby(except_query.field("b")))
+
+        self.assertEqual(
+            '(SELECT "foo" "a" FROM "abc") '
+            "EXCEPT "
+            '(SELECT "bar" "a" FROM "efg") '
+            'ORDER BY "b"',
+            except_query,
+        )
+
+    def test_require_equal_number_of_fields_except_of(self):
+        query1 = Query.from_(self.table1).select(self.table1.foo)
+        query2 = Query.from_(self.table2).select(self.table2.fiz, self.table2.buz)
+
+        with self.assertRaises(SetOperationException):
+            str(query1.except_of(query2))
+
+    def test_except_as_subquery(self):
+        abc, efg = Tables("abc", "efg")
+        hij = Query.from_(abc).select(abc.t).except_of(Query.from_(efg).select(efg.t))
+        q = Query.from_(hij).select(fn.Avg(hij.t))
+
+        self.assertEqual(
+            'SELECT AVG("sq0"."t") FROM ((SELECT "t" FROM "abc") EXCEPT (SELECT "t" FROM "efg")) "sq0"',
+            str(q),
+        )
+
+    def test_except_with_no_quote_char(self):
+        abc, efg = Tables("abc", "efg")
+        hij = Query.from_(abc).select(abc.t).except_of(Query.from_(efg).select(efg.t))
+        q = Query.from_(hij).select(fn.Avg(hij.t))
+
+        self.assertEqual(
+            "SELECT AVG(sq0.t) FROM ((SELECT t FROM abc) EXCEPT (SELECT t FROM efg)) sq0",
+            q.get_sql(quote_char=None),
+        )
diff --git a/pypika/tests/test_tuples.py b/pypika/tests/test_tuples.py
index 2fab67f..374c03c 100644
--- a/pypika/tests/test_tuples.py
+++ b/pypika/tests/test_tuples.py
@@ -3,12 +3,12 @@ import unittest
 from pypika import (
     Array,
     Bracket,
+    PostgreSQLQuery,
     Query,
     Table,
     Tables,
     Tuple,
 )
-from pypika.terms import AggregateFunction, Function
 
 
 class TupleTests(unittest.TestCase):
@@ -124,27 +124,6 @@ class TupleTests(unittest.TestCase):
         q = Query.from_(tb).select(Tuple(tb.col).as_("different_name"))
         self.assertEqual(str(q), 'SELECT ("col") "different_name" FROM "tb"')
 
-    def test_tuple_is_aggregate_None_if_args_implement_is_aggregate_None(self):
-        with self.subTest('single non-aggregatable argument'):
-            self.assertEqual(None, Tuple(0).is_aggregate)
-
-        with self.subTest('multiple non-aggregatable arguments'):
-            self.assertEqual(None, Tuple(0, 'a').is_aggregate)
-
-    def test_tuple_is_aggregate_True_if_args_all_implement_is_aggregate_True(self):
-        with self.subTest('single aggregatable function'):
-            self.assertEqual(True, Tuple(AggregateFunction('col')).is_aggregate)
-
-        with self.subTest('multiple aggregatable functions'):
-            self.assertEqual(True, Tuple(AggregateFunction('col'), AggregateFunction('col2')).is_aggregate)
-
-    def test_tuple_is_aggregate_False_if_some_of_the_args_implement_is_aggregate_False(self):
-        with self.subTest('single non-aggregatable function'):
-            self.assertEqual(False, Tuple(Function('col')).is_aggregate)
-
-        with self.subTest('when a mix of non-aggregatable and aggregatable functions'):
-            self.assertEqual(False, Tuple(Function('col'), Function('col2', 0), AggregateFunction('col3')).is_aggregate)
-
 
 class ArrayTests(unittest.TestCase):
     table_abc, table_efg = Tables("abc", "efg")