join if a particular key row not found in other key value table

Question

My table structure looks like this.

Table user

user_id | user_name
1       | user1
2       | user2
3       | user3

Table user_key_value

id | user_id | key | value
1  | 1       | k1  | v1
2  | 1       | k2  | v2
3  | 2       | k1  | v1
4  | 2       | k2  | v22
5  | 3       | k1  | v11

One user can have 0 or more key value pairs. Different users can have different values for the same key. I want to find those users who either don't have row with key k2 or if they have then the value should only be v22 i.e. user2 and user3 in the above example. I am unable to come up with a condition that satisfies this case.

I have tried left outer join and where not exists but both give me those users also who have a row with k2 and value not v22 (user1 because it has a row with k1 which satisfies the where not exists condition and is included in the result)

select u.user_id 
from user u
    join user_key_value kv on u.user_id=kv.user_id
    where not exists (select 1 from user_key_value kv1 where kv1.user_id=kv.user_id and kv.key='k2')
    or kv.value = 'v22';

Answer 1

What you need is to filter out users, who have k2 values other than v22. So let's just filter them out with not in or not exists depending on tables' size and indexes on table with key-values (I hope you have no null values for user_id). But with general tables they both will do hash join anti.

select *
from u
where user_id not in (
  select kv.user_id
  from kv
  where k = 'k2'
    and val != 'v22'
)

USER_ID | USER_NAME
------: | :--------
      2 | user2    
      3 | user3    

select * from table(dbms_xplan.display_cursor(format=>'TYPICAL +PREDICATE'));

| PLAN_TABLE_OUTPUT                                                           |
| :-------------------------------------------------------------------------- |
| SQL_ID  gxp71hkxpq7kz, child number 0                                       |
| -------------------------------------                                       |
| select * from u where user_id not in (   select kv.user_id   from kv        |
| where k = 'k2'     and val != 'v22' )                                       |
|                                                                             |
| Plan hash value: 1497905987                                                 |
|                                                                             |
| --------------------------------------------------------------------------- |
| | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     | |
| --------------------------------------------------------------------------- |
| |   0 | SELECT STATEMENT   |      |       |       |     6 (100)|          | |
| |*  1 |  HASH JOIN ANTI NA |      |     1 |    19 |     6   (0)| 00:00:01 | |
| |   2 |   TABLE ACCESS FULL| U    |     3 |    27 |     3   (0)| 00:00:01 | |
| |*  3 |   TABLE ACCESS FULL| KV   |     2 |    20 |     3   (0)| 00:00:01 | |
| --------------------------------------------------------------------------- |
|                                                                             |
| Predicate Information (identified by operation id):                         |
| ---------------------------------------------------                         |
|                                                                             |
|    1 - access("USER_ID"="KV"."USER_ID")                                     |
|    3 - filter(("VAL"<>'v22' AND "K"='k2'))                                  |
|                                                                             |

select *
from u
where not exists (
  select kv.user_id
  from kv
  where k = 'k2'
    and val != 'v22'
    and u.user_id = kv.user_id
)

USER_ID | USER_NAME
------: | :--------
      2 | user2    
      3 | user3    

select * from table(dbms_xplan.display_cursor(format=>'TYPICAL +PREDICATE'));

| PLAN_TABLE_OUTPUT                                                           |
| :-------------------------------------------------------------------------- |
| SQL_ID  3h1mrqrrtgtvp, child number 0                                       |
| -------------------------------------                                       |
| select * from u where not exists (   select kv.user_id   from kv            |
| where k = 'k2'     and val != 'v22'     and u.user_id = kv.user_id )        |
|                                                                             |
| Plan hash value: 3036374083                                                 |
|                                                                             |
| --------------------------------------------------------------------------- |
| | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     | |
| --------------------------------------------------------------------------- |
| |   0 | SELECT STATEMENT   |      |       |       |     6 (100)|          | |
| |*  1 |  HASH JOIN ANTI    |      |     1 |    19 |     6   (0)| 00:00:01 | |
| |   2 |   TABLE ACCESS FULL| U    |     3 |    27 |     3   (0)| 00:00:01 | |
| |*  3 |   TABLE ACCESS FULL| KV   |     2 |    20 |     3   (0)| 00:00:01 | |
| --------------------------------------------------------------------------- |
|                                                                             |
| Predicate Information (identified by operation id):                         |
| ---------------------------------------------------                         |
|                                                                             |
|    1 - access("U"."USER_ID"="KV"."USER_ID")                                 |
|    3 - filter(("VAL"<>'v22' AND "K"='k2'))                                  |
|                                                                             |

db<>fiddle here

Answer 2

You have to first check users who don't have k2 at all .. and in the second step to check those who have that but with a corresponding value.. then you join those together :)

-- user list
WITH w_users AS (
SELECT 1 as user_id, 'user1' as user_name FROM dual
UNION ALL SELECT 2 as user_id, 'user2' as user_name FROM dual
UNION ALL SELECT 3 as user_id, 'user3' as user_name FROM dual
)
-- user key value table
, w_user_kv AS (
SELECT 1 as id, 1 as user_id, 'k1' as key, 'v1' as value FROM dual
UNION ALL SELECT 2, 1, 'k2', 'v2' FROM dual
UNION ALL SELECT 3, 2, 'k1', 'v1' FROM dual
UNION ALL SELECT 4, 2, 'k2', 'v22' FROM dual
UNION ALL SELECT 5, 3, 'k1', 'v11' FROM dual
)


SELECT u.*
, CASE 
   WHEN no_k2.user_id IS NOT NULL THEN 'missing_k2'
   WHEN k2_v22.user_id IS NOT NULL THEN 'k2_v22'
   ELSE null 
  END as u_status
FROM w_users u
LEFT JOIN ( -- those without k2
            SELECT user_id 
            , max(CASE 
               WHEN key = 'k2' THEN 1
               ELSE 0
              END) as has_key_k2
            FROM w_user_kv 
            WHERE 1=1
            GROUP BY user_id
            HAVING max(CASE 
               WHEN key = 'k2' THEN 1
               ELSE 0
              END) = 0 -- no k2
        ) no_k2
ON no_k2.user_id = u.user_id 
LEFT JOIN ( -- those with k2 and v22
        SELECT user_id 
        FROM w_user_kv 
        WHERE 1=1
        AND key = 'k2'
        AND value = 'v22'
        ) k2_v22
ON k2_v22.user_id = u.user_id 
WHERE  1=1
AND ( -- to restrict only to users fulfilling the condition
      no_k2.user_id IS NOT NULL
       OR
      k2_v22.user_id IS NOT NULL
     )

RESULT:

USER_ID | USER_NAME | U_STATUS
-------------------------------
2       | user2     | k2_v22
3       | user3     | missing_k2

Answer 3

Fro your sample data, It seems you need below query -

SELECT user_id
  FROM user_key_value KV
 WHERE NOT EXISTS (SELECT 1
                     FROM user_key_value KV1
                    WHERE KV1.user_id = KV.user_id
                      AND KV1.key='k2')
 UNION
SELECT user_id
  FROM user_key_value KV
 WHERE EXISTS (SELECT 1
                 FROM user_key_value KV1
                WHERE KV1.user_id = KV.user_id
                  AND KV1.key='k2'
                  AND KV1.value = 'v22')