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   |
| Brad  | null  |
+-------+-------+

Solution

Approach: UsingOUTER JOINandWHEREclause [Accepted]

Intuition

Join tableEmployeewithBonusand then useWHEREclause to get the required records.

Algorithm

Since foreign keyBonus.empIdrefers toEmployee.empIdand some employees do not have bonus records, we can useOUTER JOINto link these two tables as the first step.

SELECT Employee.name, Bonus.bonus
from Employee 
LEFT JOIN Bonus on Employee.empid = Bonus.empid;

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

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

| name   | bonus |
|--------|-------|
| Dan    | 500   |
| Thomas | 2000  |
| Brad   |       |
| John   |       |

The bonus value for 'Brad' and 'John' is empty, which is actuallyNULLin the database. "Conceptually, NULL means “a missing unknown value” and it is treated somewhat differently from other values." Check theWorking with NULL Valuesin MySQL manual for more details. In addition, we have to useIS NULLorIS NOT NULLto compare a value withNULL.

At last, we can add aWHEREclause with the proper conditions to filter these records.

MySQL

SELECT Employee.name, Bonus.bonus
from Employee 
LEFT JOIN Bonus on Employee.empid = Bonus.empid
where bonus < 1000 OR bonus IS NULL;