Morrowind came out six years after prequel Daggerfall, and greatly benefited from improvements in technology and game design/programming know-how. Technology at this point was much more suited to the huge open-world vision of the original two games, but Bethesda also made the decision here to scale back the size of the game world somewhat. Instead of heaping masses of procedurally generated empty terrain that served no purpose and largely never even got visited, Morrowind instead gives you a smaller but more intricately hand-crafted world, way more walkable, and with little things tucked away for you to uncover literally everywhere on the map.
That's one main key to why the game leapt in quality so much, and became "mainstream" with this installment. The other is Bethesda's policy of open moddability, making things as easy as possible with an official Construction Kit (packed in with the GOTY edition.) Turned out to be a very wise decision, as Morrowind does end up having a few serious shortcomings ... but the fan community, which has thousands of mods now and is still going strong almost ten years after release, has addressed pretty much all of them on their own. Once properly patched and modded out, Morrowind leaves you with little to complain about.
The game gets off to a good start by not dumping you into some asshole dungeon right off the bat like the first two games did. You are sprung from prison to start the game, but that's handled in a cut-scene, and the actual gameplay starts you off free as a bird in a little seaside village. The "main story" of the game starts you out with instructions to deliver a package to a man named Caius in a nearby city called Balmora, but now as through the rest of the game, you're free to ignore the main story for as long as you like and wander around doing whatever.
One issue with Morrowind is that it is tough compared to sequels Oblivion and Skyrim, as enemies are largely not tied to your current level. No matter how you roll your character, you're pretty much a joke in combat at the start of the game. It's recommended that you actually follow the main quest for a little while, as the game provides you a fairly easy path to Balmora, and once there a number of fairly easy quests to help build up and become more suited to freely roaming the map. I didn't really have a problem with the vanilla difficulty, but as with most of the game's other issues, I suppose you could install a re-balancing mod, or just use the in-game cheat console to give yourself a little boost in stats if you find the outset too hard. Really, the difficulty is all front-loaded into the first ten hours or so of gameplay; once you've leveled your character to around 15 or so and got their important core skills up to level 50-ish and have a good set of equipment and suite of spells or enchanted items on hand, I felt like the game actually became way too easy, with no foes that I could find from there on presenting much of a challenge.
This does bring up an interesting point about Morrowind's leveling system, which is unique. Instead of gaining generic EXP that goes toward a level boost that raises all your stats, you level skills by using them. If you've played any Quest For Glory games, it's just like that. But you do have an overall "level" that determines your core statistics - strength, endurance, etc. When the game starts out you pick five Major Skills and five Minor Skills to emphasize. Aside from getting starting boosts, these skills will also determine your overall level. Every time you cumulatively raise your major and minor skills by ten levels, you go up an "overall" level as well. So there's nothing stopping you from, say, making Athletics one of your major skills, and then just gaining levels toward your overall level as you run around. In fact, if you were really determined, you could make Acrobatics (which is simply raised by jumping) one of your major skills, put a small weight of some sort on the jump button, and then just wander off for a couple hours and come back to find some grotesquely over-leveled character. Aside from the fact that this is a lame way to play, it's somewhat counterbalanced by the fact that your other important skills (combat, armor use, magic, etc.) still have to be organically leveled by using them in the field. A super-strong warrior is no good if he whiffs on every swing and he gets no armor bonus from wearing anything.
This brings up another point - though the game looks FPS-style, and technically has action-oriented combat rather than turn-based, everything is still influenced by invisible dice rolls in the background. In the beginning of the game, monsters will be right in your face, yet most of your swings will go right through them. This can be immensely frustrating to watch, and puts some people off to the game immediately. The workaround is simply to raise the characters starting stats a bit, either via mod or console, so they at least consistently land hits with your weapon of choice (bringing that weapon's skill to 40 or so should do it.)
The last thing that might put people off at the outset is movement speed. The speed at which you walk and run is tied directly to your Speed stat and your Athletics skills, both of which are usually balls at the beginning. Walking is painfully slow, running is pretty much mandatory to move about the game world. But running drains your "fatigue" bar. There's no real penalty for running around when out of fatigue when in a town, but if you have to fight something, having low fatigue lowers all your combat skills significantly. Again, modding or console tweaking can fix this if you find it intolerable.
The NPCs aren't as generic and purposeless as they were in previous games, but characterization and narrative is still the weak point here. All game text is delivered to you in the most boring and old-school of plaintext, and all the game's characters draw from some generic pool of responses to the topics you ask them about. The world still doesn't feel "alive" or "real" in terms of population, but it is a bit of a step forward from the prequels.
Since the overall story is kinda boring and the characters are generic text-spouting lumps that mostly just stand in one place 24 hours a day, the focus here is mostly on exploring. And that's where the game is strongest. The game sports an entirely contiguous overworld map, and I think it's the only one I've ever seen so far that does this; rather than loading towns separately, they're simply connected to the rest of the world map. Meaning you can train monsters into the village square and then watch guards surround them for the lulz. The only time the game loads a new area is if you go into an indoor area - a house, a castle, a cave, etc. Though the graphics are dated, overall the architecture of the whole world is very impressive. The castles and towns have a realistic-seeming structure to them, and there are all sorts of pretty vistas to be stumbled upon (particularly with modern graphics mods installed.) Strike out from any random direction from a village, and it won't be more than a minute or two before you stumble across something interesting to explore - massive dwarven ruins with inexplicable technology still running, creepy evil shrines, bandit caves, shipwrecks, or just random people hanging out in random places waiting to ask for your help in a quest. Though there's no horsie to ride around in this one, you do get a plethora of tools to assist your world travels - you can magically levitate, walk on water, or swim underwater (complete with a spell or potion to let you breathe underwater). There's also a bunch of options for quick travel between towns - Mages Guilds will teleport you between their locations, there's a Silt Strider service (a giant walking bug you ride) that zips you between most of the game's larger cities and towns for a small fee, and boat people here and there on the coasts that ferry you automatically between the smaller coastal villages. You can also get a mark/recall spell allowing you to set teleport points, and there's a spell that will zap you back to the nearest temple as well. Eventually you even find a series of ancient ruins that are connected by their own teleportation system.
Also interesting is the setting. Morrowind is TES's continent of Elves, but they don't take the stock Tolkien approach to the race. They aren't all kind, wise, jumpy and living in deep forests. In fact, much of Morrowind is more swampy, with giant mushrooms more common than pine trees. The Elves are also a mixed bag. There's several different factions with different appearances. Like humans, they're all over the place in talents and abilities, some are racists, some are clumsy dumbasses. The Empire is occupying the land, so you have a mixed bag of Imperial settlements and native Elf villages, and the main theme of the game is the most hardcore of the natives' desire to drive the Empire from their land.
The game also doesn't put any constrictions on you in terms of the "main quest" or overall time. You can spend as much time as you want goofing off without aging or anything failing. There's no Time Limit Of Impending Doom hanging over your head whatsoever. And when you finish the main storyline, the game keeps going, you're free to keep exploring the world and doing whatever, and adding mods that add new content to take your character through. And if you have the yen to do quests but get tired of the regular story, there's shit tons of factions to align yourself with. There's the usual Guilds - Fighters, Thieves and Mages. Then there's the Temple, the native religion "good guys" of the game. There's also the Imperial Legion, the army of the occupying forces of Morrowind, and the Imperial Cult, their religious branch. Then you have the three Great Houses, of which you can only pick one per game. Later in the game you can join the Morag Tong, the legalized ninjer contract killers of the Morrowind world. There's probably even some other ones I'm forgetting about. The best part about it is that you're totally free to ignore any or all of these if you care to, and there's still more than enough things to do and ways to build your character up. For example, in my game I was playing a freebooting rogue sort, and has absolutely no desire to get involved with the Legion or anything military. So I never even talked to them or the cult. No harm done whatsoever. I joined the Temple just to get a free bed in every town at the outset, then merrily ignored their first quest, a giant pilgrimage to shrines all over the world. I had no interest in joining any of the Houses either, since I was having too much fun breaking into their manors and stealing from them. I was doing quests for the Fighters, Thieves and Mages Guilds simultaneously, but eventually they started giving me quests that conflicted with each other (the Fighters Guild wanted me to assasinate a Thieves Guild member; the Thieves Guild wanted me to steal from the Mage's Guild.) Since I didn't want to get in bad with any of the Guilds and lose access to their resources, I just stopped doing all their quests altogether. No harm, no foul, and there was still a shit ton of other (more interesting) things to do in the game.
In the end I felt like a lot of the appeal of Morrowind was actually somewhat analagous to "sequence-breaking" in Metroid; you come across some place you aren't "supposed" to be ready for yet, or some quest that it's too "early" to undertake, but then between the game's open-ended nature/wealth of options and your own cleverness, you come up with a way to do it anyway. Feels good, man. The two sequels did a number of things better, particularly interpersonal interaction and making the world feel more like its actually populated with sentient creatures, but there's something to be said for the freedom and open exploration of this one that was never quite recaptured. I guess the main question here is, "should I buy/play this with Oblivion and Skyrim available?" I'd have to say yes, because it offers something of a different experience, even if the later games do a number of things better. The "vanilla" version of the game by itself also offers you north of 100 hours of content, then there's the Bloodmoon and Tribunal expansions piling on even more, and finally the eight years worth of work by the modding community. If you really end up getting into the game, it could keep you busy for years.