## 577. Employee Bonus

Select all employee's name and bonus whose bonus is < 1000.

Table:`Employee `

```+-------+--------+-----------+--------+
| empId |  name  | supervisor| salary |
+-------+--------+-----------+--------+
|   1   | John   |  3        | 1000   |
|   2   | Dan    |  3        | 2000   |
|   3   | Brad   |  null     | 4000   |
|   4   | Thomas |  3        | 4000   |
+-------+--------+-----------+--------+
empId is the primary key column for this table.
```

Table: `Bonus`

```+-------+-------+
| empId | bonus |
+-------+-------+
| 2     | 500   |
| 4     | 2000  |
+-------+-------+
empId is the primary key column for this table.
```

Example ouput:

```+-------+-------+
| name  | bonus |
+-------+-------+
| John  | null  |
| Dan   | 500   |
+-------+-------+
```

b'
\n\n

## Solution

\n
\n

#### Approach: Using `OUTER JOIN` and `WHERE` clause [Accepted]

\n

Intuition

\n

Join table Employee with Bonus and then use `WHERE` clause to get the required records.

\n

Algorithm

\n

Since foreign key Bonus.empId refers to Employee.empId and some employees do not have bonus records, we can use `OUTER JOIN` to link these two tables as the first step.

\n
`SELECT\n    Employee.name, Bonus.bonus\nFROM\n    Employee\n        LEFT OUTER JOIN\n    Bonus ON Employee.empid = Bonus.empid\n;\n`
\n
\n

Note: "LEFT OUTER JOIN" could be written as "LEFT JOIN".

\n
\n

The output to run this code with the sample data is as below.

\n
`| name   | bonus |\n|--------|-------|\n| Dan    | 500   |\n| Thomas | 2000  |\n| Brad   |       |\n| John   |       |\n`
\n

The bonus value for \'Brad\' and \'John\' is empty, which is actually `NULL` in the database. "Conceptually, NULL means \xe2\x80\x9ca missing unknown value\xe2\x80\x9d and it is treated somewhat differently from other values." Check the Working with NULL Values in MySQL manual for more details. In addition, we have to use `IS NULL` or `IS NOT NULL` to compare a value with `NULL`.

\n

At last, we can add a `WHERE` clause with the proper conditions to filter these records.

\n

MySQL

\n
`SELECT\n    Employee.name, Bonus.bonus\nFROM\n    Employee\n        LEFT JOIN\n    Bonus ON Employee.empid = Bonus.empid\nWHERE\n    bonus < 1000 OR bonus IS NULL\n;\n`
\n
'