PostgreSQL UNION Clause: Combine Results from Multiple Queries

Introduction

Ever find yourself in a situation where you want to pull together results from separate queries into a single result set? Well, the UNION clause in PostgreSQL is your friend here. In this guide, we’ll dive into how to use the UNION clause effectively, giving you the tips you need to avoid common mistakes and optimize your queries.

Basic Syntax and Usage

The fundamental syntax of a UNION query involves two or more SELECT queries separated by the UNION keyword.

SELECT column1, column2 FROM table1
UNION
SELECT column1, column2 FROM table2;

Here, column1 and column2 must be of the same data type in both table1 and table2.

Example with tv_series Table

Assume we have a tv_series table and another table called web_series. We’ll fetch names of series from both tables that have been released after 2010.

SELECT name FROM tv_series WHERE release_year > 2010
UNION
SELECT name FROM web_series WHERE release_year > 2010;

web_series Table

Let us create a new table called web_series and seed it with some data. This table will be used by the queries below.

-- Create web_series table
CREATE TABLE public.web_series (
    id serial4 NOT NULL,
    "name" text NOT NULL,
    genre text NOT NULL,
    seasons int4 NULL DEFAULT 1,
    is_running bool NULL DEFAULT true,
    release_year int4 NULL,
    CONSTRAINT web_series_pkey PRIMARY KEY (id)
);

-- Seed web_series table with sample data
INSERT INTO public.web_series (name, genre, seasons, is_running, release_year) VALUES
('Stranger Friends', 'Drama', 3, true, 2019),
('Coder Life', 'Comedy', 2, true, 2020),
('Dark Web', 'Thriller', 4, false, 2018),
('Cooking Masters', 'Reality', 5, true, 2015),
('Treasure Hunt', 'Adventure', 1, false, 2021);

-- query new table
SELECT * FROM web_series;

Common Flags and Usage Patterns

• UNION vs UNION ALL: By default, UNION removes duplicate rows. If you want to keep them, use UNION ALL.

  SELECT name FROM tv_series WHERE genre = 'Drama'
  UNION ALL
  SELECT name FROM web_series WHERE genre = 'Drama';

• Ordering Results: Use ORDER BY at the end to sort the final result set.

  SELECT name FROM tv_series WHERE genre = 'Comedy'
  UNION
  SELECT name FROM web_series WHERE genre = 'Comedy'
  ORDER BY name ASC;

Common Errors and Solutions

Error Message:

ERROR:  each UNION query must have the same number of columns

Cause:
This error occurs when the SELECT queries combined using UNION don’t have the same number of columns.

Solution:
Make sure that each SELECT statement within the UNION has the same number of columns, and those columns are of compatible data types.

Pro Tips

• Use UNION ALL when you’re sure that the queries won’t have duplicate rows or when duplicates are acceptable. It’s faster because it doesn’t have to check for duplicates.

• Be mindful of the data types for each column in the SELECT queries. They must be compatible for the UNION to work.