Result Sets Operations

Explore SQL result set operations such as union, intersect, and except to combine and differentiate data from multiple queries. This lesson teaches how to use these operators effectively for aggregating results, removing duplicates, and performing data comparisons, enhancing your ability to manage query output in PostgreSQL.

We'll cover the following...

The union operator
The except operator
Try it yourself

SQL also includes set operations for combining queries result sets into a single one.

In our data model, we have driverstandings and constructorstandings—they contain data that come from the results table that we’ve been using a lot so that we can query a smaller dataset or write simple SQL queries.

The set operations are union, intersect, and except.

The `union` operator

As expected with the union, we can assemble a result set from the result of several queries.

In writing the query, you might notice that we parenthesized the branches of the union. It’s not required that we do so, but it improves the readability of the query and makes it obvious as to what dataset the order by clause is applied to.

PostgreSQL

(
      select raceid,
             'driver' as type,
             format('%s %s',
                    drivers.forename,
                    drivers.surname)
             as name,
             driverstandings.points
        
        from driverstandings
             join drivers using(driverid)
       
       where raceid = 972
         and points > 0
)     
union all
(
      select raceid,
             'constructor' as type,
             constructors.name as name,
             constructorstandings.points
        
        from constructorstandings
             join constructors using(constructorid)
       
       where raceid = 972
         and points > 0
)
order by points desc;

Result Sets Operations

The union operator

The `union` operator