0

I have 2 tables in sql server ...

  • Table 1 contains 100,000 account names and account IDs
  • Table 2 contains 10,000 account names and account IDs that are already included in table 1

I need to show all the records that are in table 1, that don't also appear in table 2 (so that should give me 90,000 results.

I am completely stuck on how to show this, whether it be a join or a select in the where clause

thanks, and apologies I know lots of things like this are out in the ether

Shiva
  • 20,575
  • 14
  • 82
  • 112
user2957104
  • 1
  • possible duplicate of [How can I join two tables but only return rows that don't match?](http://stackoverflow.com/questions/6613708/how-can-i-join-two-tables-but-only-return-rows-that-dont-match). The accepted answer looks to be a good "general sql" solution. – Paul Richter Feb 12 '14 at 00:00

4 Answers4

2

Instead of a JOIN, I think you want EXCEPT.

SELECT * FROM table1
EXCEPT
SELECT * FROM table2

Of course, that only works with the columns are the same, or if you can specify a common list of columns instead of *.

Tim
  • 4,999
  • 3
  • 24
  • 29
1

You should be using a LEFT OUTER JOIN.

Like so.

SELECT T1.*
FROM Table1 T1 LEFT OUTER JOIN Table2 T2
   ON T1.AccountID = T2.AccountID
WHERE T2.AccountID IS NULL

Explanation: This will take all the rows from Table1 and attempt to link them with all the rows in Table2. Where there is a match, the Table2.AccountId will have the matching AccountID. Where there isn't a match, the Table2.AccountId will be NULL.

Another way to do this is without a JOIN, is by using the EXISTS keyword.

Shiva
  • 20,575
  • 14
  • 82
  • 112
0

I'm not sure of the exact sql-server syntax, but in other databases you can either do

select * from table1 t1 where t1.id not in (select id from table2)

alternatively the 'null join' method

select * from table1 t1 left join table2 t2 on t1.id = t2.id where t2.id is null
Matt
  • 3,303
  • 5
  • 31
  • 53
0

I think you are confusing 2 SQL concepts:

A Join is used generally to join two tables together that are linked via some key: For example You may have a login table with usernames and passwords which has a USERID field. In another table you have the address information about the same user that also has the USERID field. You would simply do SELECT ... FROM passwords p join address a on p.userid = a.userid. That will give you all user passwords and the associated user data with it.

In your case you dont need to combine, you just want to use NOT IN

SELECT * FROM TABLE 1 WHERE somevalue not in (SELECT somevalue from table2)
logixologist
  • 3,694
  • 4
  • 28
  • 46