[TSQL] how to write a efficient to get the difference from two table

Question

hi i am using SQL Server 2008, want to create a view to list the different between two tables.

for example

t1
id    name
----  ------
1      John
2      peter
3      mary
4      joe
5      sun

t2
id    name
---   ----
1      john
2      joe

how to create a view to list all the name in t1 but not in t2.

i tried to do that, and always get "Conversion failed when converting from a character string to uniqueidentifier." error

and also, i don`t want to use select something not in {something}, this is slow

so is there any way can use join??

Answer 1

NOT IN

---

SELECT t1.name
  FROM TABLE_1 t1
 WHERE t1.name NOT IN (SELECT t2.name
                         FROM TABLE_2 t2)

NOT EXISTS

---

SELECT t1.name
  FROM TABLE_1 t1
 WHERE NOT EXISTS (SELECT NULL
                     FROM TABLE_2 t2
                    WHERE t2.name = t1.name)

LEFT JOIN/IS NULL:

---

   SELECT t1.name
     FROM TABLE_1 t1
LEFT JOIN TABLE_2 t2 ON t2.name = t1.name
    WHERE t2.name IS NULL

Summary

---

Contrary to your belief, NOT IN will perform equivalent to NOT EXISTS. LEFT JOIN/IS NULL is the least efficient of the three options.

Answer 2

Here's a trick I use:

SELECT  MAX(TABLE_NAME) AS TABLE_NAME
       ,[id]
       ,[name]
FROM    (
         SELECT 'T1' AS TABLE_NAME
               ,[id]
               ,[name]
         FROM   T1
         UNION ALL
         SELECT 'T2' AS TABLE_NAME
               ,[id]
               ,[name]
         FROM   T2
        ) AS X
GROUP BY [id]
       ,[name]
HAVING  COUNT(*) = 1
ORDER BY [id]
       ,[name]

I actually have a universal proc I use which takes two tables and a bunch of parameters and does table compares generating dynamic SQL (the SQL above is actually cleaned up code-generated output of it), it uses either this UNION ALL trick or a combination of the three joins in OMG Ponies' answer depending on whether key columns/ignore columns/comare columns are specified.