How can I return just different columns when I use except in SQL Server?
Example:
SELECT ID, NAME FROM TABLE_B
EXCEPT
SELECT ID, NAME FROM TABLE_A
In this case, if there is different name return just show name column.
2 Answers
Your code is correct. You won't get any repeated row (that's ID + NAME!).
But if I understand correctly, you only want to focus in names. Then remove ID from selected fields:
SELECT NAME FROM TABLE_B
EXCEPT
SELECT NAME FROM TABLE_A
[Edited, regarding a comment:]
This shows distinct rows from TABLE_B that aren’t in TABLE_A. This is the goal of using EXCEPT. For anything else, EXCEPT is not the solution.
In case you're looking for all diferent names from both tables, you can use:
select distinct NAME
from
(select NAME from TABLE_A
UNION
select NAME from TABLE_B) as T
2 Comments
diiN__________
With your solution you only get names from
TABLE_B that aren't in TABLE_A. What about the names that are just in TABLE_A?
MarcM
@diiN_ you're right. It shows distinct rows from TABLE_B that aren’t in TABLE_A.This is the goal of using EXCEPT. For anything else, EXCEPT is not the solution. Regarding your comment ...@diiN_ maybe your are looking for something like 'SELECT distinct NAME from (select NAME from TABLE_A UNION select NAME from TABLE_B) as T' ...which will get all diferent names from both tables.
You can get a result set which flags non-existing data in the second table in the form
ID flag_ID NAME flag_Name
1 ! A ! -- No Id=1, no NAME ='A' exists in the second table
3 NULL NULL ! -- Id=3 exists, no NAME is NULL exists
4 NULL Y NULL -- Both values exist but never in the same row
and proceed with a criteria you need.
Assuming ID is NOT NULL, NAME is nullable, NULLs should be considered "equal":
SELECT b.ID,
CASE WHEN NOT EXISTS (SELECT 1 FROM a t2 WHERE t2.ID=b.ID) THEN '!' END flag_ID,
b.NAME,
CASE WHEN NOT EXISTS (SELECT 1 FROM a t2
WHERE ISNULL(NULLIF(b.NAME, t2.NAME), NULLIF(t2.NAME, b.NAME)) IS NULL)
THEN '!' END flag_Name
FROM b
LEFT JOIN a ON a.ID = b.ID
AND ISNULL(NULLIF(a.NAME, b.NAME), NULLIF(b.NAME, a.NAME)) IS NULL
WHERE a.ID IS NULL
OR ISNULL(NULLIF(a.NAME, b.NAME), NULLIF(b.NAME, a.NAME)) IS NOT NULL
select id, name from tableb where id not in (select id from tablea)