Why does SQL Server say it can’t convert varchar to numeric?


I have the following schema/data in MSSQL Server 2019 (SQLFiddle):

CREATE TABLE products(     idn NUMERIC(9) PRIMARY KEY );  CREATE TABLE sales(     idn NUMERIC(9) PRIMARY KEY,     pid VARCHAR(50) NOT NULL,     type VARCHAR(10) NOT NULL );  INSERT INTO products (idn) VALUES (1); INSERT INTO sales (idn, pid, type) VALUES (1, 1, 'number'); INSERT INTO sales (idn, pid, type) VALUES (2, 'Colgate', 'word'); 

sales has mixed data i.e VARCHAR and NUMERIC. The transaction filter takes care of JOINing correctly.

Why does the following SELECT fail?

SELECT      *  FROM      products      INNER JOIN sales ON products.idn = sales.pid      AND sales.type = N'number'  WHERE      products.idn in (1);  

I don’t see why casting NVARCHAR to NUMERIC is an issue:

SELECT CAST (N'1' as NUMERIC); 

If I modify the query slightly it works:

SELECT      *  FROM      products      INNER JOIN sales ON products.idn = sales.pid      AND sales.type = N 'number'  WHERE     -- Selecting the same data from `sales`.     sales.pid in (1);  SELECT      *  FROM      products      INNER JOIN sales ON products.idn = sales.pid     -- Dropping the `N` prefix.     AND sales.type = 'number'  WHERE      products.idn in (1);