MySQL subquery - Difference in EXISTS NOT EXISTS, IN NOT IN

Table master_a	Table master
id ↓ name ↓ 1 Charlie 2 David 3 Joomla	id ↓ name ↓ 2 David 3 Andy

========================================

 (where master_a.name = master.name)

========================================

---------------------------------

USING Where Clause

---------------------------------

select * from master_a
where exists (
select * from master where master_a.name = master.name
);

---------------------------------

select * from master_a
where master_a.name in (
select master.name from master where master_a.name = master.name
);

---------------------------------

NOT USING Where Clause

---------------------------------

select * from master_a
where master_a.name in (
select master.name from master
);

---------------------------------

Result ：

---------------------------------

id	name
2	David

PS. SAME AS>

select * from master_a left join master on master_a.name = master.name
where master.id is not NULL;

========================================

 (where master_a.id= master.id)

========================================

select * from master_a
where exists(
select * from master where master_a.id = master.id
);

---------------------------------

select * from master_a
where master_a.id in (
select master.id from master where master_a.id = master.id
)

---------------------------------

Result ：

---------------------------------

id	name
2	David
3	Joomla

========================================
※ In,and Not In ---> Can be defferent condition
========================================

select * from master_a
where master_a.name in (
select master.name from master where master_a.id = master.id
);

---------------------------------

Result ：

---------------------------------


id
name

2
David

========================================
※Wrong Usage ("exists,not exists" must have a "where" clause)
========================================

select * from master_a
where exists (
select * from master
)

id	name
1	Charlie
2	David
3	Joomla

===================================================

Ref.http://www.nowamagic.net/librarys/veda/detail/639