In my opinion
Always use unique property for the field you want to be, actually - unique !
If by any means you can not do so, and also want to know before hand if the desired value already exists in the table / collection then additionally use if-Exists functionality.
Why not any one?
May be because improper sharding key in mongodb allows non-unique value in each shards!
Cannot offer knowledge in SQL but I think methodology goes same - use unique indexing where possible.
Cost effectiveness
Both methods cost server resources and hits the db server minimum twice.
So what's the big deal?
In the unknown universe -- you sent a request to know if a value exists and response was false to let you know the uniqueness! By the time you recognize it in the application server and requests the insertion may be someone else has already inserted the same value, by any chance in a busy server!
The server may never bother telling you the discrepancy as the indexing was unavailable!
For this point of view if - uniqueness is that important then you should enable indexing in table or collection level first!