• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 741
  • Last Modified:

To compare uncommon values in two tables

Hi,
Suppose we have two tables both have a column CODE. Now we want to know what are the values that appear in CODE column of table 1 but are not present in CODE column of table 2. What should be the TSQL to find that?
0
nkapur
Asked:
nkapur
  • 2
1 Solution
 
NightmanCTOCommented:
Give this a bash.

SELECT * FROM Table1 as t1
WHERE
  NOT EXISTS
  (
    SELECT * FROM Table2 as t2 WHERE t2.CODE=t1.CODE
  )
0
 
wilcoxonCommented:
Nightman has the right of it.  I would also recommend using select 1 rather than select * in the sub-query (usually very minimal performance impact but can be substantial if Table2 has wide rows).

SELECT * FROM Table1 as t1
WHERE
  NOT EXISTS
  (
    SELECT 1 FROM Table2 as t2 WHERE t2.CODE=t1.CODE
  )
0
 
NightmanCTOCommented:
normally wilcoxon would be correct about the select *, but in reality this a boolean expression and the value of * isn't evaluated, only the condtions.

You could even do the following with no divide by zero error:

SELECT * FROM Table1 as t1
WHERE
  NOT EXISTS
  (
    SELECT 1/0 FROM Table2 as t2 WHERE t2.CODE=t1.CODE
  )
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now