SQL authoring for seven joins

First, join diagram

Ii. SQL Demo

A. Creating demo tables and data

SET NAMES utf8mb4; SET foreign_key_checks = 0;--------------------------------Table structure for ' tbl_dept '--------------------------- ---DROP TABLE IF EXISTS ' tbl_dept '; CREATE TABLE ' tbl_dept ' (' id ' int (one) not null auto_increment, ' deptname ' varchar () DEFAULT NULL, ' locadd ' varchar (4 0) Default NULL, PRIMARY KEY (' id ')) engine=innodb auto_increment=7 default Charset=utf8;------------------------------  --Records of ' tbl_dept '------------------------------begin;insert into ' tbl_dept ' VALUES (' 1 ', ' RD ', ' One '), (' 2 ', ' HR '), ' 3 ', ' MK ', ' + '), (' 5 ', ' MIS ', ' + '), (' 6 ', ' FD ', ' 15 '); COMMIT;--------------------------------table structure for ' tbl_emp '------------------------------DROP table IF EXISTS ' tbl_emp '; CREATE TABLE ' tbl_emp ' (' id ' int (one) not null auto_increment, ' name ' varchar) DEFAULT NULL, ' deptid ' int (one) defaul T NULL, PRIMARY key (' ID '), key ' fk_dept_id ' (' DeptID ')) Engine=innodb auto_increment=9 DEFAULT Charset=utf8;---------- ----------------------Records of ' tbl_emp '------------------------------begin;insert into ' tbl_emp ' VALUES (' 1 ', ' Z3 ', ' 1 '), (' 2 ', ' Z4 ', ' 1 '), (' 3 ', ' Z5 ', ' 1 '), (' 4 ', ' W5 ', ' 2 '), (' 5 ', ' W6 ', ' 2 '), (' 6 ', ' S7 ', ' 3 '), (' 7 ', ' S8 ', ' 4 '), (' 8 ', ' S9 ', ' 51 '); COMMIT; SET foreign_key_checks = 1;

B. Specific SQL and results

1. Figure I

SELECT * from Tbl_emp a left JOIN tbl_dept b on a.deptid = b.ID;

2. Figure II

SELECT * from Tbl_emp a INNER joins tbl_dept b on a.deptid = b.ID;

3. Figure III

SELECT * from Tbl_emp a right joins tbl_dept b on a.deptid = b.ID;

4. Figure Four

SELECT * from Tbl_emp a left JOIN tbl_dept b to A.deptid = b.id where b.id is null;

5. Figure Five

SELECT * from Tbl_emp a right JOIN tbl_dept b to A.deptid = b.id where a.deptid is null;

6. Figure Six

SELECT * from Tbl_emp a right joins tbl_dept b on a.deptid = B.idunionselect * from Tbl_emp a right joins Tbl_dept B on a.de PtId = b.id;

7. Figure Seven

SELECT * FROM Tbl_emp a left joins tbl_dept b on a.deptid = b.id where b.id are nullunionselect * from Tbl_emp a RIGHT Join Tbl_dept b on a.deptid = b.id where a.deptid is null;

This concludes ...

SQL authoring for seven joins