SQL & PL/SQL

Oracle DB version: 19c

The following post from last week prompted me to play around with similair data.

https://forums.oracle.com/ords/apexds/post/sql-query-for-oracle-dba-find-pdb-which-doesn-t-have-a-part-9102

mathguy already provided a good solution to the above post. But, it uses subquery. Being an intermediate learner of SQL, I had this feeling that this is a good use case of OUTER JOIN and I wanted to solve this using an OUTER JOIN.

I've prepared similair test data for this. My requirement is mentioned below.

create user smith identified by Penguin#368 default tablespace users;
grant create table, create session to smith;
alter user smith quota 10m on users;

sqlplus smith/Penguin#368@PDB_TEST

create table countries (country_id number, country_name varchar2(25));

create table cities (city_name varchar2(25), country_id number,
       CONSTRAINT pk_cities PRIMARY KEY (city_name));

create table shops (city_name varchar2(25), shop_name varchar2(15), 
   CONSTRAINT pk_shops PRIMARY KEY (city_name, shop_name));
-- Sample data
-- Countries
insert into countries values (4, 'BELARUS') ;
insert into countries values (5, 'SPAIN') ;
insert into countries values (6, 'ITALY') ;
insert into countries values (7, 'RUSSIA') ;
insert into countries values (8, 'CANADA') ;
insert into countries values (9, 'URUGUAY') ;
-- Cities
insert into cities values ('OTTAWA', 8 ) ;
insert into cities values ('MINSK', 4 ) ;
insert into cities values ('MADRID', 5 ) ;
insert into cities values ('MILAN', 6) ;
insert into cities values ('MOSCOW', 7) ;
insert into cities values ('MONTEVIDEO', 9) ;
-- Shops
insert into shops values ('MADRID', 'MCDONALDS') ;
insert into shops values ('MADRID', 'IKEA') ;
insert into shops values ('MILAN', 'IKEA') ;
insert into shops values ('MOSCOW', 'LVMH') ;
commit;

SQL> select * from countries;

COUNTRY_ID COUNTRY_NAME
---------- -------------------------
        4 BELARUS
        5 SPAIN
        6 ITALY
        7 RUSSIA
        8 CANADA
        9 URUGUAY
        
6 rows selected.

SQL> select * from cities;

CITY_NAME                 COUNTRY_ID
------------------------- ----------
OTTAWA                             8
MINSK                              4
MADRID                             5
MILAN                              6
MOSCOW                             7
MONTEVIDEO                         9

6 rows selected.

SQL> select * from shops;

CITY_NAME                 SHOP_NAME
------------------------- ---------------
MADRID                    IKEA
MADRID                    MCDONALDS
MILAN                     IKEA
MOSCOW                    LVMH

SQL>

Requirement: I need to find all countries which has a city starting with 'M%' but lacks the shop named IKEA.
MINSK, MOSCOW and MONTEVIDEO have no shop named IKEA.

So, after some trial and error, the below query seems to provide stage 1 of the above mentioned requirement which is the output of Query1 shown below.

Query1 does an INNER JOIN between COUNTRIES and CITIES and then does a LEFT OUTER JOIN with SHOPS table. I was happy to see those NULL values under SHOP_NAME column. To me, it means, the OUTER JOIN is working (I could be wrong)

Query1.

set null <<>

select cy.country_name,
      ct.city_name,
      sp.shop_name
from countries cy inner join cities ct on (cy.country_id = ct.country_id and ct.city_name like 'M%')
left join shops sp on (ct.city_name=sp.city_name)
and  sp.shop_name = 'IKEA';

COUNTRY_NAME              CITY_NAME                 SHOP_NAME
------------------------- ------------------------- ---------------
BELARUS                   MINSK                     <<>
SPAIN                     MADRID                    IKEA
ITALY                     MILAN                     IKEA
RUSSIA                    MOSCOW                    <<>
URUGUAY                   MONTEVIDEO                <<>

SQL>

So I thought my work is 99% done and to get the final output, I thought I could just add the filter "**and sp.shop_name is null**" and it will provide my required output ie. list countries which has a city starting with 'M%' but lacks the shop named IKEA. But, it gives the wrong output like below.

Query2.

select cy.country_name,
      ct.city_name,
      sp.shop_name
from countries cy inner join cities ct on (cy.country_id = ct.country_id and ct.city_name like 'M%')
left join shops sp on (ct.city_name=sp.city_name)
and sp.shop_name is null;

COUNTRY_NAME              CITY_NAME                 SHOP_NAME
------------------------- ------------------------- ---------------
BELARUS                   MINSK                     <<>
SPAIN                     MADRID                    <<>  ----> But MADRID has an IKEA
ITALY                     MILAN                     <<>  ----> But MILAN has an IKEA
RUSSIA                    MOSCOW                    <<>
URUGUAY                   MONTEVIDEO                <<>

SQL>

So, how to fix the above query (Query2) ? ANSI or Oracle style joins; either one will do.