Redirections are processed from left to right, and in Bash, after pipelines (in fact, in any POSIX shell). In
cat numbers.txt | sort < words.txt
the shell first sets up sort’s (future) standard input to be the pipe from cat, but then when it processes < words.txt, changes it to read words.txt.
<< starts a heredoc:
cat numbers.txt | sort << words.txt
will sit waiting for a line saying “words.txt” (since that is specified as the end-of-input marker in your command). See What are the shell's control and redirection operators?