Django ORM Union, Intersection and Difference

Starting from Version 1.11 (April 2017),  Django ORM comes with Union, Intersection and Difference capabilities . While it was possible to implement this capability our own, it is nicer to have the ‘batteries included’.

Lets examine this capability using a simple model.

I have added some sample data so that we can make queries

Let’s make some queries:

 

Uses SQL’s UNION operator to combine the results of two or more QuerySets. The UNION operator selects only distinct values by default. To allow duplicate values, use the all=True argument.

Examples:

 

Uses SQL’s INTERSECT operator to return the shared elements of two or more QuerySets.

Examples:

 

Uses SQL’s EXCEPT operator to keep only elements present in the QuerySet but not in some other QuerySets.

Examples:

 

union()intersection(), and difference() return model instances of the type of the first QuerySet even if the arguments are QuerySets of other models. Passing different models works as long as the SELECT list is the same in all QuerySets (at least the types, the names don’t matter as long as the types in the same order).

In addition, only LIMITOFFSETCOUNT(*), and ORDER BY (i.e. slicing, count(), and order_by()) are allowed on the resulting QuerySet. Further, databases place restrictions on what operations are allowed in the combined queries. For example, most databases don’t allow LIMIT or OFFSET in the combined queries.

You may also like...

3 Responses

  1. james says:

    Thank you for this post. what’s the difference using union to using the | operator?

    • josecherian says:

      They are similar. query1 | query2 will give a union of the two queries, but will include duplicates. For query1.uninon(query2), it returns a new queryset with only unique items by default.

  1. December 20, 2017

    […] my blog post on this for more […]

Leave a Reply

Your email address will not be published.