CODEWITHSUNDEEP
sqlpostgresql
porgrammer3124
porgrammer3124

Reputation: 85

postgres filter column if name not in another table

I am new to postgreSQL. I have two tables main, secondary each with 1 column

main

data
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
...

secondary

name
james
john

expected output:

data
[email protected]
[email protected]

I want to filter main (10 mil rows) using the names in secondary (40k rows), if the name is not in data (case insensitive).

Note: both tables have unique data

Upvotes: 1

Views: 758

Answers (2)

user330315
user330315

Reputation:

I would use a not exists condition:

select *
from main m
where not exists (select * 
                  from secondary s 
                  where m.data ilike '%'||s.name||'%');

Upvotes: 1

crvv
crvv

Reputation: 610

SELECT data
FROM main
LEFT JOIN secondary ON strpos(lower(main.data), lower(secondary.name)) > 0
WHERE secondary.name IS NULL

Upvotes: 1

Related Questions