It is always good to think twice before using a subquery to solve a SQL problem, especially if it is something that manipulates large volumes of data or that runs many times because those queries have to be stored in memory for later comparison , worsening application performance.
In the case presented, considering that a EMPLOYEE is related to a DEPARTMENT and this to a LOCATION , you need to express that relationship in your query and I believe you do not need to use subqueries. There are two basic ways to do this with a single query, one defining JOIN clauses and the other using tests in the WHERE clause that cause the creation of an implicit%%.
In both cases all three tables must be involved and the fields must be qualified by the names of the tables to which they belong, to avoid ambiguity. It is possible to set INNER JOIN to the tables, if necessary, to reduce the amount of text in the query, but I will keep the names in full for clarity.
Using the class aliases :
SELECT
employees.first_name,
employees.department_id,
employees.job_id
FROM
employees,
locations,
departments
WHERE
departments.location_id = locations.location_id and
employees.department_id = departments.department_id and
locations.state_province = 'sao paulo';
This type of query, in my opinion, shows less "verbosity" than using WHERE explicit, making it easier to read. The use of JOIN for this type of purpose is only possible because it is a simple relationship, in which all tables have data to cross, so that the expressed equalities result in an implicit WHERE .
Using INNER JOIN :
As I said above, I prefer using
INNER JOIN in these cases because I find it more interesting to put all the rules in one place, but I believe that the "most correct" is to define relationships between tables in
WHERE , using clauses
FROM . In addition to being the canonical use of SQL, the main advantage is the ability to express more complex relationships.
It follows the same relationship as the previous query, now expressed with two JOIN . I took the liberty of changing the requested fields in INNER JOIN and the filter in SELECT to demonstrate that the columns of all the tables are accessible (note that this is possible in any of the two join methods discussed here). >
SELECT
employees.first_name,
employees.department_id,
employees.job_id ,
departments.department_name,
locations.street_address
FROM
employees inner join departments on employees.department_id = departments.department_id
inner join locations on departments.location_id = locations.location_id
WHERE
departments.department_name = 'ti';
The explicit definition of WHERE enables a quicker visual understanding of the relationships established by the query. There are also other types of JOIN that give more control over which of the tables takes precedence in data recovery, something that is not at all possible using JOIN .
Chaining subqueries :
If for some reason it is necessary that the relationship between the tables is not expressed in the WHERE clause nor in the FROM clause as above, it can be reproduced through chained queries (also called "derived queries") in the clause WHERE .
Although we have a "deploying" of the use of SELECT previously shown, this method is somewhat limited in the searchable fields, since only the columns of the "outside" query will be returned:
select * from employees where department_id in (
select department_id from departments where location_id = (
select location_id from locations where state_province = 'sao paulo'
)
);
Note that in order to be able to compare a field with a query in the WHERE clause, this query must also return only one field of the same type. If the subquery returns more than one record and you need to search for all, change the operator from WHERE WHERE to = .
- SQL Fiddle demonstrating the above queries using some data
mounted under the template given in the question:
link
- For more information about using
in :
link
