Query A if there is no B

I have a table with columns ID, TYPE, DATE, ... and I need all data sets with TYPE <> 'A' (case 1) and the data sets with TYPE = A if there is no TYPE = B with the same ID (case 2). The first case is easy so I think to use UNION to combine the first and the second case. But I have no idea how to select the second case. I'm experimenting with self joins like:

SELECT *
FROM MY_TABLE TAB1
JOIN MY_TABLE TAB2
ON
  TAB1.ID = TAB2.ID
  AND TAB1.TYPE = 'A'
  AND TAB2.TYPE = 'B'

This way I get the sets which I don't want. I think I need a subquery but I have no idea in which direction I should work.

Maybe something like this:

SELECT *
FROM MY_TABLE TAB1
WHERE TAB1.TYPE <>'A'
UNION
SELECT *
FROM MY_TABLE TAB1
NOT IN
(SELECT *
FROM MY_TABLE TAB1
JOIN MY_TABLE TAB2
ON
  TAB1.ID = TAB2.ID
  AND TAB1.TYPE = 'A'
  AND TAB2.TYPE = 'B')

Example:

ID | TYPE | ...
---------------
1  |  B   | ...
2  |  A   | ...
2  |  B   | ...
3  |  A   | ...

I need row 1 (TYPE <> 'A'), row 3 ( TYPE <> 'A') and row 4 (there is no set with same ID and TYPE = 'B'). I don't want row 2, because TYPE = 'A' and there is a data set with same ID and TYPE = 'B'. So the result should be:

ID | TYPE | ...
---------------
1  |  B   | ...
2  |  B   | ...
3  |  A   | ...

Use NOT EXISTS:

Declare @T table (id int, [type] varchar(5))
insert into @t(id,[type]) values(1,'B'),(2,'A'),(2,'B'),(3,'A')

SELECT *
FROM @T TAB1
WHERE [TYPE] <> 'A'

UNION ALL

SELECT *
FROM @t TAB1
WHERE [type] = 'A'
    AND NOT EXISTS (
        SELECT *
        FROM @t
        WHERE id = TAB1.id
            AND [type] = 'B'
        )

The above uses UNION ALL (simple concatenation), which is typically more efficient than duplicate-removing UNION [DISTINCT]. Two sets, one with type = A and the other with type <> A cannot possibly overlap, so concatenation is safe here.

You could also express the logic as WHERE [TYPE] <> 'A' OR ([TYPE] = 'A' AND NOT EXISTS ...) but check which method gives you better performance (complex disjunctions do not always optimize well).

StackExchange Database Administrators Q#196572, answer score: 6