Same records between two tables

Question

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?

• Daniel Dawes: I would consider, if possible, normalizing the database a touch so you don't have this repetition of data, and being forced to write queries like this. Having a table of 'Players' that instead just has a PlayerID and a foreign key PersonID would be a lot better and cause less issues.
• peterm: It is as good as you can get in terms of SQL. Now the efficiency is a subject for many other factors: RDBMS you're actually using, if it is MySQL what engine is being used, actual table schemas, what indices you have, cardinality for both tables etc.
• istovatis: Actually, finding the same records between those two tables, was just a simple task I was asked. I just figured out the query. Considering that inner join is a kind of "expensive" operation, I was wondering if there is a more efficient way, in terms of sql query.

Answer 1

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".

edit

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.

example:

player table

| 2001 | John | Carlin |

AND

person 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

• istovatis: This query returns ERROR: more than one row returned by a subquery used as an expression. Maybe you meant 'in' instead of '=' in your query. I like your query, but assuming that id is not the primary key, (so there is not primary key at all) does this query return a valid output?
• Malachi: Yes i meant in, my bad. It will provide valid informating if both tables hold exactly the same information in these columns for each ID. I will edit the question when i get to a laptop.
• Malachi: I fixed that query in my post. and like I said it will return valid information. if the information is the same in the both tables. then it will also be correct.
• jmoreno: Sample data clearly shows that ID is insufficient for a match. But I do agree with your comment on joins.
• Malachi: @jmoreno, I can't believe that I didn't look close enough at the sample input.

Answer 2

The efficiency of a join is primarily based upon covering indexes, not number of columns used. Given your scenario, you basically have the right idea.

You don't mention which RDBMS you are using, but you could possibly use the INTERSECT command.

Select id, name, surname
From player
Intersect
Select id, name, surname
From person

• Malachi: nice I was working on redoing my answer...
• jmoreno: @Malachi: I mainly answered because there's a non-join approach.
• Malachi: I think I might have another. but that looks good too. but I think I might be able to come up with something else as well.
• Malachi: your code takes longer to run and has more associated with it than the original posters code... SQLFiddle your code actually forces a left semi join, then a quicksort which draws attention and resources.
• jmoreno: Adding an index should even that out a bit. sqlfiddle.com/#!6/d2b3d/1. But it conveys the intent better, which lacking a performance bottle neck, I would prefer over both size and speed.