What is this rotating pipe adapter called
The fitting on the right side of the assembly in your picture is a union, it lets you make a threaded connection without spinning the whole assembly.  It's not just handy, it's essential because it lets you make a threaded connection where you cut into an existing system or when you're making complex assemblies that can't be built by spinning the pieces on sequentially one by one.