I imagine the existence of those means that CD drives had their own DAC and other logic. I guess there was an idea of wanting to play CD audio without it being a PC concern? Or on PCs without audio capability?
Unfortunately, a decent number of machines were not fitted with the relevant cable. Combined with the low-quality DACs that most drives used, the compatibility issues that plagued ATAPI implementations and the dramatic increase in CPU power and sound card quality throughout the mid-to-late 90s, this led media player software to quickly move on from drive based playback to so-called "digital audio extraction", where the CD is basically ripped in real time and streamed to your sound card's own DAC. Thus, unless you played older games that relied on hardware CD-DA playback [1], it's somewhat unlikely you ever experienced it under, say, Windows 98 or XP.
[1] As offloading playback to the drive had no CPU overhead, games often stored their music as additional tracks on the game disc and played it that way. Incidentally, basically all CD-ROM-based game consoles and arcade systems relied heavily on hardware accelerated playback as well, with some going even further and allowing for compressed (ADPCM) CD audio streaming with no CPU intervention.