jQuery is the core library. jQueryUI is built on top of it. If you use jQueryUI, you must also include jQuery.
jQuery Tabs preceded jQueryUI library. jQueryUI Tabs is based on jQuery Tabs. The current version of jQuery Tabs is Tabs 3. If I recall correctly (I vaguely remember looking through the source code about a year ago), Tabs 3 is very similar to what's in jQueryUI. The jQueryUI version, of course, respects the Themeroller themes.
I used jQueryUI in one project. It has some nice features, but there are few widgets and it seems to be advancing at a glacial pace (seems like the same six widgets have been there for over a year). Maybe it'll get a second wind. If I recall, one of the things that bothered me is that in many of the widgets, there was no visible difference between the hover state and the click state.
So I would not recommend jQueryUI at this time for its widgets. You may want it for the effects and the draggable/dropable interactions handling.
The widgets in Google's Closure library and the widgets in ExtJS seem more useful, in my opinion.