How to simplify nested select in where clause?

I have 4 tables EMPLOYEE, COMPANY, WORKS and MANAGES. The tables are defined as follows-

CREATE TABLE EMPLOYEE
(
    EMPLOYEE_NAME VARCHAR2(50) NOT NULL PRIMARY KEY,
    STREET VARCHAR2(50) NOT NULL,
    CITY VARCHAR2(30) NOT NULL
);

CREATE TABLE COMPANY
(
    COMPANY_NAME VARCHAR2(100) NOT NULL PRIMARY KEY,
    CITY VARCHAR2(50) NOT NULL
);

CREATE TABLE WORKS
(
    EMPLOYEE_NAME VARCHAR2(50) NOT NULL PRIMARY KEY REFERENCES EMPLOYEE(EMPLOYEE_NAME),
    COMPANY_NAME VARCHAR2(100) NOT NULL REFERENCES COMPANY(COMPANY_NAME),
    SALARY NUMBER(12,2) NOT NULL
);

CREATE TABLE MANAGES
(
    EMPLOYEE_NAME VARCHAR2(50) NOT NULL PRIMARY KEY REFERENCES EMPLOYEE(EMPLOYEE_NAME),
    MANAGER_NAME VARCHAR2(50) NOT NULL
);

I need to find all the employees who live in the same city as the company for which they work. So far I have done this.

SELECT EMPLOYEE_NAME AS Names
FROM EMPLOYEE
WHERE CITY = (
    SELECT CITY
    FROM COMPANY
    WHERE COMPANY_NAME = (
        SELECT COMPANY_NAME
        FROM WORKS
        WHERE WORKS.EMPLOYEE_NAME = EMPLOYEE.EMPLOYEE_NAME
    )
);

It’s working fine. But I want to know is there any simpler way to do this query?

Answer

You could use an explicit inner join instead of nested subselect

SELECT EMPLOYEE.EMPLOYEE_NAME AS Names
FROM EMPLOYEE
INNER JOIN WORKS ON  WORKS.EMPLOYEE_NAME = EMPLOYEE.EMPLOYEE_NAME
INNER JOIN COMPANY ON  EMPLOYEE.CITY = COMPANY.CITY

Advertisement

Answer