hash, nested and merge join

---nested loop  join ---
Nested Loops” operator basically does is: For each record from the outer input – find matching rows from the inner input.
Technically, this means that the clustered index scan you see as the outer input is executed once to get all the relevant records,
and the clustered index seek you see below it is executed for each record from the outer input.

SELECT
OC.first_name, OH.department_id
FROM
employees OC
JOIN
departments OH
ON
OH.department_id = OC.department_id;
--- merge join ----
SELECT
OC.first_name, OH.department_id
FROM
employees OC
JOIN
departments OH
ON
OH.department_id = OC.department_id
where OH.department_id  between 10 and 50;

--- Hash join ----
SELECT
OC.first_name, OH.department_id
FROM
employees OC
JOIN
departments OH
ON
OH.department_id = OC.department_id
where OC.first_name like '%S%';