naiveai naiveai - 6 months ago 123
SQL Question

SQL: Check if at least one element of an array is in subquery

I have a table like this:

id | name | artists
-------------------
1 | XYZ | {Some Dude, Whatever}
2 | ABC | {Blah Blah Blah, Whatever}
3 | EFG | {Running, Out, Of, Made, Up, Names}


I have a subquery that returns one column called
name
with a bunch of artist's names. I need a way to check if at least one of the elements of
artists
(for each of the rows) is contained in the results of that subquery. That is, if the subquery returns this:

name
----
Some Dude
Whatever
Blah Blah Blah


then, I want to select only the rows with id 1 and 2 in my example, because none of the artists in id 3 are returned by the subquery.

I do know I can do
single_element = ANY(subquery)
but that only tests a single element. I've tried doing:

SELECT * FROM table WHERE ANY(artists) = ANY(subquery)


but that fails immediately with "ERROR: syntax error at or near 'any'".

Thanks in advance!

Answer Source

You can use the && operator to test for set-element overlap. Its documented in the postgresql documentation section on array functions

WITH artists (name) AS (
VALUES 
    ('Blah Blah Blah'::text), 
    ('Whatever'), 
    ('Some Dude')
),
my_table (id, name, artists) AS (
VALUES 
    (1,'XYZ',ARRAY['Some Dude'::TEXT, 'Whatever'::TEXT]),
    (2,'ABC',ARRAY['Blah Blah Blah', 'Whatever']),
    (3,'EFG',ARRAY['Running', 'Out', 'Of', 'Made', 'Up', 'Names'])
)
SELECT * 
FROM my_table 
WHERE artists && (SELECT ARRAY_AGG(name) FROM artists)

Also, from my example above you can see how to convert a subquery into an array to be able to use the overlaps operator

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download