CODEWITHSUNDEEP
sqloracle11g
sahar
sahar

Reputation: 569

what is the problem in this sql statement?

I write this statement which works correctly in all cases except one case

SELECT p.priv_no FROM osqs_privileges p,osqs_users_privileges up 
WHERE up.priv_no = p.priv_no AND up.user_no = 54 AND up."GRANT" = 'Y' 
    UNION 
SELECT p.priv_no FROM 
osqs_privileges p,osqs_groups_privileges gp,osqs_users_groups ug,osqs_users_privileges up 
    WHERE gp.priv_no = p.priv_no AND ug.grp_no = gp.grp_no AND ug.user_no = 54 
AND gp.priv_no NOT IN 
    (SELECT priv_no FROM osqs_users_privileges WHERE user_no = 54 AND "GRANT" = 'N');

in a case that osqs_users_privileges have no rows and this statement (SELECT priv_no FROM osqs_users_privileges WHERE user_no = 54 AND "GRANT" = 'N') return 0 rows, all the script return 0 rows even if othe select statements returns data. why?

Upvotes: 0

Views: 133

Answers (3)

sahar
sahar

Reputation: 569

this is the right command:

    SELECT p.priv_no FROM osqs_privileges p,osqs_users_privileges up 
WHERE up.priv_no = p.priv_no AND up.user_no = 55 AND up."GRANT" = 'Y' 
    UNION 
SELECT p.priv_no 
FROM osqs_privileges p 
LEFT JOIN osqs_groups_privileges gp on gp.priv_no = p.priv_no 
LEFT JOIN osqs_users_groups ug on ug.grp_no = gp.grp_no 
LEFT JOIN osqs_users_privileges up on up.priv_no = p.priv_no 
WHERE ug.user_no = 55 
AND gp.priv_no NOT IN 
    (SELECT priv_no FROM osqs_users_privileges WHERE user_no = 55 AND "GRANT" = 'N') ;

Upvotes: 0

Mitch Wheat
Mitch Wheat

Reputation: 300509

It's due to the behaviour of NULLs in the NOT IN clause.

For example,

select 'yes' where 3 in (1, 2, 3, null)  -- Return result
select 'yes' where 3 not in (1, 2, null) -- Returns an empty set

Why? Because 3 <> null results in UNKNOWN.

Nulls in Conditions

A condition that evaluates to UNKNOWN acts almost like FALSE. For example, a SELECT statement with a condition in the WHERE clause that evaluates to UNKNOWN returns no rows. However, a condition evaluating to UNKNOWN differs from FALSE in that further operations on an UNKNOWN condition evaluation will evaluate to UNKNOWN. Thus, NOT FALSE evaluates to TRUE, but NOT UNKNOWN evaluates to UNKNOWN.

Ref.

Upvotes: 5

Bohemian
Bohemian

Reputation: 424983

You need to use an OUTER JOIN:

SELECT p.priv_no FROM osqs_privileges p,osqs_users_privileges up 
WHERE up.priv_no = p.priv_no AND up.user_no = 54 AND up."GRANT" = 'Y' 
    UNION 
SELECT p.priv_no 
FROM osqs_privileges p
LEFT JOIN osqs_groups_privileges gp on osqs_users_groups ug on gp.priv_no = p.priv_no
LEFT JOIN osqs_users_privileges up on ug.grp_no = gp.grp_no
WHERE ug.user_no = 54 
AND gp.priv_no NOT IN 
    (SELECT priv_no FROM osqs_users_privileges WHERE user_no = 54 AND "GRANT" = 'N');

Upvotes: 1

Related Questions