01

Select the rows from the table you want in the in new row

SELECT employee.emp_id, employee.first_name, 
branch.branch_name
from employee
JOIN branch
ON employee.emp_id = branch.mgr_id;

This is a 'inner join' which combines the rows from the specified columns

02

The LEFT join includes all of the rows from the columns specified in select, the first table FROM in our case employee.

SELECT employee.emp_id, employee.first_name, branch.branch_name
FROM employee
LEFT JOIN branch
ON employee.emp_id = branch.mgr_id;

Notice how each employee is displayed, because they all have an emp_id and first_name

But, notice how only the branch columns meeting the condition in ON are displayed

03

Using right join will display only the columns from the 2nd table, branch, that meet the condition in ON, which occurs with the inner join as well

select employee.emp_id, employee.first_name, branch.branch_name
FROM employee
RIGHT JOIN branch
on employee.emp_id = branch.mgr_id;

04

Insert a new row in the branch table

insert into branch VALUES(4,'Buffalo',NULL,NULL);

Keep mgr_id and mgr_start_date NULL

Then re-run the RIGHT JOIN code from above

select employee.emp_id, employee.first_name, branch.branch_name
from employee
RIGHT JOIN branch
ON employee.emp_id = branch.mgr_id;

see how the new column we displayed, that doesn't meet the condition in ON. So everything get displayed from the 2nd table branch