I have the following tables:                  player| id         | name        | surname      ||:-----------|------------:|:------------:|| 1001       |    Mike     |    Tyson     || 2001       |    John     |    Carlin    || 3001       |    Dan      |    StanH     || 4001       |    Alen     |     Derl     |                  person| id         | name        | surname      ||:-----------|------------:|:------------:|| 1001       |    Mike     |    Tyson     || 2001       |    Pirlon   |    Austin    || 6001       |    Danly    |    StanH     || 4001       |    Alen     |     Derl     |I want create an SQL query in order to get the same records of the above schemes and print first and last name.The query is SELECT player.name, player.surname    FROM  player INNER JOIN person      on  player.id =  person.id     AND  player.name = person.name     AND  player.surname = person.surname;Is there any way to create a more efficient query?
Same records between two tables
optimization;sql
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.edithere 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.idWHERE player.id = person.id    AND player.name = person.name    AND player.surname = person.surnameThis 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 codeSQL 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.  example: player table| 2001       |    John     |    Carlin    |ANDperson table| 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