What is best way to compare int and varchar fields in mysql?

December 9, 2022

I have a two large tables.

In the first table have indexed id field with type int.
In the second table have indexed classId field with type varchar(50).
I need to get all fields from first table which do not have classId in the second.

select 
    a.id, a.type, a.path, a.filename
from 
    assets a
where 
    a.type="folder" and 
    concat_ws("", a.path, a.filename) like "/product%" AND
    a.id not in (
        select 
           classId 
        from 
           gridconfigs g 
        where 
           g.type='asset' and
           g.name='Photo Attributes'
    );

I am trying to do it in that way but request is executing very slowly.

Any idea?

>Solution :

Use Not exists… or if you need data from gridconfigs use a left join where g.classID is null

SELECT * 
FROM assets A 
WHERE NOT EXISTS 
      (SELECT 1 
       FROM gridconfigs G 
       WHERE A.ID = G.CLASSID 
         and G.type = 'asset' 
         and G.name = 'Photo Attributes')

As to why: Not exist and not in operate differently in the presence of NULL

Not exists can early exit once a single occurrence is found, in doesn’t.

Read up: