How does water sink works?
This is a simple case of communicating vessels:
When water enters your sink, it raises the water level on the right side of the kink all the way up to the sink. This means that the water pressure on the right side of the pipe is greater than on the left side of the pipe. This imbalance is "not allowed", so the water level on the left side rises (therefore increasing the pressure on that side). If it could, it would rise up to the level of the water on the right side of the pipe. However, since the left side connects to the sewage column, the water merely drains away.
Once you close the faucet, the water on the right hand side will drop until it reaches an equilibrium with the left side.
What you tried with the flexible tubing didn't work because both the entrance and exit levels are the same. If you are putting water in from the left (as would seem to be the case), then lower the right side just a bit. When you add the water, it will fill the tubing up on both sides until it reaches an exit, which will be the now-lowered right side.