NOT IN clause not working when used with GROUP BY clause

sagar2911 · May 2018

Hello,

I am new to Vertica DB (and the forum), and I am stuck at resolving a query issue. I am using NOT IN clause in my query; however, the results still have the records which should have been excluded because of the NOT IN clause.

For e.g.:
select * from employee where employee_id NOT IN (105,107) ==> This query works fine

select employee_name, employee_id, employee_location, employee_manager, count(*) "leaves" from employee where employee_id NOT IN (105,107) GROUP BY employee_name, employee_id, employee_location, employee_manager
==> This query returns results which has employee_id = 105 and 107

I am not sure why the NOT IN clause is only working when I'm not using it in a GROUP BY clause.

Any pointers would be really helpful. Thank you.

Jim_Knicely · May 2018

Hi,

Welcome to the Forums!

What version of Vertica are you using?

The WHERE clause is applied prior to the GROUP BY so should not get employee id's 105 and 107 in your results.

A quick test show this:

dbadmin=> select * from employee;
 employee_name | employee_id | employee_location | employee_manager
---------------+-------------+-------------------+------------------
 Jane          |         105 | 1                 | 1
 Jim           |         101 | 1                 | 1
 Josh          |         107 | 2                 | 1
(3 rows)

dbadmin=> select * from employee where employee_id NOT IN (105,107);
 employee_name | employee_id | employee_location | employee_manager
---------------+-------------+-------------------+------------------
 Jim           |         101 | 1                 | 1
(1 row)

dbadmin=> select employee_name, employee_id, employee_location, employee_manager, count(*) "leaves" from employee where employee_id NOT IN (105,107) GROUP BY employee_name, employee_id, employee_location, employee_manager;
 employee_name | employee_id | employee_location | employee_manager | leaves
---------------+-------------+-------------------+------------------+--------
 Jim           |         101 | 1                 | 1                |      1
(1 row)

Can you provide the DDL for your table and some sample data that is in question?

You can get the DDL by running this:

select export_objects('','employee');

Thanks!

NOT IN clause not working when used with GROUP BY clause

Answers

Leave a Comment