I have a table employees

------------------------------------------------
| name  | email              | date_employment |
|------- --------------------|-----------------|
| MAX   | [email protected]   | 2021-08-18      |
| ALEX  | [email protected]  | 1998-07-10      |
| ROBERT| [email protected]  | 2016-08-23      |
| JOHN  | [email protected]  | 2001-03-09      |
------------------------------------------------

and I want to write a subquery that will display employees who have been with them for more than 10 years.

SELECT employees.name, employees.email, employees.date_employment
FROM employees
WHERE 10 > (SELECT round((julianday('now') - julianday(employees.date_employment)) / 365, 0) FROM employees);

After executing this request, it displays all employees, regardless of their seniority. If you write a request like this, then everything works

SELECT name, round((julianday('now') - julianday(employees.date_employment)) / 365, 0) as ex
FROM employees WHERE ex > 10;

Why subquery is not working properly?

CodePudding user response：

If you execute the subquery:

SELECT round((julianday('now') - julianday(employees.date_employment)) / 365, 0) AS ex
FROM employees

you will see that it returns 4 rows:

It does not make sense to use the above resultset in a condition of a WHERE clause to compare it with 10.
But SQLite allows it, while other databases would throw an error.

How does SQLite handle this case? It choses only 1 value of the resultset (usually the first) and uses it in the WHERE clause, so your code is equivalent to:

WHERE 10 > 1

which is always true and this is why you get all the rows of the table as a result.