Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
marksquall
Starting Member
17 Posts |
 Posted - 2013-05-09 : 15:59:09
|
| Dear SQLTeam friends,Hello everyone, I hope you are all doing fine. I have three (3) tables in which I would like to display the entry from my main table that doesn't exist on either two (2) tables.Example:Names tables <--MAIN TABLEFirstPeterBruceTonyScottSteveNamesArch tablesFirstBruceTonySteveNamesOldies tablesFirstBruceTonyScottNow I want to output from my main (Names) table.RESULT:Names FirstPeterScottSteveUsing the main table as the "main reference" to the other two table, The name Peter does not exist in both tables (NamesArch and NamesOldies). The name Scott does not exist in NamesArch. Lastly, The name Steve does not exist in NamesOldies.I usually have 50 records on each table. I hope someone could give me an idea how to achieve this. Thank you and more power.Respectfully Yours,MarkSquall"Listen to advice and accept instruction, and in the end you will be wise." -Proverbs 19:20 |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-05-09 : 16:07:58
|
| [code]SELECT name FROM MainTable mWHERE NOT EXISTS (SELECT * FROM NamesArch a WHERE a.NAME = m.NAME)OR NOT EXISTS (SELECT * FROM NamesOldLadies b WHERE b.NAME= m.NAME);[/code]By the way, you can specify SELECT 1 or SELECT 'a' or any other constant or literal instead of SELECT * in th eNOT EXISTS clause. There are proponents of using such constants/literals, but it doesn't make any difference when querying against base tables. |
 |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-05-09 : 16:24:33
|
quote:
Originally posted by James K
[code]There are proponents of using such constants/literals, but it doesn't make any difference when querying against base tables.
I would agree that the difference is trivial. But, SQL still has to lookup the meta data of the table if you do a SELECT *. So, as a practial matter it doesn't matter much. But, if you have a system that is doing billions of transactions a day, that tiny amount will add up. |
|
|
|
marksquall
Starting Member
17 Posts |
Posted - 2013-05-09 : 16:46:05
|
Dear James K:Wow, that was so quick, my previous query was so long, hahaha..silly me. Thank you so much. Though to be honest, please correct me, the way I understand this:quote:
...you can specify SELECT 1 or SELECT 'a' or any other constant or literal instead of SELECT * in the NOT EXISTS clause. There are proponents of using such constants/literals, but it doesn't make any difference when querying against base tables.
is this:NOT EXISTS (SELECT a.FIRST FROM NamesArch a WHERE a.FIRST = m.FIRST) I only use the column named FIRST rather than selecting all colums? I think I didn't get it, right?  I hope you'll be kind enough to explain in layman's term.But anyway, thank you all.  Respectfully Yours,MarkSquall"Listen to advice and accept instruction, and in the end you will be wise." -Proverbs 19:20 |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-05-09 : 16:50:48
|
| I have seen various conflicting opinions on it. One school of thought is that the SQL needs to look up some metadata and permissions anyway because of the existence of the columns from the table in the WHERE clause. Now that it has looked that up, when it sees the SELECT * in the select portion, it knows that it is checking for existence, so it does not go back to look at more metadata.I recall seeing articles that suggest that when the object being queried against is a view rather than a base table, then the behavior can be poor if you use SELECT *. I have seen no evidence of that, but then again, I haven't been looking.Would love to see some definitive information on this, even though, like you said, it is probably of minimal impact. But isn't that why we all love SQL - for the fun of tearing into the minutest detail? |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-05-09 : 17:23:52
|
quote:
Originally posted by marksquall
Dear James K:Wow, that was so quick, my previous query was so long, hahaha..silly me. Thank you so much. Though to be honest, please correct me, the way I understand this:quote:
...you can specify SELECT 1 or SELECT 'a' or any other constant or literal instead of SELECT * in the NOT EXISTS clause. There are proponents of using such constants/literals, but it doesn't make any difference when querying against base tables.
is this:NOT EXISTS (SELECT a.FIRST FROM NamesArch a WHERE a.FIRST = m.FIRST) I only use the column named FIRST rather than selecting all colums? I think I didn't get it, right? I hope you'll be kind enough to explain in layman's term.But anyway, thank you all. Respectfully Yours,MarkSquall"Listen to advice and accept instruction, and in the end you will be wise." -Proverbs 19:20
Don't use a real column name. Use either SELECT * or SELECT 1. The reason has to do with the metadata lookup that Lamprey mentioned. It is really an EXISTENCE check, i.e., "Is there a row that satisfies the where clauses". You don't care whether it is a.FIRST that exists, or anything that exists (*), or a constat (such as 1 which is returned in the select query for each row). That being the case, you don't want to make SQL server go and look up a specific column. |
|
|
|
|
|
|
|
|
Show values that does not exist in two tables?