$ cat CMakeLists.txt 
cmake_minimum_required(VERSION 3.15)
project(link)
add_executable(test main.cpp foo-test.cpp)
add_library(lib foo-real.cpp)
target_link_libraries(test PRIVATE lib)
 $ cat main.cpp 
#include "foo.h"
int main() { return foo(); }
 $ cat foo.h
#pragma once
int foo();
 $ cat foo-real.cpp 
int foo() { return 0; }
 $ cat foo-test.cpp 
int foo() { return 1; }

 $ cmake --build b
[5/5] Linking CXX executable test
 $ ./b/test; echo $?
1

add_executable(test
  main.cpp
  foo-test.cpp
  foo-real.cpp
)
 $ cmake --build b
[1/1] Linking CXX executable test
FAILED: test 
: && /usr/bin/clang++   CMakeFiles/test.dir/main.cpp.o CMakeFiles/test.dir/foo-test.cpp.o CMakeFiles/test.dir/foo-real.cpp.o -o test   && :
/usr/bin/ld: CMakeFiles/test.dir/foo-real.cpp.o: in function `foo()':
foo-real.cpp:(.text+0x0): multiple definition of `foo()'; CMakeFiles/test.dir/foo-test.cpp.o:foo-test.cpp:(.text+0x0): first defined here

The reason for the difference in functionality is that there are different types of symbols.
A library function is a weak symbol. It is only included if it is not already defined.