Sql-server – SQL Server JOIN not working

join;sql server

I am not sure why the below join query is not returning any results.

SELECT * FROM table1 JOIN table2  ON table1.fieldid = table2.idfield

The below 2 queries return results so I am not sure why the join wont pick up any rows when i can clearly see that both tables have join field values in common.

SELECT *  FROM table1 WHERE fieldid =  900399530  (returns rows)
SELECT *  FROM table2 WHERE idfield =  900399530  (returns rows)

Any ideas?

Below are the data types for the 2 columns:

fieldid [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

idfield [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

Best Answer

As far as it's a CHAR/VARCHAR/NVARCHAR field you should use single quotes:

Maybe there is some extra or leading space.

Try it on this way:

SELECT *  FROM table1 WHERE fieldid =  '900399530';
SELECT *  FROM table2 WHERE idfield =  '900399530';

or remove additional spaces by using TRIM() function.

SELECT * FROM table1 JOIN table2  ON TRIM(table1.fieldid) = TRIM(table2.idfield);

For versions below 2017:

SELECT *
FROM   table1
JOIN   table2
ON     LTRIM(RTRIM(table1.fieldid)) = LTRIM(RTRIM(table2.idfield));
Related Question