MySQL IN clause query

Question

Simply, I want to find a solution for testing if one column value exits within another, or not, across tables. I've naturally jumped to 'NOT IN clause' but I've also discovered I can't use a colum name within the bracket (b.full_name)

SELECT 
    * 
FROM 
    tbl1_name a 
INNER JOIN 
    tbl2_name 
    ON a.id = b.id 
WHERE 
    a.name NOT IN (b.full_name);

What clause can I use to achieve what I'm looking for etc.

IN checks if a value exists in a list of other values or fields. This solution would just be checking if a.name does not equal b.full_name. — Kickstart, Feb 03 '15 at 18:00

score 3 · Answer 1 · answered Feb 03 '15 at 15:46

3

You could simply do

SELECT * FROM tbl1_name a
INNER JOIN tbl2_name ON a.id = b.id
WHERE a.name <> b.full_name;

The IN keyword is normally used to check for multiple values example

NOT IN ('A', 'B');

Or

NOT IN (subquery);

answered Feb 03 '15 at 15:46

Jean-François Savard

20,626
7
49
76

@user2505513 Got no feedback from this, did this answer worked for you ? Please mark the answer as accepted if it solved your issue. – Jean-François Savard Feb 13 '15 at 01:01

score 0 · Answer 2 · answered Feb 03 '15 at 15:49

0

You can use subquery after not in clause and select all the names from the table b. Hope it will resolve your problem.

Select * from table1 a inner join table2 b 
on a.id=b.id 
where a.name not in
(Select names from table2)

answered Feb 03 '15 at 15:49

Kushagra Jain

51
1
6

sub queries have to be avoided when possible! why suggest to use them when there are much simpler solutions – giammin Feb 03 '15 at 15:52

score 0 · Answer 3 · answered Feb 03 '15 at 16:13

0

you can perform that using subqueries, this way :

SELECT * FROM tbl1_name a
WHERE a.name IN (SELECT b.full_name FROM tbl2_name b WHERE a.id = b.id);

answered Feb 03 '15 at 16:13

NeoPix

433
5
14

MySQL IN clause query

3 Answers3