To retrieve more information from other tables
Hi,
I wanted to retrieve more information regarding the two employees from the assignment here, such as their names and department names.
The method I used is to use double join, having a subquery within WHERE clause as follows:
---------
SELECT
e.emp_no, dm.dept_no, d.dept_name,e.first_name, e.last_name
FROM
employees e
JOIN
dept_manager dm ON e.emp_no = dm.emp_no
JOIN
departments d ON dm.dept_no = d.dept_no
WHERE
e.emp_no IN
(SELECT
emp_no
FROM
employees e
WHERE
hire_date BETWEEN '1990-01-01' AND '1995-01-01')
;
----------
It worked as I wanted, but I would like to know if it's a good practice in real life or should there be a better way considering computational power, probability to cause an error, etc.
If there is any other alternatives either better or not, I would love to see how others code!
Thank you,
Hi Song!
Thanks for reaching out.
Using a subquery within the WHERE clause, as you've implemented, is certainly valid and can work as intended in many scenarios. However, whether this is considered good practice in real-life applications often depends on a few factors:
1) Database Size and Performance: If you're working with a large database, the performance of subqueries can become a concern. Subqueries, especially those in the WHERE clause, can sometimes lead to less efficient execution plans, particularly if they need to be executed repeatedly for each row in the outer query.
2) Readability and Maintenance: Subqueries can make the SQL statement more complex and harder to read, especially for those who are not very experienced with SQL. This could potentially lead to maintenance challenges as complexity increases.
3) Error Probability: While subqueries do not inherently increase the probability of errors, the complexity they add can potentially lead to mistakes in logic, especially in more complex queries involving multiple subqueries or nested conditions.
Hope this helps.
Best,
Tsvetelin