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 |