SQL is made to function on Joins, I wouldn't say that they are "kind of "expensive" operation" RDMS databases are made to relate to other tables. if you had Primary and Foreign Keys set up and/or even indexes then it shouldn't be an "expensive operation".
here is the best query for what you are trying to accomplish.
SELECT player.name, player.surname
FROM player INNER JOIN person ON player.id = person.id
WHERE player.id = person.id
AND player.name = person.name
AND player.surname = person.surname
This is the write way to write the code, it's clean and straight forward to what is going on. your code essentially is doing the exact same thing, but is bad coding practice from my point of view.
SQL Fiddle Statistics and running code
SQL Fiddle Statistics for your code
(it actually looks like the execution is the same.)
I need to Note that both this query and the query that you are using join the two tables on their id columns. this is an issue because there is 1 id that holds different values in either table. so those id columns mean two totally separate things and shouldn't be joined on in all reality.
| 2001 | John | Carlin |
| 2001 | Pirlon | Austin |
in this specific instance the where statement covers the id being different, in this one case. BUT YOU NEED TO BE AWARE THIS IS BAD DATA you should fix the database.
Sorry to say but the
INTERSECT query that has been suggested actually takes longer and has more operations attached to it then both your original code and the code I posted.
SQL Fiddle from jmoreno's answer