In principle using the Google (or any other) CDN makes sense for a standalone site or application. However, WordPress is an open source application, and there are thousands of developers creating plugins and themes for WordPress, all in their own way.
Say I develop a fabulous plugin, and it needs the latest and greatest jQuery version, so I make sure that I include that from Google or jQuery CDN. Unfortunately, the latest and greatest today is old and outdated “tomorrow”, and definitely next year. WordPress ships “a” version of jQuery, and it is good practise to use that version, so that all themes and plugins use the same resources. One year from now, jQuery has without doubt been updated, WordPress has been updated, and because my plugin is including the now one year old “latest and greatest”, other new plugins and themes, or those plugins and themes where developers made sure to stay up to date with WordPress will start to fail, or conflict. My plugin is in fact creating these issues.
Of course, in an ideal world, and if this happens, every(!) developer should update his plugin to stay on par with WordPress, and ALL users should update all their plugins, but in that case, you might as well use the WordPress supplied version. The price you pay to load jQuery from your own WordPress install will definitely make up for the mess that is created in the situation I mentioned before.
So in a way, including a jQuery version that is not the same as the version used by a specific WordPress version, is like modifying core WordPress files.
As moderator of a support forum, I receive (almost daily) reports of failing plugins due to the above.