Joins

An SQL JOIN clause is used to combine rows from two or more tables, based on a common field between them.

INNER JOIN: Returns all rows when there is at least one match in BOTH tables
LEFT JOIN: Return all rows from the left table, and the matched rows from the right table
RIGHT JOIN: Return all rows from the right table, and the matched rows from the left table
FULL JOIN: Return all rows when there is a match in ONE of the tables


-- Add the extra branch
INSERT INTO branch VALUES(4, "Buffalo", NULL, NULL);

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

--LEFT JOIN (includes all rows of left table, i.e., table included in FROM statement)
SELECT employee.emp_id, employee.first_name, branch.branch_name
FROM employee
LEFT JOIN branch    
ON employee.emp_id = branch.mgr_id;

--RIGHT JOIN (includes all rows of right table, i.e., "branch")
SELECT employee.emp_id, employee.first_name, branch.branch_name
FROM employee
RIGHT JOIN branch    
ON employee.emp_id = branch.mgr_id;