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 st开发者_开发百科atement (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?

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.

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');

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') ;