I upvoted the answer by rinspy. Here, I’ll try to add a few things.
~ ~ ~
r and p-value measure different things.
The p-value indicates the probability of getting data as extreme† as the observed data assuming that the null hypothesis is true. By our decision rule, if p < alpha, we have sufficient evidence to reject the null hypothesis that there’s no correlation. And that’s all the p-value does for us.
The p-value result is often much less informative than we pretend it is. Concluding that there’s a statistically significant correlation doesn’t tell us how strong the correlation is, and certainly doesn’t tell us if the correlation is of practical importance.
r is a measure of effect size. It tells us how strong the correlation is.
Interpretation of effect sizes are necessarily dependent on the discipline and the expectations of the analysis. In physics or chemistry, a near-perfect relationship may be expected, whereas in macro biology or psychology, a much smaller effect size may be notable. Cohen (1988) gives some guidelines for behavior sciences: Small, ≥ 0.10; Medium, ≥ 0.30; Large ≥ 0.50.
~ ~ ~
Effect sizes are not affected by sample size, whereas a p-value will be affected by sample size for a given effect size.
Consider x = (1, 2, 3), y = (1, 1, 2). Here, r = 0.866; p = 0.33.
Now, we’ll keep the same values, but double the number of observations for each of x and y. The effect size stays exactly the same. But now, with six observations, and an r of 0.866, the p-value decreases to below 0.05.
x = (1, 2, 3, 1, 2, 3), y = (1, 1, 2, 1, 1, 2). r = 0.866; p = 0.03.
And we’ll increase the sample size again.
x = (1, 2, 3, 1, 2, 3, 1, 2, 3), y = (1, 1, 2, 1, 1, 2, 1, 1, 2). r = 0.866; p = 0.003.
For small samples, an effect size may not be very informative. For example, if we measure the height and weight of two people, we will find that height and weight are perfectly correlated, and r = 1. This will not impress us. If we increase the sample size to three or four, we still may find a large r value, but know that this could very well be by chance. Here, we might rely more on the p-value to determine if something interesting going on.
For larger sample sizes, a p-value might be significant even if the r is small. Here, we want to make sure we look at the effect size, r, and not give too much weight to the p-value.
~ ~ ~
r may not always be the best way to understand the size of a correlation. For example, if I were telling you about a correlation between corn yield and fertilizer rate, “p = 0.01; r = 0.4” might be of interest. But probably you would want to know something like, “For an increase in 5 kg/ha fertilizer, corn yield increased 1000 kg/ha.
† In this case, "as extreme" means as correlated.
Cohen, J. 1988. Statistical Power Analysis for the Behavioral Sciences, 2nd Edition. Routledge.