Hi and welcome!
1. It depends on the situation.
2. Best is a custom loop with copper radiators and a proper pump. There are some semi custom, open loop coolers available with copper rads and better quality pumps which sit as a half step down to the aluminium radiators with cheaper pumps of the normal CLC's you'll find on the market from Corsair, NZXT et al.
3. For RGB and ease of installation you're better off with an closed loop water cooler(CLC) for best visual effect.
If you go AIO get at very least a 240mm model, anything smaller is not worth it unless you are going for a compact ITX build when compared to air coolers.
Dual tower air coolers like the Noctua NHD-15 will perform around the same as a 240mm CLC for the most part.
CLC's are much more compatible with large RAM heat spreaders and slimmer cases, they also make it easier to work in your system. They will need to be replaced eventually as the liquid will slowly evaporate over time, and the pump will give out one day. A large air cooler is hard to work around and less compatible. However air coolers will last literally forever, and have zero chance of leaking.
Depending on the workload, any overclocking, voltage used, ambient room temperatures, airflow in case etc etc, a 240mm CLC or large dual tower air cooler should be enough for anything on the market. Personally I'd prefer to go with a 280mm cooler to be on the safe side if I was going to be upgrading to an 11700K/Ryzen 5900 or above. Below that 240mm/large air should be sufficient for anything.
Your 6700 will be easily cooled at stock by pretty much any air cooler or CLC.
If you still would like to go with an air cooler, the NHD-15S has no front fan for RAM compatibility and the heatsink is offset to allow for more space between it and your GPU. You can add a fan if you like for a couple degrees extra cooling, it comes with clips but you'd have to buy an extra fan.
PCPartPicker Part List
CPU Cooler: Noctua NH-D15S 82.52 CFM CPU Cooler ($89.95 @ Amazon)
Total: $89.95
Prices include shipping, taxes, and discounts when available
Generated by PCPartPicker 2021-04-11 13:14 EDT-0400
Other dual towers are available and just as good, but this is the most compatible, there's a Chromax black edition thats hard to find at the moment that looks better if you could find it..
I'm not to up on the best CLC models to choose these days as they iterate all the time on very similar designs, in fact many are made by the same couple of OEMs and rebranded by companies like Corsair. However I do know this one reviewed well at Gamers Nexus and has plenty of RGB for you.
PCPartPicker Part List
CPU Cooler: Corsair H115i RGB PLATINUM 97 CFM Liquid CPU Cooler ($129.99 @ Corsair)
Total: $129.99
Prices include shipping, taxes, and discounts when available
Generated by PCPartPicker 2021-04-11 13:50 EDT-0400
If you wont overclock anything to the bleeding edge and you don't want an air cooler, a 240mm CLC should be sufficient for anything on the market:
PCPartPicker Part List
CPU Cooler: Deepcool GAMERSTORM CAPTAIN 240PRO V2 69.34 CFM Liquid CPU Cooler ($105.99 @ Newegg)
Total: $105.99
Prices include shipping, taxes, and discounts when available
Generated by PCPartPicker 2021-04-11 14:10 EDT-0400
This one reviews well enough for performance, although I have to say I know absolutely nothing about RGB controls and the like. No judgement intended just not my bag.
If you have more questions let me know, I do go on sometimes