Motivations
I have two array values like below now.
If there are some values that are not registered in my database, I want to know them.
Arrays
Array1
[1, 2, 30, 100]
Array2
['hello', 'world', 'sample']
[Database] sample_tables
| id | name |
|---|---|
| 1 | hello world |
| 2 | good morning |
Getting existed values
I can get values that are registered in the database.
Array1
SELECT * FROM sample_tables WHERE id IN (1, 2, 30, 100);
Results1
1
2
Array2
SELECT * FROM sample_tables WHERE REGEXP_LIKE(name, '(hello)|(world)|(sample)')
Results2
hello world
Getting not existed values
Conversely, how can I get the values that are not registered in the database?
I can use subquery to do that.
Array1
SELECT t1.keyword FROM (SELECT UNNEST(ARRAY[1, 2, 30, 100]) as keyword) as t1
LEFT JOIN sample_tables as t2 ON t2.id = t1.keyword
WHERE t2.ID IS NULL;
Result1
30
100
Array2
SELECT t1.keyword FROM (SELECT UNNEST(ARRAY['hello', 'world', 'sample']) as keyword) as t1
LEFT JOIN sample_tables as t2 ON t2.name LIKE '%' || t1.keyword || '%'
WHERE t2.ID IS NULL;
Results2
sample
Top comments (0)