In theory, both forms should get optimized similarly by the DB, but the practice will likely differ from database to database, and maybe even from DB version to

In theory, both forms should get optimized similarly by the DB, but the practice will likely differ from database to database, and maybe even from DB version to DB version. There are though things that CTEs can do and sub-selects can't (e.g. WITH RECURSIVE)