Search code examples
sqlitedaterequestsubquerywhere-clause

Subquery implementation

I have a table employees

------------------------------------------------
| name  | email              | date_employment |
|-------+--------------------|-----------------|
| MAX   | qwerty@gmail.com   | 2021-08-18      |
| ALEX  | qwerty2@gmail.com  | 1998-07-10      |
| ROBERT| qwerty3@gmail.com  | 2016-08-23      |
| JOHN  | qwerty4@gmail.com  | 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?

Solution

  • 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:

    ex
    1
    24
    6
    21

    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.