Employees and Their Managers : LEFT OUTER JOIN « Table Join « SQL Server / T-SQL Tutorial

1. SQL Server / T-SQL Tutorial
2. Table Join
3. LEFT OUTER JOIN

5>
6> CREATE TABLE Employees
7> (
8>   empid   int         NOT NULL,
9>   mgrid   int         NULL,
10>   empname varchar(25) NOT NULL,
11>   salary money        NOT NULL)
12> GO
1> INSERT INTO employees(empid, mgrid, empname, salary) VALUES(1, NULL, 'Nancy', $10000.00)
2> INSERT INTO employees(empid, mgrid, empname, salary) VALUES(2, 1, 'Andrew', $5000.00)
3> INSERT INTO employees(empid, mgrid, empname, salary) VALUES(3, 1, 'Janet', $5000.00)
4> INSERT INTO employees(empid, mgrid, empname, salary) VALUES(4, 1, 'Margaret', $5000.00)
5> INSERT INTO employees(empid, mgrid, empname, salary) VALUES(5, 2, 'Steven', $2500.00)
6> INSERT INTO employees(empid, mgrid, empname, salary) VALUES(6, 2, 'Michael', $2500.00)
7> INSERT INTO employees(empid, mgrid, empname, salary) VALUES(7, 3, 'Robert', $2500.00)
8> INSERT INTO employees(empid, mgrid, empname, salary) VALUES(8, 3, 'Laura', $2500.00)
9> INSERT INTO employees(empid, mgrid, empname, salary) VALUES(9, 3, 'Ann', $2500.00)
10> INSERT INTO employees(empid, mgrid, empname, salary) VALUES(10, 4, 'Ina', $2500.00)
11> INSERT INTO employees(empid, mgrid, empname, salary) VALUES(11, 7, 'David', $2000.00)
12> INSERT INTO employees(empid, mgrid, empname, salary) VALUES(12, 7, 'Ron', $2000.00)
13> INSERT INTO employees(empid, mgrid, empname, salary) VALUES(13, 7, 'Dan', $2000.00)
14> INSERT INTO employees(empid, mgrid, empname, salary) VALUES(14, 11, 'James', $1500.00)
15> GO

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)
1>
2> SELECT
3>   E.empname AS EmployeeName,
4>   M.empname AS ManagerName
5> FROM
6>     Employees AS E
7>   LEFT OUTER JOIN
8>     Employees AS M ON E.mgrid = M.empid;
9> GO
EmployeeName              ManagerName
------------------------- -------------------------
Nancy                     NULL
Andrew                    Nancy
Janet                     Nancy
Margaret                  Nancy
Steven                    Andrew
Michael                   Andrew
Robert                    Janet
Laura                     Janet
Ann                       Janet
Ina                       Margaret
David                     Robert
Ron                       Robert
Dan                       Robert
James                     David

(14 rows affected)
1>
2>
3> drop table Employees;
4> GO
1>
2>