MS SQL
Question\n\n1965. Employees With Missing Information\nEasy\n\nSQL Schema\nTable: Employees\n\n+-------------+---------+\n| Column Name | Type    |\n+-----------
#### Question\n```\n1965. Employees With Missing Information\nEasy\n\nSQL Schema\nTable: Employees\n\n+-------------+---------+\n| Column Name | Type    |\n+-------------+---------+\n| employee_id | int     |\n| name        | varchar |\n+-------------+---------+\nemployee_id is the primary key for this table.\nEach row of this table indicates the name of the employee whose ID is employee_id.\n \n\nTable: Salaries\n\n+-------------+---------+\n| Column Name | Type    |\n+-------------+---------+\n| employee_id | int     |\n| salary      | int     |\n+-------------+---------+\nemployee_id is the primary key for this table.\nEach row of this table indicates the salary of the employee whose ID is employee_id.\n \n\nWrite an SQL query to report the IDs of all the employees with missing information. The information of an employee is missing if:\n\nThe employee\'s name is missing, or\nThe employee\'s salary is missing.\nReturn the result table ordered by employee_id in ascending order.\n\nThe query result format is in the following example:\n\n \n\nEmployees table:\n+-------------+----------+\n| employee_id | name     |\n+-------------+----------+\n| 2           | Crew     |\n| 4           | Haven    |\n| 5           | Kristian |\n+-------------+----------+\nSalaries table:\n+-------------+--------+\n| employee_id | salary |\n+-------------+--------+\n| 5           | 76071  |\n| 1           | 22517  |\n| 4           | 63539  |\n+-------------+--------+\n\nResult table:\n+-------------+\n| employee_id |\n+-------------+\n| 1           |\n| 2           |\n+-------------+\n\nEmployees 1, 2, 4, and 5 are working at this company.\nThe name of employee 1 is missing.\nThe salary of employee 2 is missing.\n```\n#### Answer1\n```SQL\nselect case when e.employee_id is NULL then s.employee_id\nelse e.employee_id  end as employee_id FROM Employees e\nFULL OUTER JOIN Salaries s\nON e.employee_id = s.employee_id\nWHERE e.employee_id IS NULL or s.employee_id IS NULL   \norder by employee_id\n```\n#### Answer2\n```SQL\nselect coalesce(e.employee_id,s.employee_id) as employee_id \nFROM Employees e\nFULL OUTER JOIN Salaries s\nON e.employee_id = s.employee_id\nWHERE e.employee_id IS NULL or s.employee_id IS NULL   \norder by employee_id\n\n```
['MS SQL Server']