/*
https://www.codeproject.com/Articles/231132/Difference-between-And-clause-along-with-on-and-Wh
*/


+--------+---------------+
| DeptId | DeptName      |
+--------+---------------+
|      1 | HR            |
|      2 | Payroll       |
|      3 | Admin         |
|      4 | Marketing     |
|      5 | HR & Accounts |
+--------+---------------+

+-------+----------+--------+-----------+
| EmpId | EmpName  | DeptId | EmpSalary |
+-------+----------+--------+-----------+
|     1 | John     |      1 |   5000.00 |
|     2 | Albert   |      1 |   4500.00 |
|     3 | Crain    |      2 |   6000.00 |
|     4 | Micheal  |      2 |   5000.00 |
|     5 | David    |   NULL |     34.00 |
|     6 | Kelly    |   NULL |    457.00 |
|     7 | Rudy     |      1 |    879.00 |
|     8 | Smith    |      2 |   7878.00 |
|     9 | Karsen   |      5 |    878.00 |
|    10 | Stringer |      5 |    345.00 |
|    11 | Cheryl   |   NULL |      0.00 |
+-------+----------+--------+-----------+


-- 1
select * from employees e
left join departments d on e.deptid = d.deptid;
+-------+----------+--------+-----------+--------+---------------+
| EmpId | EmpName  | DeptId | EmpSalary | DeptId | DeptName      |
+-------+----------+--------+-----------+--------+---------------+
|     1 | John     |      1 |   5000.00 |      1 | HR            |
|     2 | Albert   |      1 |   4500.00 |      1 | HR            |
|     3 | Crain    |      2 |   6000.00 |      2 | Payroll       |
|     4 | Micheal  |      2 |   5000.00 |      2 | Payroll       |
|     5 | David    |   NULL |     34.00 |   NULL | NULL          |
|     6 | Kelly    |   NULL |    457.00 |   NULL | NULL          |
|     7 | Rudy     |      1 |    879.00 |      1 | HR            |
|     8 | Smith    |      2 |   7878.00 |      2 | Payroll       |
|     9 | Karsen   |      5 |    878.00 |      5 | HR & Accounts |
|    10 | Stringer |      5 |    345.00 |      5 | HR & Accounts |
|    11 | Cheryl   |   NULL |      0.00 |   NULL | NULL          |
+-------+----------+--------+-----------+--------+---------------+


-- 2
select * from employees e
left join departments d on e.deptid = d.deptid
and ( d.deptname = 'HR' or d.deptname  = 'HR & Accounts');
+-------+----------+--------+-----------+--------+---------------+
| EmpId | EmpName  | DeptId | EmpSalary | DeptId | DeptName      |
+-------+----------+--------+-----------+--------+---------------+
|     1 | John     |      1 |   5000.00 |      1 | HR            |
|     2 | Albert   |      1 |   4500.00 |      1 | HR            |
|     3 | Crain    |      2 |   6000.00 |   NULL | NULL          |
|     4 | Micheal  |      2 |   5000.00 |   NULL | NULL          |
|     5 | David    |   NULL |     34.00 |   NULL | NULL          |
|     6 | Kelly    |   NULL |    457.00 |   NULL | NULL          |
|     7 | Rudy     |      1 |    879.00 |      1 | HR            |
|     8 | Smith    |      2 |   7878.00 |   NULL | NULL          |
|     9 | Karsen   |      5 |    878.00 |      5 | HR & Accounts |
|    10 | Stringer |      5 |    345.00 |      5 | HR & Accounts |
|    11 | Cheryl   |   NULL |      0.00 |   NULL | NULL          |
+-------+----------+--------+-----------+--------+---------------+


-- 3
select * from employees e
left join departments d on e.deptid = d.deptid
where ( d.deptname = 'HR' or d.deptname  = 'HR & Accounts');
+-------+----------+--------+-----------+--------+---------------+
| EmpId | EmpName  | DeptId | EmpSalary | DeptId | DeptName      |
+-------+----------+--------+-----------+--------+---------------+
|     1 | John     |      1 |   5000.00 |      1 | HR            |
|     2 | Albert   |      1 |   4500.00 |      1 | HR            |
|     7 | Rudy     |      1 |    879.00 |      1 | HR            |
|     9 | Karsen   |      5 |    878.00 |      5 | HR & Accounts |
|    10 | Stringer |      5 |    345.00 |      5 | HR & Accounts |
+-------+----------+--------+-----------+--------+---------------+


-- to same ale z druheho pohledu, slo by resit pres RIGHT JOIN

-- 1
select * from departments d
left join employees e on e.deptId = d.deptId;
+--------+---------------+-------+----------+--------+-----------+
| DeptId | DeptName      | EmpId | EmpName  | DeptId | EmpSalary |
+--------+---------------+-------+----------+--------+-----------+
|      1 | HR            |     1 | John     |      1 |   5000.00 |
|      1 | HR            |     2 | Albert   |      1 |   4500.00 |
|      1 | HR            |     7 | Rudy     |      1 |    879.00 |
|      2 | Payroll       |     3 | Crain    |      2 |   6000.00 |
|      2 | Payroll       |     4 | Micheal  |      2 |   5000.00 |
|      2 | Payroll       |     8 | Smith    |      2 |   7878.00 |
|      3 | Admin         |  NULL | NULL     |   NULL |      NULL |
|      4 | Marketing     |  NULL | NULL     |   NULL |      NULL |
|      5 | HR & Accounts |     9 | Karsen   |      5 |    878.00 |
|      5 | HR & Accounts |    10 | Stringer |      5 |    345.00 |
+--------+---------------+-------+----------+--------+-----------+


-- 2
select * from departments d
left join employees e on e.deptId = d.deptId and
( d.deptname = 'HR' or d.deptname  = 'HR & Accounts');
+--------+---------------+-------+----------+--------+-----------+
| DeptId | DeptName      | EmpId | EmpName  | DeptId | EmpSalary |
+--------+---------------+-------+----------+--------+-----------+
|      1 | HR            |     1 | John     |      1 |   5000.00 |
|      1 | HR            |     2 | Albert   |      1 |   4500.00 |
|      1 | HR            |     7 | Rudy     |      1 |    879.00 |
|      2 | Payroll       |  NULL | NULL     |   NULL |      NULL |
|      3 | Admin         |  NULL | NULL     |   NULL |      NULL |
|      4 | Marketing     |  NULL | NULL     |   NULL |      NULL |
|      5 | HR & Accounts |     9 | Karsen   |      5 |    878.00 |
|      5 | HR & Accounts |    10 | Stringer |      5 |    345.00 |
+--------+---------------+-------+----------+--------+-----------+

-- 3
SELECT * from departments d
left join employees e on e.deptId = d.deptId
where ( d.deptname = 'HR' or d.deptname  = 'HR & Accounts');
+--------+---------------+-------+----------+--------+-----------+
| DeptId | DeptName      | EmpId | EmpName  | DeptId | EmpSalary |
+--------+---------------+-------+----------+--------+-----------+
|      1 | HR            |     1 | John     |      1 |   5000.00 |
|      1 | HR            |     2 | Albert   |      1 |   4500.00 |
|      1 | HR            |     7 | Rudy     |      1 |    879.00 |
|      5 | HR & Accounts |     9 | Karsen   |      5 |    878.00 |
|      5 | HR & Accounts |    10 | Stringer |      5 |    345.00 |
+--------+---------------+-------+----------+--------+-----------+