Sorry to hijack slightly, but care to name these few? I’m in San Jose and currently have only one store I know of that I like. Would love to know of others!
I was trying to avoid this since our LFS's are the reason I order most everything online. I think shop space and labor in the Bay Area just make it almost impossible to run a LFS here. We used to have Atlantis Aquariums (originator of all the ATL corals you still see online), but they closed about 5 years ago and nothing has really filled in the void. Here's the ones I know of and my impressions. Hope this is helpful and I've tried to be as positive as possible.
East Bay/San Jose:
Diablo Corals (Concord, CA - 45 minutes from SF): In a warehouse in an older part of town, but still on a main street. Pretty small, but usually have a good selection of coral and some fish as I recall. It's been a few years since I've been here. Farthest one from San Jose, but closest to SF.
Aquatic Collection (Hayward, CA - 45 minutes - 1 hour from SF): again, a warehouse in an office/warehouse area (you'll see a theme). Great selection of fish and corals. This is probably the only one worth the drive and I'd prioritize this over others.
California Reef Company (Newark, CA - 1 hour from SF): Warehouse shop with mostly captive bred fish and corals. I just stopped there yesterday on the way back from a site meeting and they had a tank crash (50% of their coral space) and generally poor selection of corals. I'd not recommend this one, it's a shame as it used to be pretty good under different ownership.
Neptune Aquatics (San Jose, CA - 1 to 1.5 hours from SF): Great selection of corals and fish. Most of the corals are fragged from wild colonies with fewer aquacultured/named corals. They do have a good selection of zoas usually and cheap SPS. Lots of wild caught fish with a few captive bred specimens. A quality store, but usually named SPS and zoas are extremely expensive. I assume this is the one
@IPHY knows about. They moved a few years ago from a nice warehouse to a new warehouse in a dicey area of town that's mostly old industrial buildings and streetside drug sales. Unfortunately, I witnessed a shooting and carjacking on my last (failed) visit three years ago and haven't been back since.
Ultimate Aquarium (San Mateo, CA - 45 minutes from SF): It's across from the mall in a real retail shop space! Usually a pretty good selection of fish and corals. Lots of ORA fish and corals. They will order specific things from ORA if you want and can wait, but they usually have a pretty good selection. The manager is an interesting guy, but usually helpful and does keep aquariums, so he's generally knowledgeable.
San Francisco City:
6th Avenue: Big place that's crammed with stuff everywhere, wild caught fish and inverts in enormous tanks. Not very clean and poor fish health usually. Poor selection of corals. A good place to pick up cheap chinese LED's, equipment, or a quarantine tank. Shipments of fish come in on Sunday AM and they have a bag sale where you can select/buy the fish before they go into their tanks.
Lucky Ocean: Great owner here, but this place is just hanging on. REALLY small. Sometimes he has some corals but mostly pretty pedestrian LPS/softies.
Aquaforest Aquarium: Exclusively freshwater/planted tanks, but they are the sole importer of ADA tanks and equipment in the US. Great selection of ADA and higher end tanks and equipment. They also have a number of freshwater planted show tanks. If you are in the area it might be worth a drop through just to look. Again, no saltwater or coral.