SQL Server – Optimize a Query with Unexpected Results

I do not post often in this SE, so if I do not explain the request correctly, thank you for letting me know and I will edit it.

I have to conceal some of the work I do to try to explain my problem in generic terms and hope that I can get some answers on what to investigate.

I have a big table with a variety of columns. But for the sake of this question, let's just say that it's my table and has columns RecordID and Category

If I do:

SELECT *
Of the table

This request will take very, very long to run under several hours.

But if I do something like

SELECT *
Of the table
O Category = 1

This query will run very quickly. In fact, assuming I only have 2 categories, I can do something like

SELECT *
Of the table
O Category = 1
UNION ALL
SELECT *
Of the table
O Category = 2

And this query will run in a few minutes and will always contain all the records that the first query should have extracted (again, assuming only 2 categories).

It should be noted that the following query does not show any improvement over the benchmark, it is only the UNION statement that corrects it.

SELECT *
Of the table
O Category IN (1,2)

It's counterintuitive to me that a basic query can be slower than a regular one. UNION two requests. Why is that? For me, this goes beyond the usual issues related to indexing: if the indexing was not correct, none of the queries presented would work properly. So, I really do not know what I should look for.

Is this a symptom that seems familiar to anyone and who can point me in the right direction for further research?