Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE facts_themes(id int, categories_a text[], categories_b text[]);

INSERT INTO facts_themes VALUES
  (1, '{"A"}', '{"B","F","C"}')
, (2, '{"B","A"}', '{"Z","B","F"}')
, (3, '{"B","F"}', '{"A","E","R"}')
-- added cases
, (4, '{"B","B"}', '{"A","B","R"}')
, (5, '{null,"B"}', '{null,"B","R"}')
, (6, '{null,"B"}', '{"X","B","R"}')
, (7, '{null, null,"B"}', '{null,"B","R"}')
;
7 rows affected

SELECT *, ARRAY(SELECT unnest(categories_a) EXCEPT ALL SELECT unnest(categories_b)) AS missing
FROM   facts_themes;


id
categories_a
categories_b
missing


1
{A}
{B,F,C}
{A}

2
{B,A}
{Z,B,F}
{A}

3
{B,F}
{A,E,R}
{B,F}

4
{B,B}
{A,B,R}
{B}

5
{NULL,B}
{NULL,B,R}
{}

6
{NULL,B}
{X,B,R}
{NULL}

7
{NULL,NULL,B}
{NULL,B,R}
{NULL}


SELECT *, ARRAY(SELECT unnest(categories_a) EXCEPT  SELECT unnest(categories_b)) AS missing
FROM   facts_themes;


id
categories_a
categories_b
missing


1
{A}
{B,F,C}
{A}

2
{B,A}
{Z,B,F}
{A}

3
{B,F}
{A,E,R}
{B,F}

4
{B,B}
{A,B,R}
{}

5
{NULL,B}
{NULL,B,R}
{}

6
{NULL,B}
{X,B,R}
{NULL}

7
{NULL,NULL,B}
{NULL,B,R}
{}

id	categories_a	categories_b	missing
1	{A}	{B,F,C}	{A}
2	{B,A}	{Z,B,F}	{A}
3	{B,F}	{A,E,R}	{B,F}
4	{B,B}	{A,B,R}	{B}
5	{NULL,B}	{NULL,B,R}	{}
6	{NULL,B}	{X,B,R}	{NULL}
7	{NULL,NULL,B}	{NULL,B,R}	{NULL}