5. Set Operations
Union
(combine result sets of multiple queries into a single result set.)
Combines result sets of two or more
SELECT
statements into a single result setThe queries must conform to the following rules:
The number and order of columns in the select list of both queries must be same
The data types must be compatible
It also removes all the duplicate rows from the combined data set
To retain the duplicate rows, you should use the
UNION ALL
instead
UNION with ORDER BY clause
UNION
operator may place the rows from the result ret of the first query before or after or between the rows from the result set of the second queryTo sort the rows in the final result set, you use the
ORDER BY
caluse in the second queryIf you place the ORDER BY clause at the end of each query, the combined result set will not be sorted as you expected.
Examples
We have a table top_rated_films
We have a table most_popular_films
1. Combine data from both tables
2. Combine result sets from both tables including duplicates
3. Combine result sets from both tables including duplicates and ordering them
Intersect
(combine the result sets of two or more queries and returns a single result set that has the rows appear in both result sets.)
This operator combines result sets of two or more SELECT statements into a single result set
It returns any rows that are available in both result sets.
PostgreSQL INTERSECT with ORDER BY clause
If you want to sort the result set returned by the
INTERSECT
operator, you place theORDER BY
at the final query in the query list like in theUNION
operator
Example (Get popular films which are also top rated films)
Except
(return the rows in the first query that does not appear in the output of the second query.)
The
EXCEPT
operator returns distinct rows from the first (left) query that are not in the output of the second (right) query.The queries need to follow these rules
The number of columns and their orders must be same in two queries
The data types of respective columns must be compatible
Example (Find top-rated films that are not popular)
Last updated