How to search for what is missing with MS SQL Server
Summary
It's very easy to find 'what's there', such as what are the employees in a department.
DEPTNO DEPTNAME MGRNO EMPNO FIRSTNME LASTNAME -------------------------------------------------------------------------- A00 SPIFFY COMPUTER SERVICE DIV. 000010 000010 CHRISTINE HAAS A00 SPIFFY COMPUTER SERVICE DIV. 000010 000110 VINCENZO LUCCHESSI A00 SPIFFY COMPUTER SERVICE DIV. 000010 000120 SEAN O'CONNELL C01 INFORMATION CENTER 000030 000030 SALLY KWAN C01 INFORMATION CENTER 000030 000130 DOLORES QUINTANA C01 INFORMATION CENTER 000030 000140 HEATHER NICHOLLS
But what about: what are the departments without any employee? What's missing? MS SQL Server provides an easy solution.
Data used
Department info
- SELECT Deptno, DeptName, MgrNo, AdmrDept
- FROM department;
Deptno DeptName MgrNo AdmrDept ------ ------------------------------------ ------ -------- A00 SPIFFY COMPUTER SERVICE DIV. 000010 A00 B01 PLANNING 000020 A00 C01 INFORMATION CENTER 000030 A00 D01 DEVELOPMENT CENTER NULL A00 D11 MANUFACTURING SYSTEMS 000060 D01 D21 ADMINISTRATION SYSTEMS 000070 D01 E01 SUPPORT SERVICES 000050 A00 E11 OPERATIONS 000090 E01 E21 SOFTWARE SUPPORT 000100 E01 (9 row(s) affected)
Employee info
- SELECT empno, firstnme, lastname, workdept
- FROM emps
- ORDER BY empno;
empno firstnme lastname workdept ------ ------------ --------------- -------- 000010 CHRISTINE HAAS A00 000020 MICHAEL THOMPSON NULL 000030 SALLY KWAN C01 000050 JOHN GEYER E01 000060 IRVING STERN D11 000070 EVA PULASKI D21 000090 EILEEN HENDERSON E11 000100 THEODORE SPENSER NULL 000110 VINCENZO LUCCHESSI A00 000120 SEAN O CONNELL A00 000130 DOLORES QUINTANA C01 000140 HEATHER NICHOLLS C01 000150 BRUCE ADAMSON NULL 000160 ELIZABETH PIANKA D11 000170 MASATOSHI YOSHIMURA D11 000180 MARILYN SCOUTTEN D11 000190 JAMES WALKER D11 000200 DAVID BROWN D11 000210 WILLIAM JONES D11 000220 JENNIFER LUTZ NULL 000230 JAMES JEFFERSON D21 000240 SALVATORE MARINO D21 000250 DANIEL SMITH D21 000260 SYBIL JOHNSON D21 000270 MARIA PEREZ D21 000280 ETHEL SCHNEIDER E11 000290 JOHN PARKER E11 000300 PHILIP SMITH E11 000310 MAUDE SETRIGHT E11 000320 RAMLAL MEHTA NULL 000330 WING LEE E21 000340 JASON GOUNOT E21 (32 row(s) affected)
4 employees have been hired, but they are not working yet, and don't yet have a department.
What's happening
- SELECT d.deptno, d.deptname, d.mgrno, e.empno, e.firstnme, e.lastname
- FROM department d, emps e
- WHERE d.deptno = e.workdept
- ORDER BY d.deptno, e.empno;
deptno deptname mgrno empno firstnme lastname ------ ------------------------------------ ------ ------ ------------ --------------- A00 SPIFFY COMPUTER SERVICE DIV. 000010 000010 CHRISTINE HAAS A00 SPIFFY COMPUTER SERVICE DIV. 000010 000110 VINCENZO LUCCHESSI A00 SPIFFY COMPUTER SERVICE DIV. 000010 000120 SEAN O CONNELL C01 INFORMATION CENTER 000030 000030 SALLY KWAN C01 INFORMATION CENTER 000030 000130 DOLORES QUINTANA C01 INFORMATION CENTER 000030 000140 HEATHER NICHOLLS D11 MANUFACTURING SYSTEMS 000060 000060 IRVING STERN D11 MANUFACTURING SYSTEMS 000060 000160 ELIZABETH PIANKA D11 MANUFACTURING SYSTEMS 000060 000170 MASATOSHI YOSHIMURA D11 MANUFACTURING SYSTEMS 000060 000180 MARILYN SCOUTTEN D11 MANUFACTURING SYSTEMS 000060 000190 JAMES WALKER D11 MANUFACTURING SYSTEMS 000060 000200 DAVID BROWN D11 MANUFACTURING SYSTEMS 000060 000210 WILLIAM JONES D21 ADMINISTRATION SYSTEMS 000070 000070 EVA PULASKI D21 ADMINISTRATION SYSTEMS 000070 000230 JAMES JEFFERSON D21 ADMINISTRATION SYSTEMS 000070 000240 SALVATORE MARINO D21 ADMINISTRATION SYSTEMS 000070 000250 DANIEL SMITH D21 ADMINISTRATION SYSTEMS 000070 000260 SYBIL JOHNSON D21 ADMINISTRATION SYSTEMS 000070 000270 MARIA PEREZ E01 SUPPORT SERVICES 000050 000050 JOHN GEYER E11 OPERATIONS 000090 000090 EILEEN HENDERSON E11 OPERATIONS 000090 000280 ETHEL SCHNEIDER E11 OPERATIONS 000090 000290 JOHN PARKER E11 OPERATIONS 000090 000300 PHILIP SMITH E11 OPERATIONS 000090 000310 MAUDE SETRIGHT E21 SOFTWARE SUPPORT 000100 000330 WING LEE E21 SOFTWARE SUPPORT 000100 000340 JASON GOUNOT (27 row(s) affected)
- Departments: 9
- Employees: 32
- Employees with department: 27
- Employees without department: 5
What's there
What we are looking for are all the departments that does not have any working employee. MS SQL Server provides a very easy answer with the LEFT OUTER JOIN.
- With MS SQL Server, the LEFT OUTER JOIN means: give me all the rows from the first table [the left] whether there is a child [the second table, the right] or NOT.
- All the rows from the left table will be included.
- SELECT deptno, deptname, empno, firstnme, lastname
- FROM department LEFT outer join emps
- ON (deptno = workdept)
- ORDER BY deptno;
deptno deptname empno firstnme lastname ------ ------------------------------------ ------ ------------ --------------- A00 SPIFFY COMPUTER SERVICE DIV. 000010 CHRISTINE HAAS A00 SPIFFY COMPUTER SERVICE DIV. 000110 VINCENZO LUCCHESSI A00 SPIFFY COMPUTER SERVICE DIV. 000120 SEAN O CONNELL B01 PLANNING NULL NULL NULL C01 INFORMATION CENTER 000030 SALLY KWAN C01 INFORMATION CENTER 000130 DOLORES QUINTANA C01 INFORMATION CENTER 000140 HEATHER NICHOLLS D01 DEVELOPMENT CENTER NULL NULL NULL D11 MANUFACTURING SYSTEMS 000060 IRVING STERN D11 MANUFACTURING SYSTEMS 000160 ELIZABETH PIANKA D11 MANUFACTURING SYSTEMS 000170 MASATOSHI YOSHIMURA D11 MANUFACTURING SYSTEMS 000180 MARILYN SCOUTTEN D11 MANUFACTURING SYSTEMS 000190 JAMES WALKER D11 MANUFACTURING SYSTEMS 000200 DAVID BROWN D11 MANUFACTURING SYSTEMS 000210 WILLIAM JONES D21 ADMINISTRATION SYSTEMS 000070 EVA PULASKI D21 ADMINISTRATION SYSTEMS 000230 JAMES JEFFERSON D21 ADMINISTRATION SYSTEMS 000240 SALVATORE MARINO D21 ADMINISTRATION SYSTEMS 000250 DANIEL SMITH D21 ADMINISTRATION SYSTEMS 000260 SYBIL JOHNSON D21 ADMINISTRATION SYSTEMS 000270 MARIA PEREZ E01 SUPPORT SERVICES 000050 JOHN GEYER E11 OPERATIONS 000090 EILEEN HENDERSON E11 OPERATIONS 000280 ETHEL SCHNEIDER E11 OPERATIONS 000290 JOHN PARKER E11 OPERATIONS 000300 PHILIP SMITH E11 OPERATIONS 000310 MAUDE SETRIGHT E21 SOFTWARE SUPPORT 000330 WING LEE E21 SOFTWARE SUPPORT 000340 JASON GOUNOT (29 row(s) affected)
- This includes B01 and D01 which have no employee assigned to them.
What's missing only
What we are looking for are only the departments that does not have any working employee. It's the same as the previous: What's there but with a where statement of null in the right.
- SELECT deptno, deptname
- FROM department LEFT outer join emps
- ON (deptno = workdept)
- WHERE empno IS null;
deptno deptname ------ ------------------------------------ B01 PLANNING D01 DEVELOPMENT CENTER (2 row(s) affected)
The key to finding what's missing is:
- The LEFT OUTER JOIN for linking the 2 tables.
- The IS NULL for finding out what is not in the child table.
This is often used for:
- What are the customers we have not contacted in the last 3 month...
- What are the employees that we does not have time sheet for...
- What part is missing in this bill of material...