You wrote that you'd like to do this without creating additional tables if possible. Where would the numbers come from if you don't list them in a table or similar?
What comes to the query itself, it can be done in multiple ways. Personally I would perhaps prefer the following format
SELECT a.TestNumber,
COALESCE( (SELECT TOP 1
1
FROM DataTable b
WHERE b.Number = a.TestNumber), 0) AS Exists
FROM NumberToTestTable a
The query above should prevent multiplication of rows in case the number exists several times in the data table. Also
TOP 1
structure should end the investigation as soon as a match is found.
From the performance point of view, it would be critical to index the
Number
column in the
DataTable
in order to efficiently scan for the data.