How to simplify the query to retrieve the details in Oracle?

Question

I need to write a query in Oracle to retrieve the details of employees who work either in Sales, Textiles and Marketing. But when the employees work in Marketing, I need to get only those employees whose department location is US and EU.

The location column for Sales and Textiles is NULL in the department table.

For example,

Department table:

dept_id   emp_id    dep_name       location 
D_1       A1130    MARKETING         US  
D_2       A1132    SALES             (null)
D_3       A1133    TEXTILES          (null)
D_4       A1134    MARKETING         US 
D_5       A1135    MARKETING         EU

I wrote the below query. Is there any other alternate way to write it simple instead of using UNION and writing the code twice?

Please help me.

SELECT emp.emp_id,
      emp.first_name ||' '|| emp.last_name employee_name,
      dept.department_name
FROM department dept,             
    employee emp,
    salary sal
WHERE emp.emp_id = dept.emp_id
 and dept.dept_id = sal.dept_id
 and emp.emp_id = 'A1130'
 and dept.department_name in ( 'SALES',
                             'TEXTILES')
UNION
SELECT emp.emp_id,
     emp.first_name ||' '|| emp.last_name employee_name,
     dept.department_name
FROM department dept,             
    employee emp,
    salary sal
WHERE emp.emp_id = dept.emp_id
 and dept.dept_id = sal.dept_id 
 and emp.emp_id = 'A1130'
 and dept.department_name in ( 'MARKETING')
 and dept.locations in ('US','EU'); 

Answer 1

retrieve the details of employees who work either in Sales, Textiles and Marketing. But when the employees work in Marketing, I need to get only those employees whose department location is US and EU.

You can do this without UNION, with ORed conditions in the WHERE clause:

SELECT 
    emp.emp_id,
    emp.first_name ||' '|| emp.last_name employee_name,
    dept.department_name
FROM 
    department dept
    INNER JOIN employee emp ON emp.emp_id = dept.emp_id
    INNER JOIN salary sal ON dept.dept_id = sal.dept_id
WHERE 
    emp.emp_id = 'A1130'
    AND (
        dept.department_name in ('SALES', 'TEXTILES')
        OR (dept.department_name = 'MARKETING' AND dept.locations in ('US','EU'))
    )

Side note: always use explicit joins (with the ON keyword) instead of old-school, implicit joins (with a list of table in the FROM clause), which have fallen out of favor more than twenty years ago.