CODEWITHSUNDEEP
mysqlsql
coder101
coder101

Reputation: 1605

mysql multiple NOT in where condition based on result from another query

I have a situation where in i need to pull data from one table but exclude some rows based on the rows in another table. I mean that i need to pull studentid(s) from one table but exclude those studentid(s) which are there in another table.

first query :

$sql = "select studentid from table 2 where iarsid = '12'";

as i'll get an array result from this query i want to use this result and put it in NOT conditions in the next query simply excluding these very rows from the result from this another query.

Second query:

$sql2 = "select studentid from table 2, table 3 where iarsid = '12' // and a lot of joins";

Basically the students who are in the first table are not needed while pulling out students based on the second query. If i am using the wrong logic, please guide so as to achieve this.

Upvotes: 0

Views: 4782

Answers (3)

bonCodigo
bonCodigo

Reputation: 14361

I can see that you have accepted an answer. But you can also do this. The best way to check which query is fast by checking your Explain Plan.

 SELECT student_name
 FROM table_A a
 WHERE a.student_id NOT EXISTS (SELECT student_id FROM table_B b)

Since this is an un-correalted query using exists, this will be fater for a larger table. And IN will be faster for a small table. The reason it's faster the moment it finds no match, it will return a false instead IN will do a full table scan.

Also this one:

 SELECT student_name
 FROM table_A a
 WHERE NOT EXISTS (SELECT null 
                   FROM table_B b
                   WHERE a.studentid = b.studentid);

Upvotes: 1

DWright
DWright

Reputation: 9500

You can do the general idea at least 3 ways, using a LEFT JOIN, and also using NOT IN and NOT EXISTS.

Via LEFT JOINS.

SELECT student_name
FROM table_A a
LEFT JOIN table_B b ON a.student_id = b.student_id
WHERE b.student_id IS NULL

This gets all student information in table_A, where the student is not in table_B.

and here it is via NOT EXISTS:

 SELECT student_name
 FROM table_A a
 WHERE NOT EXISTS (SELECT student_id FROM table_B b WHERE b.student_id = a.student_id)

and via NOT IN

 SELECT student_name
 FROM table_A a
 WHERE a.student_id NOT IN (SELECT student_id FROM table_B b)

Upvotes: 3

benbai123
benbai123

Reputation: 1463

Do you mean second query that use the first query as a condition with NOT?

"select studentid from table 2, table 3 where iarsid = '12' // and a lot of joins"
+ " WHERE studentid NOT IN (select studentid from table 2 where iarsid = '12')"

Upvotes: 1

Related Questions