# `IN` and `EXISTS` Examples

+ Does Jupiter have moons? How many moons are there?
```
EXPLAIN SELECT 1
FROM universe.planets p
WHERE EXISTS (SELECT NULL FROM universe.moons
                WHERE planet_id = p.id
                AND p.name = 'Jupiter');

SELECT m.name, m.alias
FROM universe.moons m
WHERE m.planet_id IN (SELECT id FROM universe.planets
                      WHERE name = 'Jupiter');
```

-----------------------------------------------------------------------------------
Reviewed the execution plan and learned that `EXISTS`  it was adopted `semi join`.

```
tidb> EXPLAIN SELECT 1
    -> FROM universe.planets p
    -> WHERE EXISTS (SELECT NULL FROM universe.moons
    ->                 WHERE planet_id = p.id
    ->                 AND p.name = 'Jupiter');
+-------------------------------+---------+-----------+---------------------------+----------------------------------------------------------------------+
| id                            | estRows | task      | access object             | operator info                                                        |
+-------------------------------+---------+-----------+---------------------------+----------------------------------------------------------------------+
| Projection_8                  | 0.01    | root      |                           | 1->Column#32                                                         |
| └─HashJoin_9                  | 0.01    | root      |                           | semi join, equal:[eq(universe.planets.id, universe.moons.planet_id)] |
|   ├─TableReader_13(Build)     | 28.00   | root      |                           | data:TableFullScan_12                                                |
|   │ └─TableFullScan_12        | 28.00   | cop[tikv] | table:moons               | keep order:false, stats:pseudo                                       |
|   └─IndexReader_11(Probe)     | 0.01    | root      |                           | index:IndexRangeScan_10                                              |
|     └─IndexRangeScan_10       | 0.01    | cop[tikv] | table:p, index:name(name) | range:["Jupiter","Jupiter"], keep order:false, stats:pseudo          |
+-------------------------------+---------+-----------+---------------------------+----------------------------------------------------------------------+
6 rows in set (0.00 sec)

tidb> explain SELECT m.name, m.alias
    -> FROM universe.moons m
    -> WHERE EXISTS (SELECT NULL FROM universe.planets 
    ->               WHERE id = m.planet_id
    ->               AND name = 'Jupiter');
+-----------------------------+---------+-----------+---------------------------------+----------------------------------------------------------------------+
| id                          | estRows | task      | access object                   | operator info                                                        |
+-----------------------------+---------+-----------+---------------------------------+----------------------------------------------------------------------+
| HashJoin_20                 | 22.40   | root      |                                 | semi join, equal:[eq(universe.moons.planet_id, universe.planets.id)] |
| ├─IndexReader_24(Build)     | 0.01    | root      |                                 | index:IndexRangeScan_23                                              |
| │ └─IndexRangeScan_23       | 0.01    | cop[tikv] | table:planets, index:name(name) | range:["Jupiter","Jupiter"], keep order:false, stats:pseudo          |
| └─TableReader_22(Probe)     | 28.00   | root      |                                 | data:TableFullScan_21                                                |
|   └─TableFullScan_21        | 28.00   | cop[tikv] | table:m                         | keep order:false, stats:pseudo                                       |
+-----------------------------+---------+-----------+---------------------------------+----------------------------------------------------------------------+
5 rows in set (0.00 sec)
```