CODEWITHSUNDEEP
sqloracle-database
Patrick Weiß
Patrick Weiß

Reputation: 456

Comparing SQL Queries

I'm considering two SQL queries (Oracle) and I shall state the difference between them by showing examples. The queries are as follows:

/* Query 1 */
SELECT DISTINCT countryCode
FROM Member M
WHERE NOT EXISTS(
(SELECT organisation FROM Member 
WHERE countryCode = 'US')
MINUS
(SELECT organisation FROM Member
WHERE countryCode = M.countryCode ) )

/* Query 2 */
SELECT DISTINCT M1.countryCode 
FROM Member M1, Member M2
WHERE M2.countryCode = 'US' AND M1.organisation = M2.organisation
GROUP BY M1.countryCode 
HAVING COUNT(M1.organisation) = (
SELECT COUNT(M3.organisation)
FROM Member M3 WHERE M3.countryCode = 'US' )

As far as I get it, these queries give back the countries which are members of the same organisations as the United States. The scheme of Member is (countryCode, organisation, type) with bold ones as primary key. Example: ('US', 'UN', 'member'). The member table contains only a few tuples and is not complete, so when executing (1) and (2) both yield the same result (e.g. Germany, since here only 'UN' and 'G7' are in the table).

So how can I show that these queries can actually return different results?

That means how can I create an example table instance of Member such that the queries yield different results based on that table instance?

Thanks for your time and effort.

Upvotes: 0

Views: 106

Answers (3)

shawnt00
shawnt00

Reputation: 17915

The first query would return countries whose list of memberships is longer than that of the US. It does require they include the same organizations as US but it could be more.

The second one requires the two membership lists to be identical.

As for creating an example with real data, start with an empty table and add this row:

insert into Member (countryCode, organisation)
    values ('Elbonia', 'League of Fictitious Nations')

By the way a full outer join would let you characterize the difference symmetrically:

select
    mo.countryCode || ' ' ||
    case
        when count(case when mu.organisation is null then 1 else null end) > 0
         and count(case when mo.organisation is null then 1 else null end) > 0
        then 'and US both have individual memberships they that do not have in common.'

        when count(case when mo.organisation is null then 1 else null end) > 0
        then 'is a member of some organisations that US is not a member of.'

        when count(case when mo.organisation is null then 1 else null end) > 0
        then 'is not a member of some organisations that US is a member of.'

        else 'has identical membership as US.'
    end
from
    (select * from Member where countryCode = 'US') mu
    full outer join
    (select * from Member where countryCode = '??') mo
        on mo.organisation = mu.organisation

Please forgive the dangling prepositions.

And a disk note, though duplicate rows are not allowed in normalized data, this query has no problem with those.

Upvotes: 1

Patrick Weiß
Patrick Weiß

Reputation: 456

I've finally found an example to show that they can actually output different values based on the same Member instance. This is actually the case when Member contains duplicates. For query 1 this is not a problem, but for query 2 it actually affects the result, since here the number of memberships is crucial. So, if you have e.g. ('FR', 'UN', member) twice in Member the HAVING COUNT(M1.organisation) will return a different value as SELECT COUNT(M3.organisation) and 'FR' would not be part of the output.

Thanks to all for your constructive suggestions, that helped me a lot.

Upvotes: 1

AvielNiego
AvielNiego

Reputation: 1233

The queries will result all the country codes which are members at least with all the organization the US is member with (it could be member with other organizations as well).

Upvotes: 2

Related Questions