The Red Door is a historic landmark - so much so the whole building has literally been picked up and moved to make room for newer, swankier, Fremont "improvements" *twice* in its hundred-year-plus history. In the summer of 2005, the addition of a new partially-covered, heated(!) outdoor bar on the big patio made it one of the most popular places to meet friends, see and be seen on Thu-Fri-Sat nights in Fremont. It's also a great place to have lunch on weekends, where you can enjoy the weather, a drink or two, and partake in some fascinating people- (and dog-) watching overlooking the popular Fremont Sunday Market.
With the smoking ban now in effect, the Red Door has changed a fair bit on the inside too - it's much less crowded on weeknights, and is somewhat more tolerable on the crowded cold weekends, when it regularly becomes an annoyingly just-out-of-the-fraternity party- and pickup- bar for people from far-off lands like UDistrict, Bellevue and Renton.
The bartenders are uniformly excellent - often, and dependably, relied on by regulars to mix up a custom "something good" - and there is a great variety of microbrews on tap, often with tasty "beer-of-the-month" pints for $3.25. There's a side room fun to reserve any night for gatherings of up to ~30, and lots of places to sit bigger groups of up to 4-10 if you score them early. Music is bartender's choice, but is often well suited to the crowd at any given time.
Food is fairly inexpensive typical pub food, but has some notable items - I like the big Cobb salad when you add the tasty seared tuna, the grilled turkey-swiss sandwich makes for a warm tasty-greasy sponge for a night of sessioning good beer, and the nachos (the menu touts "we layer the cheese throughout!") are good to pick at if you're with a crew.
You can get most of the above just about anywhere - but one thing I like to order to on the rare occasions when there's an obvious weight-watcher or vegetarian next to me at the bar is the infamous "Burgersaurus Rex" - Bacon, cheddar, grilled mushrooms, onions, and with a fried egg on top - there's no putting that slippery thing down, once you pick it up, until your heart freezes up right along with the smile on your face.
The Sasparilla BBQ burger and fish 'n' chips are popular too, served with excellent vinegar-thirsty hand-cut french fries. The Red Door has one of the more authentic thick-and-chunky New England Clam chowders in the city, served with good bread.
If you don't like crowds, or being bustled by lots of dudes looking for love in all the wrong places, this probably isn't your best bet to hang all Fri/Sat night. However, if you have friends in from out-of-town to catch up with mid-week, or you want to catch lunch in Fremont on the weekends just off the Burke-Gilman trail, the Red Door is great option for a beer and a bite to eat.