SQL Server 2017
SSMS 18.12.1

I expect a result query with a scalar 5.

But I got error message 245

Conversion failed when converting the varchar value ‘FPO’ to data type tinyint

What am I doing wrong?

CREATE TABLE TabA(aNumVal TINYINT NOT NULL)

INSERT INTO TabA (aNumVal) VALUES(5)
INSERT INTO TabA (aNumVal) VALUES(2)
    
CREATE TABLE TabB(aCharVal VARCHAR(3) NOT NULL)

INSERT INTO TabB (aCharVal) VALUES('FPO')
INSERT INTO TabB (aCharVal) VALUES('2')

SELECT aNumVal
FROM TabA
WHERE aNumVal NOT IN (SELECT aCharVal FROM TabB 
                      WHERE ISNUMERIC(aCharVal) = 1)

>Solution :

SQL server will perform type conversion when comparing data. VARCHAR is converted to INT. There is absolutely no guarantee at which stage it is done. It could be done before evaluating WHERE clause if the server decides so.

The possible work around is to use TRY_CAST:

WHERE aNumVal NOT IN (
    SELECT TRY_CAST(aCharVal AS INT)
    FROM TabB
    WHERE TRY_CAST(aCharVal AS INT) IS NOT NULL
)

The IS NOT NULL is required for NOT IN checks.