select * from product where productid in (select productid from sales where salesdate='2020-01-22')
Which isn't the same as what you first posted because of the absence of table qualifications. That single difference, explained by Tom, completely explains why the original post did in fact provoke and error and this query does not.
The take-away is make sure to test your queries; and when dealing with sub-queries its usually a good idea of add a table qualification to your fields.
