SQL: Use SET operator to get a count result

Question

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?

score 0 · Answer 1 · answered Oct 06 '16 at 05:49

0

Please try below solution:

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

I hope this will helpful to you.

answered Oct 06 '16 at 05:49

chirag satapara

1,947
1
15
26

I have to use a SET operator. Not a relational operator. – user4824195 Oct 06 '16 at 05:59

Tim Biegeleisen · Accepted Answer · 2016-10-06T06:09:28.403

0

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
    MINUS
    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

edited Oct 06 '16 at 06:09

answered Oct 06 '16 at 05:49

Tim Biegeleisen

502,043
27
286
360

I have to use a SET operator. LEFT JOIN isn't a SET Operator. – user4824195 Oct 06 '16 at 05:59
@user4824195 I updated my answer, you can use `MINUS` as well. – Tim Biegeleisen Oct 06 '16 at 06:05

Tharsan Sivakumar · Answer 3 · 2016-10-06T06:08:44.937

0

SELECT COUNT(<attribute1>)
FROM <table_name1>
WHERE <attribute1> MINUS (SELECT <attribute1>
                           FROM <table_name2>);

https://docs.oracle.com/cd/B19306_01/server.102/b14200/operators005.htm

Updated answer

SELECT COUNT(X.id_num)
(SELECT id_num
FROM Tree 
WHERE id_num)
MINUS 
(SELECT id_num 
FROM Bird) AS X

edited Oct 06 '16 at 06:08

answered Oct 06 '16 at 05:50

Tharsan Sivakumar

6,351
3
19
28

We don;t need to use DISTINCT as MINUS returns only DISTINCT result set – Tharsan Sivakumar Oct 06 '16 at 05:51
This returns an error message - INVALID Relational Operator. – user4824195 Oct 06 '16 at 05:58
Can you share your exact query and the error you received ? – Tharsan Sivakumar Oct 06 '16 at 06:00
SELECT COUNT(id_num) FROM Tree WHERE id_num MINUS (SELECT id_num FROM Bird); – user4824195 Oct 06 '16 at 06:06
ORA-00920: invalid relational operator. Error at Line: 3 Column: 14 – user4824195 Oct 06 '16 at 06:06
Updated answer now. Could you please try again ? – Tharsan Sivakumar Oct 06 '16 at 06:09
You are still missing a `from` clause and you can't use `as` for a table alias in Oracle. Look at Tim's answer to see what the correct syntax is. – Oct 06 '16 at 06:10

SQL: Use SET operator to get a count result

3 Answers3