6

The problem arise when there are no data for books in specific library. Consider a following working scenario.

Table library

--------------------------------
| id |    name     |    owner  |
--------------------------------
|  1 |     ABC     |     A     |
|  2 |     DEF     |     D     |
|  3 |     GHI     |     G     |
--------------------------------

Table books

--------------------------------
| id |    title    |  library  |
--------------------------------
|  a |     xxx     |     1     |
|  b |     yyy     |     1     |
|  c |     zzz     |     2     |
--------------------------------

Now when I do query like below:

SELECT library.name, array_agg(b.title) AS book_list FROM library, 
(SELECT title FROM books WHERE books.library = :library_no) as b 
WHERE library.id = :library_no GROUP BY library.id

The query generates output for library 1 & 2, but not for library 3. Why and how to solve this issue? (Generate an empty list on no library books)

Required Output:

----------------------
| name |    book_list |
----------------------
|  GHI |      {}      |   # or {null}
-----------------------

I've even tried coalesce as below:

SELECT library.name, coalesce(array_agg(b.title), ARRAY[]::VARCHAR[]) AS book_list FROM library, 
(SELECT title FROM books WHERE books.library = :library_no) as b 
WHERE library.id = :library_no GROUP BY library.id

Postgres version: 12

Erwin Brandstetter
  • 175,982
  • 27
  • 439
  • 600
PaxPrz
  • 219
  • 1
  • 10

2 Answers2

10

A LEFT JOIN can solve it, like Laurenz provided.

But I suggest an ARRAY constructor in a LATERAL subquery instead:

SELECT l.name, b.book_list
FROM   library l
CROSS  JOIN LATERAL (
   SELECT ARRAY(
      SELECT title
      FROM   books
      WHERE  library = l.id
      )
   ) b(book_list)
WHERE  l.id = :library_no;

This way, you don't need to aggregate in the outer query level and don't need to GROUP BY there.

You also don't need COALESCE, since the ARRAY constructor over an empty result already produces an empty array ({}).

And it should be faster for a small selection in library - obviously the query gets the result for a single given library.

Aside, you only need the variable :library_no in a single place like demonstrated.

About LATERAL joins:

About the ARRAY constructor:

Basic about joining tables in the manual.

Erwin Brandstetter
  • 175,982
  • 27
  • 439
  • 600
  • I dont know about LATERAL and CROSS joins. Could you provide me a good link for beginner to learn about these? – PaxPrz Jan 14 '21 at 05:14
  • Thank you @Erwin :) – PaxPrz Jan 14 '21 at 05:19
  • 1
    @Pax: Basics for LATERAL with links to more: https://stackoverflow.com/a/28557803/939860. And CROSS JOIN is the simplest way to join. It's an unconditional join: all rows on the left are combined with all rows on the right. Since there is exactly 1 row on the right in the example, that's all we need. – Erwin Brandstetter Jan 14 '21 at 05:23
5

You need a left join:

... FROM library
       LEFT JOIN books ON library.id = books.library

Then you will also get libraries with no books.

Laurenz Albe
  • 51,298
  • 4
  • 39
  • 69