In the radial probability function, there is an extra r2 term that is multiplied by |ψ2|. So mathematically, it is zero at r=0. When you solve the equation, it turns out that a0 is where the maximum radial probability occurs for the 1s orbital.
In general, ψ is a mathematical wave function, or a state function. It is not a physical wave, but a formula that gives information about the state of the system, that is, all the forces acting and all the positions and momenta of the particles. ψ is a function of the coordinates of the particles of the system and, if the state changes with time, it would also be a function of time. However, for an isolated atom or molecule (like the hydrogen atom), the forces acting depend only on the coordinates and are independent of time, so this simplifies the state function.
|ψ2|, the probability density, can be thought as the probability of finding an electron at a any specific point in space at a given time. Since ψ is highest at r=0 (near the nucleus), then this is saying is that although the electron can be found at any location in the atom (in contrast to Bohr's theory which said that it had to be at a specific radius), the probability of finding the electron is greater at distances closer to the nucleus.
The radial probability is something different. Here were asking what is the probability that the distance between the nucleus and the electron is between r and r + dr, where dr is an infinitesimal change in radius. This is the probability of finding the electron in a thin spherical shell whose center is at the nucleus and whose inner and outer radii are r and r + dr, respectively. This interpretation can be restated in terms of electron density to say that the radial distribution function represents the electron density on the surface of a sphere of radius r. This is found by multiplying |ψ2| (the probability per unit volume) by the volume of the shell, 4∏r2dr for an s orbital. (For a p orbital, you need to take into consideration the angles, because ψ depends on the angle.) Hence, logically, in a sphere of radius 0, the electron density has to be 0 as well!
For the 1s orbital of the hydrogen atom, the probability density is maximum at the origin (because ψ is highest there), but the radial distribution function is zero at the nucleus, with the most probable value being a0. It is not contradictory. Like you've seen, the radial distribution has two components, the |ψ2| and the shell volume 4∏r2dr component. In finding the radial probability, there are many shell volume elements in the space. As we increase r, the shell volume increases (the shell volume is zero at r=0). Since the shell volume and the |ψ2| elements are multiplied in the probability, this increase in shell volume is offset by the corresponding decrease in |ψ2| resulting from the increase in r. So the maximum radial probability has to be somewhere between 0 and ∞, and it turns out that for the 1s orbital is a0.
Hopefully, this will help you clarify the concepts.