I’ve got a question which is like: The employees who have the same branch are colleagues of each other. You need to write a query that can give you the colleagues of EmpId ‘x’ and the list of the colleagues should not include the user ‘x’ himself. For eg:- If x = 4 then the result should be EmpId 1 and 5.
Note: You have to write one line query which should not include any nested or sub-query.
Below are my answers but I’m worried about the cost of Bytes in both scenarios.
1. Answer using simple one-line query would be:
SELECT e2.empid FROM emp e1, emp e2 WHERE e1.empid = 4 AND e1.branch = e2.branch AND e1.empid != e2.empid;
2. Answer using sub-query would be:
SELECT empId FROM emp WHERE branch =(SELECT branch FROM emp WHERE empId =4) and empId !=4;