0

I have listed two different solutions to the given problem. is there any better approach to solve these types of problems?

Q: GIVEN EMPLOYEE TABLE below, SELECT name whose id = 1 but not = 3

-- ID   NAME
-- 1    ram
-- 2    shayam
-- 1    mohan
-- 7    mohan
-- 4    monu
-- 3    monu
-- 1    monu
-- 5    sonu
-- 1    sonu
-- 2    sonu


-- OUTPUT
-- mohan
-- ram
-- sonu

-- Solution 1:
SELECT DISTINCT(e1.NAME) FROM EMPLOYEE e1 JOIN EMPLOYEE e2 ON e1.name = e2.name WHERE e1.id = 1 AND e1.NAME NOT IN (
SELECT DISTINCT(e1.NAME) FROM EMPLOYEE e1 JOIN EMPLOYEE e2 ON e1.name = e2.name WHERE  e2.id = 3);

-- Solution 2:
SELECT DISTINCT(e1.NAME) FROM EMPLOYEE e1 JOIN EMPLOYEE e2 ON e1.name = e2.name WHERE e1.id = 1 
MINUS
SELECT DISTINCT(e1.NAME) FROM EMPLOYEE e1 JOIN EMPLOYEE e2 ON e1.name = e2.name WHERE  e2.id = 3;

-- Use this code to test the logic:
CREATE TABLE EMPLOYEE( id INT, name VARCHAR(25) ); 
INSERT INTO EMPLOYEE(id, name) VALUES(1, 'ram'); 
INSERT INTO EMPLOYEE(id, name) VALUES(2, 'shayam'); 
INSERT INTO EMPLOYEE(id, name) VALUES(1, 'mohan'); 
INSERT INTO EMPLOYEE(id, name) VALUES(7, 'mohan'); 
INSERT INTO EMPLOYEE(id, name) VALUES(4, 'monu'); 
INSERT INTO EMPLOYEE(id, name) VALUES(3, 'monu'); 
INSERT INTO EMPLOYEE(id, name) VALUES(1, 'monu'); 
INSERT INTO EMPLOYEE(id, name) VALUES(5, 'sonu'); 
INSERT INTO EMPLOYEE(id, name) VALUES(1, 'sonu');
INSERT INTO EMPLOYEE(id, name) VALUES(2, 'sonu');

SELECT * FROM EMPLOYEE;
Aman Ranjan Verma
  • 183
  • 1
  • 10
  • 1
    `minus` is fine. However, that set function is database-dependent. – Gordon Linoff Jul 29 '21 at 11:24
  • 1
    Unrelated to your problem, but: `distinct` is **not** a function. It always applies to all columns in the select list. Enclosing one of the columns with parentheses won't change anything and is useless. `distinct (a),b` is the same as `distinct a,(b)` or `distinct a,b` –  Jul 29 '21 at 11:25
  • Your schema appears to be broken. If this is a list of employees, then the `id` column should be unique. If it's another table that associates employees to some other entity, such as skills the have or departments they work in, then the table should be named as such and the `id` column should be `skill_id` or similar, and there should be a separate employee table. – MatBailie Jul 29 '21 at 11:36
  • Thanks, @MatBailie , but please don't go into that detail, I have tried to give a dummy table to understand the type of problem I am trying to solve. – Aman Ranjan Verma Jul 29 '21 at 11:49
  • Thanks, @a_horse_with_no_name, really informative information. Appreciate it. – Aman Ranjan Verma Jul 29 '21 at 11:50
  • 1
    Here is another solution by using sub-queries `SELECT name FROM EMPLOYEE WHERE id = 1 and name NOT IN( SELECT name FROM EMPLOYEE WHERE id = 3 )` – rootkit7628 Jul 29 '21 at 11:53
  • SELECT E1.name from EMPLOYEE E1 left join EMPLOYEE E2 on (E1.name = E2.name and E2.id = 3) WHERE E1.id = 1 and E2.id IS NULL; – Aman Ranjan Verma Jul 29 '21 at 11:58

1 Answers1

1

The minus is fine, but you don't need the select distinct. Minus is a set function that only returns distinct rows. I tend to use aggregation for this:

select e.name
from employee e
where id in (1, 3)
group by e.name
having max(id) = 1;   -- there is no 3 if the max is 1

However, your methods are basically fine although I'll repeat that the select distincts are not necessary.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786