how to use "not in" operator in oracle cmd?-CodePudding

I am using this code:

create or replace view VIEW_MAXMIN as
select c.country_name,
       max(salary) max_salary,
       min(salary) min_salary
from   employees e,
       departments d,
       locations l,
       countries c
where  e.department_id = d.department_id
and    d.location_id = l.location_id
and    l.country_id = c.country_id = not in(select country_name
                                            from   countries
                                            where  country_name = 'Mexico');

but always error saying 'SQL command not properly ended'

CodePudding user response：

You can change the joins from legacy Oracle comma joins to ANSI joins and then it looks like you want countries where the name is not Mexico.

Also, if you are aggregating columns and have a column that you are not aggregating then you need to use GROUP BY:

create or replace view VIEW_MAXMIN as
select c.country_name,
       max(salary) max_salary,
       min(salary) min_salary
from   employees e
       INNER JOIN departments d
       ON (e.department_id = d.department_id)
       INNER JOIN locations l
       ON (d.location_id = l.location_id)
       INNER JOIN countries c
       ON (l.country_id = c.country_id)
where  c.country_name != 'Mexico'
GROUP BY c.country_name;

CodePudding user response：

Looks like the error you're getting in particular is due to -

l.country_id = c.country_id = not in(select country_name
                                            from   countries
                                            where  country_name = 'Mexico');

You have 2 equals to condition in one filter statement - like a = b = c Just - l.country_id = c.country_id would suffice and country_name != 'Mexico' will have to be a separate condition