The best cold air intakes out there are the C&L Racer and JLT, IMO.. The C&L is definitely a higher quality and costs more to manufacture so realize that when you are shopping.
Now as far as tuning goes... If the cold air intake changes the MAF housing's size than it needs a tune. If you increase the size of the MAF tube and do not tune for it than you will run leaner. It can create a CEL but know that some times it does not. This doesn't mean you're not running lean. The C&L Racer uses a 95mm MAF tube which is much larger than stock so it needs a tune. Any cold air intake out on the market that gains any power is going to have a larger than stock MAF tube. So if you're going to spend the money on a cold air intake than you're going to need to spend the money on a programmer.
If a company advertises no tune than be skeptical. This could mean anything from that it comes with an insert that uses the stock MAF tube size or it just means the company is interesting in nothing more than a sale and doesn't care about the well being of your car. Again, running a larger MAF tube without tuning will absolutely cause the motor to run leaner. Running lean will cause detonation and like any motor, especially modular motors, detonation is what destroys motors.
As far as power, my car made 290 rwhp with a C&L Racer and VMP 93 octane mail order tune. This is a gain of about 25 rwhp but it does make a big difference in the way the car drove and feel. It feels like a Mustang! It's also worth noting that VMP Tuning dyno tuned my car after and it only made approximately 3 rwhp more than the mail order tune. Just goes to show you how well the reputable tuners can tune for these mods without ever strapping the car to a dyno.
I hope this helps everyone who is curious about this issue.