Almost all SELECT statements that join tables and use the join operator can be rewritten as subqueries, and vice versa. Writing the SELECT statement using the join operator is often easier to read and understand and can also help the Database Engine to find a more efficient strategy for retrieving the appropriate data. However, there are a few problems that can be easier solved using subqueries, and there are others that can be easier solved using joins.
Advantages of using Subqueries
Subqueries are advantageous over joins when you have to calculate an aggregate value on-the-fly and use it in the outer query for comparison. Example 6.73 shows this.
Get the employee numbers and enter dates of all employees with enter dates equal to the earliest date:
USE sample;
SELECT emp_no, enter_date
FROM works_on
WHERE enter_date = (SELECT min(enter_date)
FROM works_on);Code language: PHP (php)This problem cannot be solved easily with a join, because you would have to write the aggregate function in the WHERE clause, which is not allowed. (You can solve the problem using two separate queries in relation to the works_on table.)
Advantages of using Joins
Joins are advantageous over subqueries if the SELECT list in a query contains columns from more than one table. Example 6.74 shows this.
Get the employee numbers, last names, and jobs for all employees who entered their projects on October 15, 2007:
USE sample;
SELECT employee.emp_no, emp_lname, job
FROM employee, works_on
WHERE employee.emp_no = works_on.emp_no
AND enter_date = '10.15.2007';Code language: PHP (php)The SELECT list of the query in Example 6.74 contains columns emp_no and emp_lname from the employee table and the job column from the works_on table. For this reason, the equivalent solution with the subquery would display an error, because subqueries can display information only from the outer table.
