SQL – Get value of column for rows matching a string AND a list of strings not found in the table?

I have a table that has (among other things) a “serial number” (VARCHAR(50)) column and an “active” (BOOLEAN) column… so a sample of data might look like this:

serial       | active
-------------+-------
ABCD232010E5 | True
ABCD1820102C | False
ABCD1820102C | True
ZYXW06210F20 | True
ABCD402004EZ | False

Now, from an outside source, I am given a list of serial numbers, like so:

ABCD232010E5
ZYXW49201X20
ABCD1820102C
ABCD402004EZ
ZYXW012100R3
ABCD44200B1W

Is there a single (ideally) query or series of queries I can run to get the following details:

• Does the serial number have an active=True entry?
• If not, does it have an active=False entry?
• If it doesn’t exist at all in the table, explicitly list it as such

I’m not terribly picky about the output, as long as:

• All the serials from my input are listed, whether or not they exist
• Each serial from my input is only listed once regardless of how many times it occurs in the table
• If it exists in the table, does at least one of the entries have active=True?

Something like the following would be fine (with comments added to explain why I expect the given output):

serial       | exists | active
-------------+--------+-------
ABCD232010E5 | True   | True    --has one record with active=True
ZYXW49201X20 | False  | False   --serial not found in table
ABCD1820102C | True   | True    --has one record with active=True and 1+ with active=False
ABCD402004EZ | True   | False   --only has record(s) with active=False
ZYXW012100R3 | False  | False   --serial not found in table
ABCD44200B1W | False  | False   --serial not found in table

I’d also be fine if the output were just two columns, with active=<null> for serial numbers that didn’t exist in the table. As I said I’m not picky, I just need to be able to distinguish the three states (exists and has active=True, exists and doesn’t have active=True, doesn’t exist).

Answer

One method is subqueries:

select i.*,
       (exists (select 1 from serials s where s.serial = i.serial and active
       )) as has_active_true,
       (exists (select 1 from serials s where s.serial = i.serial and not_active
       )) as has_active_false,
       (not exists (select 1 from serials s where s.serial = i.serial
       )) as no_match
from input i;

This can be run directly against a list of values by replacing input i at the end with the following:

(VALUES
        ('serial1'),('serial2'),('serial3'),
        ('serial4'),('serial5'),('serial6')
) AS i (serial)