Leetcode 1965 finding missing information

Question

Anybody can help me out for leetcode 1965 finding missing information. the question is as below :

Input: Employees table: +-------------+----------+ | employee_id | name | +-------------+----------+ | 2 | Crew | | 4 | Haven | | 5 | Kristian | +-------------+----------+ Salaries table: +-------------+--------+ | employee_id | salary | +-------------+--------+ | 5 | 76071 | | 1 | 22517 | | 4 | 63539 | +-------------+--------+ Output: +-------------+ | employee_id | +-------------+ | 1 | | 2 | +-------------+ Explanation: Employees 1, 2, 4, and 5 are working at this company. The name of employee 1 is missing. The salary of employee 2 is missing.

My solution is as below: I don't know what's the mistake.

select employee_id
from Employees
outer join salaries on Employees.Employee_id=salaries.Employee_id
where name is null or salary is null;

It mentioned I have an error for syntax.

Tyler2P · Accepted Answer · 2022-05-21 10:29:52Z

1

You can try this:

select 
  employee_id
from
  Employees
where
  employee_id not in (select employee_id from Salaries)
union

select
  employee_id
from
  Salaries
where
  employee_id not in (select employee_id from Employees)
order by employee_id;

or

(SELECT 
    Employees.employee_id 
FROM 
    Employees LEFT JOIN Salaries 
ON 
    Employees.employee_id = Salaries.employee_id 
WHERE 
    Salaries.employee_id IS NULL 
UNION 
SELECT 
    Salaries.employee_id 
FROM 
    Salaries LEFT JOIN Employees 
ON 
    Employees.employee_id = Salaries.employee_id 
WHERE 
    Employees.employee_id IS NULL) 
ORDER BY 
    employee_id;

edited May 21, 2022 at 10:29

Tyler2P

2,37030 gold badges26 silver badges34 bronze badges

answered May 18, 2022 at 17:27

Pranav Sagar

112 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tyler2P Over a year ago

Your answer could be improved by adding more information on what the code does and how it helps the OP.

Golam Rabbani · Accepted Answer · 2022-05-29 10:52:59Z

0

You can try this:

SELECT employee_id
FROM
( SELECT employee_id FROM employee
UNION ALL 
SELECT employee_id FROM salaries
)emp
GROUP BY employee_id
HAVING COUNT(employee_id)=1
ORDER BY 1;

answered May 29, 2022 at 10:52

Golam Rabbani

212 bronze badges

Comments

luffy · Accepted Answer · 2022-06-27 16:54:05Z

0

select case when t1.employee_id is not null then t1.employee_id else t2.employee_id end as employee_id from employees t1
full outer join salaries t2 on t1.employee_id = t2.employee_id where name is null or salary is null order by employee_id asc

answered Jun 27, 2022 at 16:54

luffy

1

1 Comment

Community Over a year ago

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.

Jeremy Caney · Accepted Answer · 2022-07-22 00:47:23Z

0

The easiest query is below:

SELECT      employee_id 
FROM        employees 
WHERE       employee_id 
  NOT IN(
    SELECT  EMPLOYEE_ID 
    FROM    SALARIES
  )
UNION
SELECT      employee_id 
FROM        SALARIES 
WHERE       employee_id 
  NOT IN(
    SELECT  EMPLOYEE_ID 
    FROM    EMPLOYEES
  )
ORDER BY    EMPLOYEE_ID;

edited Jul 22, 2022 at 0:47

Jeremy Caney

7,808115 gold badges58 silver badges86 bronze badges

answered Jul 19, 2022 at 7:34

Leeladhar Thada

11 bronze badge

Comments

Ekram Mohamed · Accepted Answer · 2022-08-07 20:57:24Z

0

the performance of join is better than subquery

SELECT ISNULL(e.employee_id, s.employee_id) AS employee_id 
FROM Employees as e
FULL OUTER JOIN salaries s ON e.employee_id = s.employee_id
WHERE e.employee_id IS NULL OR s.employee_id IS NULL
ORDER BY employee_id

answered Aug 7, 2022 at 20:57

Ekram Mohamed

96 bronze badges

Comments

Aman Kumar Singh · Accepted Answer · 2023-02-07 06:41:40Z

0

You can try this

SELECT employee_id FROM 
(select employee_id from employees union select employee_id from salaries) t
  WHERE employee_id IN
(
  SELECT CONCAT(e.employee_id,s.employee_id) AS employee_id FROM Employees e 
   FULL JOIN Salaries s 
  ON e.employee_id=s.employee_id 
  WHERE e.name IS NULL OR s.salary IS NULL
)
 ORDER BY employee_id;

answered Feb 7, 2023 at 6:41

Aman Kumar Singh

31 silver badge3 bronze badges

1 Comment

Community Over a year ago

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.

Suraj Rao · Accepted Answer · 2023-02-24 11:26:06Z

0

SQL Server solution

SELECT concat(e.employee_id, s.employee_id) AS employee_id
FROM Employees AS e
FULL JOIN Salaries AS s
ON e.employee_id = s.employee_id
WHERE name IS NULL OR salary IS NULL
ORDER BY 1 ASC;

edited Feb 24, 2023 at 11:26

Suraj Rao

29.7k11 gold badges96 silver badges104 bronze badges

answered Feb 24, 2023 at 11:24

kleaM

1

Collectives™ on Stack Overflow

Leetcode 1965 finding missing information

7 Answers 7

1 Comment

Comments

1 Comment

Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

1 Comment

Comments

1 Comment

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related