2 tables: `employee` & `employee_address` as an example

three indexes are provided:

```
ALTER TABLE employee ADD INDEX `E_P` (`position`) USING BTREE;

ALTER TABLE employee_address ADD INDEX `EA_Z` (`zipcode`) USING BTREE;

ALTER TABLE employee_address ADD INDEX `EA_E` (`employee_id`) USING BTREE;

```

1st inner join

```
select * from employee_address ea
inner join employee e on e.employee_id = ea.employee_id and e.position = 'MANAGER'
where ea.zipcode > 30000

```

2nd inner join with where clause

```
select * from employee_address ea
inner join (
select * from employee e where e.position = 'MANAGER'
) e on e.employee_id = ea.employee_id 
where ea.zipcode > 30000

```

assume there are :
500000 records in each table
1000 different position
2000 different zipcode

I found query 1 is much more efficient.

What's different between this 2 queries?

Would it be possible to query it as fast as they were in just one table?
and How?

```
select * from employee e where e.position = 'MANAGER' 
and e.zipcode > 30000

ALTER TABLE employee ADD INDEX `Z_P` (`zipcode`, `position`) USING BTREE;

```