Your Web News in One Place

Help Webnuz

Referal links:

Sign up for GreenGeeks web hosting
April 26, 2021 08:43 pm GMT

Getting started with PostgreSQL Set Operators

Postgres offers set operators that make it easy to query and filter the results of searches from your database. Set operators are used to join the results of two or more SELECT statements. These operators are UNION, UNION ALL, INTERSECT, and EXCEPTeach can be used to construct queries across multiple tables and filter for the specific data that you need.

To return the combined results of two SELECT statements, we use the UNION set operator. This operator removes all the duplicates from the queried resultsonly listing one row for each duplicated result. To examine this behavior, the UNION ALL set operator can be used, as it retains duplicates in the final result. The INTERSECT set operator only lists records that are shared by both SELECT queries, and conversely, the EXCEPT set operator removes the results from the second SELECT query. Thus, the INTERSECT and EXCEPT set operators are used to produce unduplicated results.

All set operators initially share the same degree of precedence (except for INTERSECT, which we'll discuss later). Because parentheses are prioritized above dangling operators, they can cause the order to differ.

Note
All SELECT statements that are used with a SET operator must select the same number of columns. The columns used in the display are obtained from the first query.

The column-list data types must be implicitly convertible by Postgres. Postgres does not perform implicit type conversion if the corresponding columns in the queries belong to different data types. If a column in the first query of type INT, and the corresponding column in the second query is of type CHAR, Postgres will not perform an implicit conversioninstead, it will raise a type error.

To sort the result set, positional ordering is used. Set operators do not allow individual result set ordering. At the end of the query, ORDER BY can only appear once.

The order of the queries in UNION and INTERSECT operators is not important, and doesn't change the final result - UNION and INTERSECT operators are commutative.

UNION ALL has better performance compared to the UNION operator because resources are not used in filtering duplicates and sorting the results.

  • It is possible to use set operators as part of subqueries.
  • It is not possible to use set operators in SELECT statements containing TABLE collection expressions.

Creating Tables

Before we continue exploring the set operators, we need to create tables that we will be using throughout the tutorial:

DROP TABLE IF EXISTS top_rated_films;CREATE TABLE top_rated_films(title VARCHAR NOT NULL,release_year SMALLINT);DROP TABLE IF EXISTS most_popular_films;CREATE TABLE most_popular_films(title VARCHAR NOT NULL,release_year SMALLINT);INSERT INTO top_rated_films(title, release_year)VALUES('Fast and furious 7', 2011),('The redemption', 2015),('Shooter', 2017);INSERT INTO most_popular_films(title, release_year)VALUES('American snipper', 2015),('Doulou continent',2018),('Outpost',2019),('Shooter', 2017);

create_tables

UNION

Postgres displays the combined results from all of the compounded SELECT queries after removal of duplicate values, and the results are sorted in ascending order when the UNION operator joins multiple SELECT queries:

SELECT * FROM top_rated_filmsUNIONSELECT * FROM most_popular_films

union operator example in the Arctype interface

Note
the columns selected must be of the compatible data types, otherwise Postgres throws a type error:

SELECT title FROM top_rated_filmsUNIONSELECT release_year FROM most_popular_films//errorUNION types character varying and smallint cannot be matched

UNION ALL

The UNION and UNION ALL operators are similarthe difference being that UNION ALL returns the result set without removing duplicates and sorting the data.

Check the query in the UNION section below. Make sure to note the difference in the output that is generated without sorting and removal of duplicates:

SELECT * FROM top_rated_filmsUNION ALLSELECT * FROM most_popular_films

union_all operator example in the Arctype interface

INTERSECT

To display the common rows from both the select statements with no duplicates, and with data arranged in sorted order; the INTERSECT operator is used.
The shooter movie is returned because it is available in both top rated and most popular movie tables.

SELECT *FROM most_popular_films INTERSECTSELECT *FROM top_rated_films;

intersect operator example in the Arctype interface

EXCEPT

To display rows that are present in the first query but absent in the second one, the EXCEPT operator is used. It returns no duplicates, and the data is arranged in ascending order by default.

SELECT * FROM most_popular_filmsEXCEPTSELECT * FROM top_rated_films

except operator example in the Arctype interface

Using ORDER BY clause in SET operations

In a query containing a compound SELECT statement, the ORDER BY clause can only appear once at the end. This indicates that individual SELECT statements cannot contain the ORDER BY clause. Sorting is based on the columns that appear in the first SELECT query only. It is recommended to sort compound queries using column positions.

The query below sorts the results from two departments and unifies them:

SELECT * FROM most_popular_filmsUNION ALLSELECT * FROM top_rated_filmsORDER BY title;

order_by clause example in the Arctype interface

Conclusion

Now that you have learned how to use Postgres set operators, you can optimize your database queries with them to retrieve and compare data across different columnsallowing for a more streamlined data retrieval process.


Original Link: https://dev.to/arctype/getting-started-with-postgresql-set-operators-406c

Share this article:    Share on Facebook
View Full Article

Dev To

An online community for sharing and discovering great ideas, having debates, and making friends

More About this Source Visit Dev To