Limitations of Update statement with Co-related Sub Query

When the co-related subquery is used to update a table, the subquery table should have combination for all the key values available in the main table. If the combination is not available in the inner table, then, for the combination, the main table will be updated as NULL for the column we do the update.

Employee
EmpID	EmpName
1	A
2	B
3	C
Sub
EmpID	EmpName
2	D
3	E

Assume, there are two tables one is Employee table which is the main table and the table should be updated with the new value from the table Sub. Below query is written to update the values

UPDATE EMPLOYEE E SET EMPNAME = (SELECT EMPNAME
                                                                             FROM SUB S
                                                                              WHERE E.EMPID = S.EMPID)


The result will be updating the employee IDs 2 and 3 and the employee ID 1 will be updated is null, which is unexpected.

Employee
EmpID	EmpName
1
2	D
3	E

The workaround would be to first insert the keys and values from Employee table to Sub table that are not available in the Sub table and to proceed with the actual update