MySQL query to find out all employees even when an employee is not tagged to any department

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.

Leave a Reply

Your email address will not be published. Required fields are marked *