What the difference between using a CNODE and two nodes being connected by a rigid element with zero weight?

The two nodes associated with the CNODE construct occupy the same location (coordinates) in space. Your rigid element would have to be "zero length" to accomplish the same thing.

In addition, with a CNODE you can address any one, or several of the six degrees of freedom. Connecting two nodes with a rigid element essentially associates all six degrees of freedom.

Think of a CNODE construct as a ball-and-socket type assembly. An example would be your arm and shoulder. This is an XYZ CNODE, where the translations are associated but not the rotations.