How to do sub-query correctly in Oracle?

Question

I need to do a sub-query of a table to find all employees who work in the same department that is part of São Paulo, but I’m not getting it. That is, I need to select from the three tables, departments, employees and locations to know from which locations the employees work. And I need to select state_province table locations while selecting the first_name, departments_id and job_id table employees.

I have the following tables :

Department table

DEPARTMENTS

department_id
department_name
location_id

Local table

LOCATIONS

location_id
street_address
postal_code
city
state_province
country_id

Table employed

EMPLOYEES

employee_id
first_name
last_name
email
phone_number
hire_date
job_id
department_id

With my current code it doesn’t work, this more or less like this :

SELECT firt_name, department_id, job_id 
FROM locations 
WHERE state_province = (SELECT * 
                        FROM locations 
                        WHERE state_province = 'Sao Paulo');

He starts to screw up the part about WHERE since state_province only belongs to the table LOCATIONS and there is no way for me to access its attributes. How can I do this sub-query correctly ?

I’m new to this part of select nestled with another select, so forgive me the grotesque errors.

Answer 1

It is always good to think twice before using a subquery to solve an SQL problem, especially if it is something that handles large volumes of data or that is executed many times, because these queries have to be saved in memory for further comparison, worsening the performance of the application.

In the present case, considering that a EMPLOYEE is related to a DEPARTMENT and this to a LOCATION, you need to express this relationship in your query and I believe that it is not necessary to use Queries. There are two basic ways to do this with a single query, one defining clauses JOIN and other using tests in the clause WHERE leading to the creation of a INNER JOIN 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 define aliases for tables, if necessary, to reduce the amount of text in the query, but I will keep the full names for clarity.

---

Using the clasp WHERE:

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, has less "verbosity" than using JOIN explicit, making it easier to read. The use of WHERE 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 a INNER JOIN implicit.

---

Using INNER JOIN:

As I said above, I prefer the use of WHERE 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 the relationships between tables in the FROM, using clauses JOIN. Besides being the canonical use of SQL, the main advantage is the possibility of expressing more complex relationships.

It follows the same relationship as the previous query, now expressed with two INNER JOIN. I took the liberty of changing the requested fields in SELECT and the filter in WHERE to demonstrate that the columns of all tables are accessible (note, this is possible in either of the two Join methods described 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 JOIN enables faster 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 through the use of WHERE.

---

Chaining:

If for any reason it is necessary that the relationship between the tables is not expressed in the clause FROM nor in the WHERE as above, it can be reproduced via chained queries (also called "derived queries") in the clause SELECT.

Although it is obtained, for all purposes, an "unfolding" of the use of WHERE previously demonstrated, this method is somewhat limited in the fields that can be searched, since only the columns of the query "outside" 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 it is possible to compare a field with a clause query WHERE, this query also needs to return only one field of the same type. If the subquery returns more than one registration and it is necessary to search for all, change the operator of the WHERE of = for in.

---

• SQL Fiddle demonstrating the above queries using some data mounted under the model indicated in the question: http://sqlfiddle.com/#! 4/49b40/6
• For more information on the use of JOIN: http://www.sql-join.com/sql-join-types/