Would these two SQLite queries generate the same result?-CodePudding

I'm working through this exercise.

On question 4, the goal is to find employees hired after "Jones". I think this problem can be solved without a join like so:

SELECT first_name, last_name, hire_date
FROM employees 
WHERE hire_date > (
  SELECT hire_date FROM employees WHERE last_name = "Jones"
)

But the answer on the website suggests:

SELECT e.first_name, e.last_name, e.hire_date 
FROM employees e 
JOIN employees davies 
ON (davies.last_name = "Jones") 
WHERE davies.hire_date < e.hire_date;

Are these more-or-less the same or is there a reason the second answer should be considered better?

CodePudding user response：

I assume that the column last_name is defined as UNIQUE, so that the subquery in the 1st query returns only 1 row.
If not, then the queries do not return the same results, because although the subquery in the 1st query may return more than 1 row (and in other databases the query would not even run), SQLite will pick just the 1st of the returned rows and use its hire_date to compare it with all the rows of the table, while the join will use all the rows where last_name = "Jones".

If my assumption is correct then the 2 queries are equivalent, but the 1st one is what I would suggest because it is more readable and I believe it would perform better than the join.

If I had to use a join for this requirement (since it is homework) I would choose a more readable form:

SELECT e.first_name, e.last_name, e.hire_date 
FROM employees e 
JOIN (SELECT * FROM employees WHERE last_name = "Jones") t 
ON t.hire_date < e.hire_date;