CODEWITHSUNDEEP
sqlpostgresqlpostgresql-9.6
naiveai
naiveai

Reputation: 853

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!

Upvotes: 2

Views: 2608

Answers (1)

Haleemur Ali
Haleemur Ali

Reputation: 28273

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

Upvotes: 3

Related Questions