Thanks for the props everybody
Stryker, very good explanation and experiment. There's another way to think about it (this though process doesn't explain going between tanks though). Just think about what could possibly be in the tube. For our purposes it is either water or air. The pipe is filled with water and both ends are submerged in water. How can air possibly get into the tube? It can't, hence our state of equilibrium
A more scientific explanation is that gravity is exerting a downward force on all things on the face of the planet, including water and air. Since water has a greater mass (per volume) it has a greater weight (mass * gravity). Pressure, in this sense, is all about density (mass per volume). The water has a greater density, and thus a greater force per volume pulling on it, and subsequently gets pulled through the air until it reaches a substance of even greater density (or a rigidity that prevents fluid from flowing, such as your fish tank). When you fill the tube with water and lift the middle out of the water there are two important forces in play:
1) Gravity pulling the water from the tube (and also pulling on the air, but that's inconsequential).
2) Molecular ionic forces holding the water molecules together.
Since the sum of the force of the water pushing down is greater at the openings of the tube than in the rest of the tank (because it's got the height of the water line, plus the water above the water line pushing down) one would think that the water would flow out. But, since there is nothing of lesser density to replace water in the tube there is actually a suction force (the molecular force I mentioned earlier) that holds the water in place. This equalizes the forces pulling the water out of the tube, and viola, you have a water arch
As for a siphon, say you've got one tank with a water level of 10cm, and another tank with a water level of 20cm (pretend the tanks are the same dimensions, just not filled the whole way). There is 10cm of water pushing down on the first tank, where there is 20cm of water pushing down on the second tank. This creates a pressure differential and water will flow from the 20cm tank to the 10cm tank until the pressures are equal, giving you two 15cm tanks.
Such is the magic of science
Hope that wasn't overkill, nor that I'm grossly inaccurate