How not to select values which exist in another table

Question

I have two MySQL tables table1 and table 2.

Table 1:-

batch    semester  scode
IT         6        DA
IT         6        IA
IT         6        FA

Table 2:-

batch    subject   user
IT        DA       1

I want to run a query to get this output:-

scode
IA
FA

So basically I want to retrieve values from table 1 which does not exist in table2 for a user with id 1 and where the batch is IT and the semester is 6. I am new to the concepts of fetching data from two tables so I am unable to think of a way to do this. Any help is highly appreciated.

Answer 1

You can use NOT EXISTS

SELECT 
scode
FROM TABLE1 T1 
WHERE NOT EXISTS (
   SELECT 1 FROM TABLE2 T2 WHERE T1.scode = T2.subject
);

---

SEE DEMO

---

You can use NOT IN

SELECT 
scode
FROM TABLE1 T1 
WHERE T1.scode NOT IN (
   SELECT T2.subject FROM TABLE2 T2 
)

SEE DEMO

---