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 |
|
chiragkhabaria
Master Smack Fu Yak Hacker
1907 Posts |
 Posted - 2005-09-17 : 09:41:45
|
| Hi All,I have 2 tables tblA and tblBnow in tblA there are 2 columns A and B which forms the pair of composite keysand in tblB also i have 2 columns A and B which forms the pair of componsite keys. now i want to know the records which are there in the tblA and not there in TblB.. Thanks in Advance. :-)Complicated things can be done by simple thinking |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-09-17 : 10:03:29
|
| Assuming the column names are the same:SELECT * FROM tblA AWHERE NOT EXISTS(SELECT * FROM tblB B WHERE A.col1=B.col1 AND A.col2=B.col2) |
 |
|
|
chiragkhabaria
Master Smack Fu Yak Hacker
1907 Posts |
Posted - 2005-09-17 : 11:52:54
|
| Hey cool.. its worked for me.. But can u explain breifly how does this query works.. ??????? and also can be apply ..Complicated things can be done by simple thinking |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-09-17 : 13:11:27
|
| Two key points:-EXISTS clause (this is pretty self-explanatory)-Correlation between the outer (main) query and the inner (sub) query, by using aliasesBooks Online has more details under "EXISTS", you should read it so you get a better understanding.Another way of writing this query is to use a LEFT JOIN with a WHERE clause:SELECT A.* FROM tblA A LEFT JOIN tblB B ON A.col1=B.col1 AND A.col2=B.col2WHERE B.col1 IS NULLThis works because a LEFT JOIN returns all the rows from the left-hand table, even if there's no match in the right-hand table. The WHERE clause specifically finds rows that do not match on the right-hand side. (this is how the Find Unmatched Query wizard in MS Access works)Either query will provide the same results; which type you use is simply a matter of personal preference. In fact, the SQL Server query optimizer could very likely generate the same (or nearly the same) query plan to process them. I prefer the NOT EXISTS style because I find it to be more logical and self-explanatory, especially in more complicated queries with more JOIN conditions. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-09-18 : 02:37:17
|
| "which type you use is simply a matter of personal preference"Indeed personal choice.I prefer the LEFT OTUER JOIN style because I find this type of thing often becomes an UPDATE if exists and INSERT if not exists, and I can then convert the OUTER JOIN to an INNER for the data I want to copy across between the two tables, or compare, or whatever.That in turn means that I don't have to rearrange the criteria [between an EXISTS and a JOIN], I can just CUT&PASTE, and thus is less prone to [my!] human errorKristen |
|
|
|
|
|
|
|
|
Composite Key..