Skip to content

Instantly share code, notes, and snippets.

@Kcko

Kcko/join.sql

Last active March 16, 2024 15:26
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save Kcko/9044013b142dd49c31587430687f7327 to your computer and use it in GitHub Desktop.
Save Kcko/9044013b142dd49c31587430687f7327 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
join.sql
/*
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 |
+--------+---------------+-------+----------+--------+-----------+
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment