user4824195 user4824195 - 1 year ago 66
SQL Question

SQL: Use SET operator to get a count result

I'm trying to get a count result using a SET operator in SQL Developer.

I have to find how many of "attribute1" are in "table_name1" but are not in "table_name2"

Essentially I want the result that I would get from the following query, but using a SET operator.

SELECT count(distinct <attribute1>)
FROM <table_name1>
WHERE <attribute1> IS NOT (SELECT <attribute1>
FROM <table_name2>);

Can anyone please help me?

Answer Source

If you must use a set operator, then you can solve this using MINUS:

SELECT COUNT(*)                      -- use COUNT(DISTINCT attribute1) to avoid
FROM                                 -- duplicates
    SELECT attribute1
    FROM table_name1
    SELECT attribute1
    FROM table_name2
) t

However, I would probably use a LEFT JOIN here because it's conceptually simple:

SELECT COUNT(DISTINCT t1.attribute1) -- replace with COUNT(*) to count duplicates
FROM table_name1 t1
LEFT JOIN table_name2 t2
    ON t1.attribute1 = t2.attribute1
WHERE t2.attribute1 IS NULL          -- indicates that attribute does NOT appear in
                                     -- the second table
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download