Why is the action of lowering operator on the ground state of a harmonic oscillator to give a 0 wave function?

The eigenstates of the operator $N = a^\dagger a$ can be labeled by their eigenvalues, i.e. $N \phi_n = n \phi_n$, where $n$ is an integer. Note that $$n = \langle \phi_n,N\phi_n\rangle = \underbrace{\langle\phi_n,a^\dagger a \phi_n\rangle = \langle a \phi_n,a\phi_n\rangle}_{a \text{ and } a^\dagger\text{ are mutually adjoint}} = \Vert a \phi_n\Vert^2 \geq 0 $$

In particular, we have that $$ 0 = \Vert a\phi_0 \Vert^2$$ Since the only element of a Hilbert space with zero norm is the zero vector itself, we have that $$a \phi_0 = 0$$

You can do this by direct substitution in the position or momentum basis. Here it is for $x$:

\begin{align} \hat a|0\rangle &= \left[i\sqrt{\frac{m\omega}{2\hbar}}\left(\hat x - \frac i {m\omega}\hat p \right) \right] \left[\left(\frac{m\omega}{\pi\hbar}\right) e^{-mwx^2/2\hbar} \right]\\ &\propto \left[x+\frac {\hbar}{m\omega}\frac d{dx} \right]e^{-mwx^2/2\hbar}\\ &\propto xe^{-mwx^2/2\hbar}+\frac {\hbar}{m\omega}(-m\omega x/\hbar )e^{-mwx^2/2\hbar} \\ &= 0 \, . \end{align}

Expanding on J. Murray's answer (which basically is the answer), we can start from the so-called number operator and have $\hat{N} = a^{\dagger}a$. As it was explained Murray's answer, its spectrum is non-negative, because $$ \langle \psi | \hat{N} | \psi \rangle = \langle \psi | a^{\dagger}a | \psi\rangle = || a|\psi\rangle ||^2 \geq 0$$ so every eigenvector $|n\rangle$ must have non-negative eigenvalue $n$ (currently no assumption on $n$. Can be fractional, can be integer, who knows?)

Now an important point is that $[\hat{N},a]=-a$, which means that if $|n\rangle$ is an eigenvector with eigenvalue $n$, then, applying this commutator we get $$ \hat{N} a |n\rangle = a(\hat{N}-1)|n\rangle = (n-1)a|n\rangle$$ so $a|n\rangle$ is also an eigenvector of $\hat{N}$ with an eigenvalue $n-1$! This means that if we start with some arbitrary eigenvector, we can create a series of eigenvectors with decreasing eigenvalues, just by repeatedly applying $a$, hurray! The only problem is that we know that this series cannot go on endlessly. It must stop otherwise we will get to negative eigenvalues territory, which cannot be true. So the only way for this series of lowering eigenvalues to stop is if it exactly hits zero. Then we don't have a state any more, and acting with $a$ on the zero vector just remains zero. So the eigenvalues $n$ have to be the non-negative integers. The state with the lowest possible eigenvalue has to be $a|0\rangle = 0$.

Edit: I just now noticed your last paragraph where you asked for the answer to be in terms of wave-functions. So you can start with $$ \int\! dx \psi^*(x) \hat{N} \psi(x) = \int\! dx |a\psi(x)|^2 \geq 0$$ ensuring that the eigenvalues of $\hat{N}$ are nonnegative, and then have $$ \hat{N} a \psi_n(x) = a(\hat{N}-1)\psi_n(x) = (n-1)a\psi_n(x)$$ telling us that $a\psi_n(x)$ is a wavefunction which is an eigenfunction of $\hat{N}$ with eigenvalue $n-1$. Other than that all progresses as before