Select rows using EXCEPT - SQL Server

Question

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.

can you provide some sample data and desired output as i don't fully understand what you're asking? — Tanner, Nov 23 '16 at 11:40
Maybe have a look at [this answer](http://stackoverflow.com/a/2077929/5809511). — diiN__________, Nov 23 '16 at 11:45
`select id, name from tableb where id not in (select id from tablea)` — McNets, Nov 23 '16 at 11:47

MarcM · Accepted Answer · 2018-04-12T16:46:08.707

1

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

edited Apr 12 '18 at 16:46

answered Nov 23 '16 at 11:50

MarcM

2,173
22
32

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`? – diiN__________ Nov 23 '16 at 13:00
@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. – MarcM Nov 23 '16 at 13:21

score 0 · Answer 2 · answered Nov 23 '16 at 13:18

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 rows using EXCEPT - SQL Server

2 Answers2