Finding records that don't match

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Finding records that don't match

Author

Topic

pwebley
Starting Member

6 Posts

Posted - 2012-02-08 : 18:51:54

Right now I am running a join query that finds matching item numbers and pulls in my specified columns on those matches. Now that I have seen what does match in my data between the two tables, what would be a good way of finding out what doesn't match? For example, when an item number in one table matches with an item number in another table columns from both tables are brought in (forgive me for just explaining the join statement). How can I reverse this to say, "What item numbers are in the first table that aren't in the second table?" I am new to SQL, so sorry if this doesn't make sense!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-08 : 18:58:16

You can use a LEFT JOIN, or a NOT EXISTS condition in a WHERE clause - as in this example:

CREATE TABLE #tmp1 (id INT);
CREATE TABLE #tmp2 (id INT);

INSERT INTO #tmp1 VALUES (1),(2),(3);
INSERT INTO #tmp2 VALUES (3),(4),(5);

SELECT
	t1.*
FROM
	#tmp1 t1
	LEFT JOIN #tmp2 t2 ON t1.id = t2.id
WHERE
	t2.id IS NULL;
	
SELECT * FROM #tmp1 t1 
WHERE NOT EXISTS
(SELECT * FROM #tmp2 t2 WHERE t2.id = t1.id);

DROP TABLE #tmp1;
DROP TABLE #tmp2;

pwebley
Starting Member

6 Posts

Posted - 2012-02-08 : 19:28:54

Holy smokes that worked, thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources