Mysql - is it possible to return those names from a table that do not have associated any rows in an other table by the id using IN or NOT IN?

deghuy
May 14, 2023
245 views
0 votes
2 Answers

Data base and tables to be used:

DROP DATABASE IF EXISTS employees;
CREATE DATABASE employees CHARACTER SET utf8mb4;
USE employees;

CREATE TABLE department (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  budget DOUBLE UNSIGNED NOT NULL,
  expense DOUBLE UNSIGNED NOT NULL
);

CREATE TABLE employee (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  nif VARCHAR(9) NOT NULL UNIQUE,
  name VARCHAR(100) NOT NULL,
  last_name VARCHAR(100) NOT NULL
  department_id INT UNSIGNED,
  FOREIGN KEY (department_id) REFERENCES department(id)
);

INSERT INTO department VALUES(1, 'Developement', 120000, 6000);
INSERT INTO department VALUES(2, 'Systems', 150000, 21000);
INSERT INTO department VALUES(3, 'Human Resources', 280000, 25000);
INSERT INTO department VALUES(4, 'Accounting', 110000, 3000);
INSERT INTO department VALUES(5, 'R+D', 375000, 380000);
INSERT INTO department VALUES(6, 'Projects', 0, 0);
INSERT INTO department VALUES(7, 'Advertising', 0, 1000);

INSERT INTO employee VALUES(1, '32481596F', 'Wade', 'Smith', 1);
INSERT INTO employee VALUES(2, 'Y5575632D', 'Olivia', 'Jones', 2);
INSERT INTO employee VALUES(3, 'R6970642B', 'Seth', 'Murphy', 3);
INSERT INTO employee VALUES(4, '77705545E', 'Emma', 'Williams', 4);
INSERT INTO employee VALUES(5, '17087203C', 'Riley', 'Brown', 5);
INSERT INTO employee VALUES(6, '38382980M', 'Charlotte', 'Walsh', 1);
INSERT INTO employee VALUES(7, '80576669X', 'Jorge', 'Taylor', 2);
INSERT INTO employee VALUES(8, '71651431Z', 'Amelia', 'Davies', 3);
INSERT INTO employee VALUES(9, '56399183D', 'Brian', 'Miller', 2);
INSERT INTO employee VALUES(10, '46384486H', 'Ava', 'Wilson', 5);
INSERT INTO employee VALUES(11, '67389283A', 'Miles','Byrne', 1);
INSERT INTO employee VALUES(12, '41234836R', 'Sophia', 'Evans', NULL);
INSERT INTO employee VALUES(13, '82635162B', 'Nathaniel','Thomas', NULL);

preconditions: use IN or NOT IN

Expected result: Returns the names of departments that do not have associated employees. (Using IN or NOT IN).

SELECT name
FROM department
WHERE id NOT IN (SELECT DISTINCT id_department FROM employee)

I’ve tried this and it returned an empty set, I know why it happens, but I can’t find a way to with using IN or NOT IN

Tags: mysql sql

Answers

- nbk
- May 12, 2023 at 11:17 am
- 0 votes
0
You have NULL Values, so you have employees that aren’t in any department, but a comparison with NULL isn’t allowed in this form, so you need to eliminate NULL from the inner SELECT
```
SELECT name
FROM department
WHERE id NOT IN (SELECT DISTINCT IFNULL(department_id,0) FROM employee)
```
name

Projects

Advertising

fiddle
Login or Signup to reply.

- SelVazi
- May 12, 2023 at 11:26 am
- 0 votes
0
This is Another way to fix your query is by excluding null values from the department list:
```
SELECT name
FROM department
WHERE id NOT IN (
  SELECT DISTINCT department_id 
  FROM employee
  where department_id > 0  
);
```
Login or Signup to reply.