MySQL 5.7

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE department (id int(11), name varchar(100));
CREATE TABLE employee (id int(11), department_id int(11), chief_id int(11), name varchar(100), salary int(11));

INSERT INTO department (id, name) VALUES
(1, "D1"),
(2, "D2"),
(3, "D3");

INSERT INTO employee (id, department_id, chief_id, name, salary) VALUES
(1, 1, 5, "John", 100),
(2, 1, 5, "Misha", 600),
(3, 2, 5, "Eugen", 300),
(4, 2, 5, "Tolya", 400),
(5, 1, NULL, "Stepan Chief", 500);

SELECT * FROM department;
SELECT * FROM employee;


id
name


1
D1

2
D2

3
D3




id
department_id
chief_id
name
salary


1
1
5
John
100

2
1
5
Misha
600

3
2
5
Eugen
300

4
2
5
Tolya
400

5
1
null
Stepan Chief
500


SELECT department.name as department_name, 
       employee.name
FROM employee 
JOIN department ON employee.department_id = department.id 
WHERE 2 > ( SELECT COUNT(*)
            FROM employee emp
            WHERE emp.department_id = employee.department_id
              AND emp.id < employee.id );
              
EXPLAIN
SELECT department.name as department_name, 
       employee.name
FROM employee 
JOIN department ON employee.department_id = department.id 
WHERE 2 > ( SELECT COUNT(*)
            FROM employee emp
            WHERE emp.department_id = employee.department_id
              AND emp.id < employee.id );


department_name
name


D1
John

D1
Misha

D2
Eugen

D2
Tolya




id
select_type
table
partitions
type
possible_keys
key
key_len
ref
rows
filtered
Extra


1
PRIMARY
department
null
ALL
null
null
null
null
3
100.00
null

1
PRIMARY
employee
null
ALL
null
null
null
null
5
20.00
Using where; Using join buffer (Block Nested Loop)

2
DEPENDENT SUBQUERY
emp
null
ALL
null
null
null
null
5
20.00
Using where


SELECT department.name as department_name, 
       subquery.name
FROM department
JOIN ( SELECT employee.*,
              @row_number := CASE WHEN @department = department_id
                                  THEN @row_number + 1
                                  ELSE 1
                                  END rownumber,
              @department := department_id
       FROM employee
       CROSS JOIN ( SELECT @department:=0, @row_number:=0 ) variables
       ORDER BY department_id, id ) subquery ON subquery.department_id = department.id
WHERE subquery.rownumber <= 2;

EXPLAIN
SELECT department.name as department_name, 
       subquery.name
FROM department
JOIN ( SELECT employee.*,
              @row_number := CASE WHEN @department = department_id
                                  THEN @row_number + 1
                                  ELSE 1
                                  END rownumber,
              @department := department_id
       FROM employee
       CROSS JOIN ( SELECT @department:=0, @row_number:=0 ) variables
       ORDER BY department_id, id ) subquery ON subquery.department_id = department.id
WHERE subquery.rownumber <= 2;


department_name
name


D1
John

D1
Misha

D2
Eugen

D2
Tolya




id
select_type
table
partitions
type
possible_keys
key
key_len
ref
rows
filtered
Extra


1
PRIMARY
department
null
ALL
null
null
null
null
3
100.00
Using where

1
PRIMARY
<derived2>
null
ref
<auto_key0>
<auto_key0>
5
db_1367354022.department.id
2
33.33
Using where

2
DERIVED
<derived3>
null
system
null
null
null
null
1
100.00
Using filesort

2
DERIVED
employee
null
ALL
null
null
null
null
5
100.00
null

3
DERIVED
null
null
null
null
null
null
null
null
null
No tables used

id	department_id	chief_id	name	salary
1	1	5	John	100
2	1	5	Misha	600
3	2	5	Eugen	300
4	2	5	Tolya	400
5	1	null	Stepan Chief	500

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	department	null	ALL	null	null	null	null	3	100.00	null
1	PRIMARY	employee	null	ALL	null	null	null	null	5	20.00	Using where; Using join buffer (Block Nested Loop)
2	DEPENDENT SUBQUERY	emp	null	ALL	null	null	null	null	5	20.00	Using where