At this point, it sounds like IndexedDB will be more highly adopted then WebSQL.

Mark West did a nice overview. I summarized a his comparison slides below:

WebSQL
• A real, relational db implementation on the client (SQLite)
• Data can be highly structured, and JOIN enables quick, ad-hoc access
• Big conceptual overhead (SQL)
• Not very JavaScripty, browser support is poor, and the spec has been more or less abandoned.

IndexedDB
• Sits between full-on SQL and unstructured key-value pairs in “localStorage”
• Values are stored as structured JavaScript objects, and an indexing system facilitates filtering and lookup.
• Asynchronous, with moderately granular locking
• Joining normalized data is a completely manual process

“The working group is designing this API so it could be wrapped by JavaScript libraries; for instance, there’s plenty of room for a CouchDB-style API on top of our IndexedDB implementation. It would also be very possible to build a SQL-based API on top of IndexedDB (such as WebDatabase).” – Arun Ranganathan, Firefox Developer

Code Comparisons: http://hacks.mozilla.org/2010/06/comparing-indexeddb-and-webdatabase/.