This tutorial will show how to list all employees even when an employee is not tagged to any department.
Create below Department table in MySQL database
CREATE TABLE `department` ( `dept_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `dept_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL, PRIMARY KEY (`dept_id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Insert data into Department table
insert into `department`(`dept_id`,`dept_name`) values (10,'ACCOUNTING'), (20,'RESEARCH'), (30,'SALES'), (40,'OPERATIONS');
Create below Employee table in MySQL database
CREATE TABLE `employee` ( `emp_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `emp_first_name` varchar(30) COLLATE utf8_unicode_ci NOT NULL, `emp_last_name` varchar(20) COLLATE utf8_unicode_ci NOT NULL, `emp_mgr_id` int(11) DEFAULT NULL, `emp_designation` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL, `dept_id` int(11) DEFAULT NULL, PRIMARY KEY (`emp_id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Insert data into Employee table
insert into `employee`(`emp_id`,`emp_first_name`,`emp_last_name`,`emp_mgr_id`,`emp_designation`,`dept_id`) values (7369,'SMITH','JHON',7902,'CLERK',20), (7499,'ALLEN','BORDER',7698,'SALESMAN',30), (7521,'WARD','SPACE',7698,'SALESMAN',30), (7654,'MARTIN','FOWLER',7698,'SALESMAN',30), (7698,'BLAKE','RAY',NULL,'MANAGER',30), (7782,'CLARK','MICHAEL',NULL,'MANAGER',10), (7788,'SCOTT','TIGER',7566,'ANALYST',20), (7839,'KING','ROY',NULL,'VICE PRESIDENT',10), (7844,'TURNER','RICK',7698,'SALESMAN',30), (7876,'ADAMS','EVE',7788,'CLERK',20), (7900,'JAMES','BOND',7698,'CLERK',30), (7902,'FORD','LAMBDA',7566,'ANALYST',20), (7934,'MILLER','JOHN',7782,'CLERK',10), (7954,'FRANK','JOHN',NULL,'MANAGER',NULL), (7964,'MARTIN','HIKMAN',NULL,'CLERK',NULL), (7974,'APRIL','HICKMAN',NULL,'SALESMAN',NULL);
Query
SELECT e.`emp_id`, e.`emp_first_name`, e.`emp_last_name`, e.`emp_designation`, e.`emp_mgr_id`, d.`dept_name`, d.`dept_id` FROM employee e LEFT JOIN department d ON e.`dept_id`=d.`dept_id`
Output
emp_id emp_first_name emp_last_name emp_designation emp_mgr_id dept_name dept_id 7369 SMITH JHON CLERK 7782 RESEARCH 20 7499 ALLEN BORDER SALESMAN 7698 SALES 30 7521 WARD SPACE SALESMAN 7698 SALES 30 7654 MARTIN FOWLER SALESMAN 7698 SALES 30 7698 BLAKE RAY MANAGER \N SALES 30 7782 CLARK MICHAEL MANAGER \N ACCOUNTING 10 7788 SCOTT TIGER ANALYST 7566 RESEARCH 20 7839 KING ROY VICE PRESIDENT \N ACCOUNTING 10 7844 TURNER RICK SALESMAN 7698 SALES 30 7876 ADAMS EVE CLERK 7788 RESEARCH 20 7900 JAMES BOND CLERK 7698 SALES 30 7902 FORD LAMBDA ANALYST 7782 RESEARCH 20 7934 MILLER JOHN CLERK 7782 ACCOUNTING 10 7954 FRANK JOHN MANAGER \N \N \N 7964 MARTIN HIKMAN CLERK \N \N \N 7974 APRIL HICKMAN SALESMAN \N \N \N
Thanks for reading.