strcat and STRCAT are two different functions.:)
I think you mean the function name strcat instead of STRCAT in this declaration
    char *
strcat(char *dest, const char *src)
{
  strcpy(dest + strlen(dest), src);
  return dest;
}
This function is designed to deal with strings that is with sequences of characters terminated by the zero character '\0'.
The function strlen returns the number of characters in a string before the terminating zero character '\0';.
So the expression  dest + strlen(dest) points to the terminating zero character '\0' of the string contained in the destination character array. Thus the function strcat can append the source string to the string stored in the destination array starting from the terminating zero.
So for example if you have a character array declared like
char dest[3] = { '1', '0' };
and the source array declared like
char src[2] = { '2', '0' };
For the array dest the function strlen( dest ) will return the value 1.
As a result dest + strlen( dest )  points to the second character of the array dest that is the zero character '0'. And this call
strcpy(dest + strlen(dest), src);
will copy characters of the string stored in the array src in the array dest starting with this position and you will get the following content of the array dest
{ '1', '2', '\0' }
In your program the expression sizeof( s ) gives the number of elements with which the array s is declared
char s[10]= "123456789"
that is 10. This value specified in the declaration of the array does not depend on the content that the array will have and is calculated at the compile time.
Pay attention to that values returned by the operator sizeof have the type size_t. So to output them using the function printf you have to use the conversion specifier zu. For example
printf("%zu\n",sizeof(s));
In your program the array str
char str[10] = " 123456789";
does not contain a string because it does not have a space to accommodate the (eleventh ) terminating zero character of the string literal used as an initializer.
On the other hand, the array s does not have a space to be able to append another string to its tail.
So your program has undefined behavior.
A valid program can look like
#include <stdio.h>
#include <string.h>
int main( void )
{
    char str[] = " 123456789";
    char s[10 + sizeof( str ) - 1] = "123456789";
    strcat( s, str );
    printf( "%s\n", s);
    printf( "%zu\n", strlen( s ) );
    printf( "%zu\n", sizeof( s ) );
}
Pay attention to that according to the C Standard the function main without parameters shall be declared like
int main( void )