The formula for the variance of the sampling distribution of the sample mean $$\sigma_x^2 = \frac{\sigma ^2}{n} $$
What I don't understand is the discrepancy that occurs when sample size, $n$ becomes the population size $N$. Logically, if the sample size is equal to the population size, the variance for the sampling distribution of the sample mean should be $0$, since no matter how many means we calculate, they will all be equal to the population mean. Thus, the variance - which measures the mean of the squared distance from every sample mean to the population mean - should be $0$. Nevertheless, it is not $0$ even when $n = N$ according to the formula. One more aspect I would like to understand is why the variance of the sampling distribution of the sample mean is $n$ times less than the population variance.
I've searched through numerous sources that prove the formula for the variance of the sample means using properties of variance, but none of them intuitively derives the formula.
I would highly appreciate an intuitive explanation of why the formula for the variance of the sampling distribution of the sample mean appears to be the way it is.
Saying the standard error of the sample mean is $\dfrac{\sigma}{\sqrt n}$ is correct when sampling from a distribution or when sampling from a finite population with replacement.
But when sampling without replacement from a finite population of size $N$, you are correct to say that this overstates the uncertainty. Instead the standard error of the sample mean is $$\dfrac{\sigma}{\sqrt n}\sqrt{\frac{N-n}{N-1}}$$ and this is indeed $0$ when $n=N$. You can find a justification on Cross Validated.
The $\sqrt{\frac{N-n}{N-1}}$, or its square when working with the variance, is sometime known as the finite population correction.