Description:
When I use two subqueries in where clause the condition doesn't work.
Example:
CREATE TABLE Department (
Id INT UNSIGNED NOT NULL,
Title VARCHAR(30),
ParentId INT UNSIGNED NOT NULL,
PRIMARY KEY (Id)
);
CREATE TABLE Employee (
id INT UNSIGNED NOT NULL,
Name VARCHAR(30),
DepId INT UNSIGNED NOT NULL,
PRIMARY KEY(id)
);
INSERT INTO Department VALUES (1, "Deparment 1", 0);
INSERT INTO Department VALUES (2, "Deparment 2", 1);
INSERT INTO Department VALUES (3, "Deparment 3", 1);
INSERT INTO Department VALUES (4, "Deparment 4", 3);
INSERT INTO Employee VALUES (1, "Employee 1", 1);
INSERT INTO Employee VALUES (2, "Employee 2", 2);
INSERT INTO Employee VALUES (3, "Employee 3", 3);
INSERT INTO Employee VALUES (4, "Employee 4", 4);
INSERT INTO Employee VALUES (5, "Employee 5", 1);
INSERT INTO Employee VALUES (6, "Employee 6", 2);
INSERT INTO Employee VALUES (7, "Employee 7", 3);
INSERT INTO Employee VALUES (8, "Employee 8", 4);
So, I write query:
SELECT * FROM Employee WHERE DepId IN (SELECT Id FROM Department WHERE ParentId = 0);
It works properly. I've got result:
+----+------------+-------+
| id | Name | DepId |
+----+------------+-------+
| 1 | Employee 1 | 1 |
| 5 | Employee 5 | 1 |
+----+------------+-------+
2 rows in set (0.00 sec)
The query:
SELECT * FROM Employee WHERE DepId IN (SELECT Id FROM Department WHERE ParentId = 1);
also works propeerly, I got result:
+----+------------+-------+
| id | Name | DepId |
+----+------------+-------+
| 2 | Employee 2 | 2 |
| 3 | Employee 3 | 3 |
| 6 | Employee 6 | 2 |
| 7 | Employee 7 | 3 |
+----+------------+-------+
4 rows in set (0.00 sec)
But if I write two subqueries in WHERE caluse, like this:
SELECT * FROM Employee
WHERE DepId IN (SELECT Id FROM Department WHERE ParentId = 2)
AND DepId IN (SELECT ID FROM Department WHERE Title = "Deparment 3");
I got all the rows from the table "Dapartment":
+----+------------+-------+
| id | Name | DepId |
+----+------------+-------+
| 1 | Employee 1 | 1 |
| 2 | Employee 2 | 2 |
| 3 | Employee 3 | 3 |
| 4 | Employee 4 | 4 |
| 5 | Employee 5 | 1 |
| 6 | Employee 6 | 2 |
| 7 | Employee 7 | 3 |
| 8 | Employee 8 | 4 |
+----+------------+-------+
8 rows in set (0.00 sec)
However, in last case, correct result set must be empty. Some other experiments, when I tried to use two or more subqueries in the same WHERE clause, give the same result: condition doesn't work in this case.
How to repeat:
It's no difference.
Description: When I use two subqueries in where clause the condition doesn't work. Example: CREATE TABLE Department ( Id INT UNSIGNED NOT NULL, Title VARCHAR(30), ParentId INT UNSIGNED NOT NULL, PRIMARY KEY (Id) ); CREATE TABLE Employee ( id INT UNSIGNED NOT NULL, Name VARCHAR(30), DepId INT UNSIGNED NOT NULL, PRIMARY KEY(id) ); INSERT INTO Department VALUES (1, "Deparment 1", 0); INSERT INTO Department VALUES (2, "Deparment 2", 1); INSERT INTO Department VALUES (3, "Deparment 3", 1); INSERT INTO Department VALUES (4, "Deparment 4", 3); INSERT INTO Employee VALUES (1, "Employee 1", 1); INSERT INTO Employee VALUES (2, "Employee 2", 2); INSERT INTO Employee VALUES (3, "Employee 3", 3); INSERT INTO Employee VALUES (4, "Employee 4", 4); INSERT INTO Employee VALUES (5, "Employee 5", 1); INSERT INTO Employee VALUES (6, "Employee 6", 2); INSERT INTO Employee VALUES (7, "Employee 7", 3); INSERT INTO Employee VALUES (8, "Employee 8", 4); So, I write query: SELECT * FROM Employee WHERE DepId IN (SELECT Id FROM Department WHERE ParentId = 0); It works properly. I've got result: +----+------------+-------+ | id | Name | DepId | +----+------------+-------+ | 1 | Employee 1 | 1 | | 5 | Employee 5 | 1 | +----+------------+-------+ 2 rows in set (0.00 sec) The query: SELECT * FROM Employee WHERE DepId IN (SELECT Id FROM Department WHERE ParentId = 1); also works propeerly, I got result: +----+------------+-------+ | id | Name | DepId | +----+------------+-------+ | 2 | Employee 2 | 2 | | 3 | Employee 3 | 3 | | 6 | Employee 6 | 2 | | 7 | Employee 7 | 3 | +----+------------+-------+ 4 rows in set (0.00 sec) But if I write two subqueries in WHERE caluse, like this: SELECT * FROM Employee WHERE DepId IN (SELECT Id FROM Department WHERE ParentId = 2) AND DepId IN (SELECT ID FROM Department WHERE Title = "Deparment 3"); I got all the rows from the table "Dapartment": +----+------------+-------+ | id | Name | DepId | +----+------------+-------+ | 1 | Employee 1 | 1 | | 2 | Employee 2 | 2 | | 3 | Employee 3 | 3 | | 4 | Employee 4 | 4 | | 5 | Employee 5 | 1 | | 6 | Employee 6 | 2 | | 7 | Employee 7 | 3 | | 8 | Employee 8 | 4 | +----+------------+-------+ 8 rows in set (0.00 sec) However, in last case, correct result set must be empty. Some other experiments, when I tried to use two or more subqueries in the same WHERE clause, give the same result: condition doesn't work in this case. How to repeat: It's no difference.