Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Not in Subquery

I am practising SQL on leetcode (Using MySQL) and came across the following problem – where I am confused on why my solution does not work.

The following is an employee table where it shows which department(s) an employee is in and which one is their primary department. If an employee only belongs to one department id, their primary flag will show "N" (even though that is their primary department as its their only one) and if they have multiple departments, it will flag "Y" or "N" to show which one is primary. The data for the test case is just what the below shows

Input:

Employee table:

employee_id department_id primary_flag
1 1 N
2 1 Y
2 2 N
3 3 N
4 2 N
4 3 Y
4 4 N

Output:

employee_id department_id
1 1
2 1
3 3
4 3

Explanation:

  • The Primary department for employee 1 is 1.
  • The Primary department for employee 2 is 1.
  • The Primary department for employee 3 is 3.
  • The Primary department for employee 4 is 3.

Here is my solution, where nothing is returned. Why does this not work. The expected output should be that from the output table above but this query returns nothing. There are no null values and when I run the subquery by itself, it shows the employee_id, department_ids that should not be in the final output. So am trying to see why this does not work.

select employee_id, department_id 
from Employee
WHERE (employee_id, department_id) not in(
    select employee_id, department_id 
    from (
        SELECT employee_id, department_id, primary_flag,
            count(employee_id) over (partition by employee_id) as cnt
        from employee
    ) a 
    WHERE cnt > 1 and primary_flag = 'n'
);

I am not looking for an alternative method, as I know how to get the solution in other ways. I am simply wondering why this specific solution does not work – just for my own deep understanding.

enter image description here

From Joel Coehoorn’s comments below – he was able to produce the same results, however it does not work for me in LeetCode. Perhaps it is an issue on their end (?)

Tags:

2

Answers


  1. 0

    I combined Employee and Department together

    CREATE TABLE [Employee] 
(
    [employee_id]   INT,
    [department_id] VARCHAR(512),
    [primary_flag]  VARCHAR(512)
);

INSERT INTO [Employee] ([employee_id], [department_id], [primary_flag]) VALUES
    ('1', '1', 'N'),
    ('2', '1', 'Y'),
    ('2', '2', 'N'),
    ('3', '3', 'N'),
    ('4', '2', 'N'),
    ('4', '3', 'Y'),
    ('4', '4', 'N');

Select * from Employee

select employee_id, department_id 
from Employee
WHERE cast(employee_id as varchar(10)) + '|' + cast(department_id as varchar(10)) not in(
    select cast(employee_id as varchar(10)) + '|' + cast(department_id as varchar(10))  
    from (
        SELECT employee_id, department_id, primary_flag,
            count(employee_id) over (partition by employee_id) as cnt
        from employee
    ) a 
    WHERE cnt > 1 and primary_flag = 'n'
);

    Fiddle

    employee_id department_id
    1 1
    2 1
    3 3
    4 3
    Login or Signup to reply.
  2. 0

    Your query is much much too complicated. This doesn’t need any subqueries or windows functions at all.

    All you need is some conditional aggregation.

    SELECT
  e.employee_id,
  CASE WHEN COUNT(*) = 1
       THEN MIN(e.department_id)
       ELSE MIN(CASE WHEN e.primary_flag = 'Y' THEN e.department_id END)
  END AS department_id
FROM Employee e
GROUP BY
  e.employee_id;

    db<>fiddle

    Your existing query has multiple issues:

    • Because the values might have a null, MySQL evaluates the NOT IN clause differently. You can see this in action in this fiddle.
      Generally you should avoid NOT IN, it can always be rewritten as a NOT EXISTS.
    • Row comparators don’t work in all DBMSs.
    • In any case you could have simplified out and removed the self-join. 
      select employee_id, department_id 
from (
    SELECT employee_id, department_id, primary_flag,
        count(employee_id) over (partition by employee_id) as cnt
    from Employee
) a 
WHERE cnt = 1 or primary_flag = 'y';

      Obviously it’s not necessary to use window functions anyway, as I mentioned earlier.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search