If you've ever had one of the dog waterers with the dish on bottom that the big water bottle sits on top, it works the same way.
The water in the bottle trying to flow downward creates a vacuum that cannot be broken until air is let in. As your dog drinks, the water the level in the bottom dish drops low enough to go below the opening and lets air in, which allows the water in the bottle to flow into the dish, until the water in the dish comes up far enough to block air flow into the bottle and create suction again.
With an aquarium the open bottom of the aquarium is sitting under water so that air cannot be let in to break the suction, which would otherwise allow the water to go where it naturally tries to go (downward with gravity).
The water trying to flow downward in the aquarium creates suction, just as you can feel demonstrated by putting your hand over an open drain in a sink or tub full of water (you can feel the suction on your hand). Without somewhere to let in air, the water just creates suction against the aquarium, which has no give and no gaps, so the water stays in one place.
