The reason the water stays in the straw is because its weight is supported by the force due the atmospheric pressure.
Suppose your straw has uniform cross-sectional area

If

is atmospheric pressure, then the force supporting the column of water is
Let

be the height of water in the straw. The volume of water in the straw is

if

is the density of water, then the mass of water in the straw is

and its weight is
So, in order for the water to stay in the straw, you need to have
Now plug in these values:

approximately.
Hence the height of the water in your straw cannot exceed about 10.34 metres (assuming your giant straw is that long) at sea level. For seawater, this maximum height will be less as seawater is denser than ordinary water.
Perhsps surprisingly, the maximum height of water you can pick up in your straw does not depend on the cross-sectional area of the straw. However, the bigger the cross-sectional area, the harder it will be to keep air from seeping in through the sides of the straw and causing the mass of water to fall off. Hence the maximum amount of seawater you can pick up in your giant straw will always be limited (both theoretically and physically).