Leaf-Level Employees (Employees with No Subordinates), Correlated Subquery Syntax

 

5>
6>
7>
8> CREATE TABLE Employees
9> (
10>   empid   int         NOT NULL,
11>   mgrid   int         NULL,
12>   empname varchar(25) NOT NULL,
13>   salary money        NOT NULL)
14> 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>
16>
17> SELECT *
18> FROM
19>   Employees AS M
20> WHERE
21>   NOT EXISTS
22>              (
23>              SELECT
24>                empid
25>               FROM
26>                Employees AS E
27>               WHERE
28>                 E.mgrid = M.empid
29>              )
30> 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)
empid       mgrid       empname                   salary
----------- ----------- ------------------------- ---------------------
          5           2 Steven                                2500.0000
          6           2 Michael                               2500.0000
          8           3 Laura                                 2500.0000
          9           3 Ann                                   2500.0000
         10           4 Ina                                   2500.0000
         12           7 Ron                                   2000.0000
         13           7 Dan                                   2000.0000
         14          11 James                                 1500.0000

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

Related examples in the same category

1.	WHERE clause in the subquery's SELECT statement links the inner query to the outer query
2.	Correlated subquery using the department table in both inner and outer queries
3.	Correlated subquery: the inner query depends on the outer query for any of its values
4.	Correlated subquery using Distinct
5.	Leaf-Level Employees (Employees with No Subordinates), Join Syntax

Leaf-Level Employees (Employees with No Subordinates), Correlated Subquery Syntax : Correlated subquery « Subquery « SQL Server / T-SQL

Related examples in the same category