CODEWITHSUNDEEP
mysqlsql
Francesco
Francesco

Reputation: 31

Mysql Select records that not contain records of second table

I have 2 MySQL tables with 1 column and a lot of records. I should select records of the first table that doesn't CONTAIN rows of second table. Can anyone help me with a query?

Table 1:
http://www.google.com
http://www.aaaa.com
http://bbbb.com
http://www.cccc.com
http://www.dddd.com

Table 2:
tttt.com
aaaa.com
google.com
rrrr.com
dddd.com

Result should to be:
http://bbbb.com
http://www.cccc.com

Upvotes: 1

Views: 52

Answers (2)

Francesco
Francesco

Reputation: 31

Works!! A question: why the last row "where t2.col is null" ?

Regards

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You can do this with a left join and like:

select t1.*
from table1 t1 left join
     table2 t2
     on t1.col like concat('%', t2.col, '%')
where t2.col is null;

Unfortunately, you cannot use indexes to optimize this query, so the engine will have to do a nested loop join.

Upvotes: 2

Related Questions