set difference, wszystkie kraje które nie należą do...

Mam za zadanie wyszukać z jednej tabeli wszystkie kraje które nie należą do WHO.
Próbowałem pisać querry z "NOT IN" ale nie działa. Potem próbowałem napisać coś takiego, ale zwraca i tak wszystkie kraje
SELECT distinct country FROM ismember LEFT JOIN country ON country WHERE organization = "WHO";
Ma ktoś pomysł jak to rozwiązać?

Tabelka wygląda tak:

A próbowałeś:

SELECT *
  FROM countries
 WHERE country is not (
       SELECT country 
         FROM countries
        WHERE organization = 'WHO' 
       )

?

SELECT distinct country FROM ismember LEFT JOIN country ON country WHERE organization = "WHO";

To właściwie odwrotność tego co chcesz osiagnąć. @KamilAdam podał porawne rozwiazanie, ja od siebie dodam, że dobrą praktyka jest unikanie przeczeń w where bo te nie są optymalizowane i czasami mogą sprawiac problemy wydajnościowe. Wersja bez negacji:

SELECT 
  c.*
FROM 
  countries c
  left join (
       SELECT 
         country 
        FROM 
          countries
        WHERE 
          organization = 'WHO' 
       ) who on who.country=c.country
where
  who.country is null

KamilAdam
Próbowałem i to właśnie nie działało, nie wiem dlacego. Może dlatego że pracuje na HeidiSQL.

Panczo
Dzięki, dopiero twój kod podziałał, chociaż za wiele z niego nie rozumiem

tu nie ma nic skomplikowanego, łącze kraje z krajami które sa w WHO, warunek sprawia, że biorę te które nie maja dopasowania.

puść sobie bez warunku i wyświetl kolmny z who to zobaczysz

Chyba najbardziej optymalne(?):

WITH dctr AS (
SELECT DISTINCT("Country") FROM ismember 
)

SELECT dctr."Country" FROM dctr WHERE NOT EXISTS (SELECT * FROM ismember WHERE "Country" = dctr."Country" AND "Organisation" = 'WHO')

1. Różnica zbiorów

SELECT COUNTRY from COUNTRIES
MINUS
SELECT COUNTRY from ISMEMBER where ORGANISATION='WHO'
;

Alternatywnie "EXCEPT" zamiast "MINUS".

2. Agregacja i zliczanie wystąpień WHO

select 
 country
from ismember 
group by country 
having sum( case when ORGANISATION='WHO' then 1 else 0 end )=0;

Niby dlaczego, najpierw masz unikalną listę krajów w CTE, a potem dla każdego sprawdzasz, czy nie istnieje wpis powiązany z WHO. Jak chcesz to zrobić optymalniej - przez JOINy? To dopiero jest nieoptymalne bo dla każdego wiersza musisz robić JOIN zamiast robić sprawdzenie przez negację tylko raz dla danego kraju.

@TomRZ: w komentarzu sie nie zmieszczę...

Fajnie, że opisałeś jak Ci się wydaje że jest robione, jednak my rozmawiamy o SQL, a w nim mamy marny wpływ na to jak silnik te dane pobierze i optymalizator zoptymalizuje. Jedno jest pewne, należy starać się unikać przeczeń, bo to o ile optymalizowane, to jednak jest to trudne do osiągnięcia. Analizując przykład na testowych danych:

CREATE TABLE `ismember` (
  `kraj` varchar(1) NOT NULL,
  `organizacja` int(2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin2;

INSERT INTO `ismember` (`kraj`, `organizacja`) VALUES
('A', 1),('B', 1),('C', 1),('D', 1),('E', 1),('F', 1),('G', 1),('H', 1),('I', 1),('J', 1)
,('A', 2),('B', 2),('C', 2),('D', 2),('E', 2),('F', 2),('G', 2),('H', 2),('I', 2),('J', 2),
('A', 3),('B', 3);

Zróbmy te zapytania:

-- bez join
WITH dctr AS (
SELECT DISTINCT(`Country`) FROM ismember 
)
SELECT dctr.`Country` FROM dctr WHERE NOT EXISTS (SELECT * FROM ismember WHERE `Country` = dctr.`Country`
                                                  AND `Organisation` = 3);
--z join
WITH dctr AS (
SELECT DISTINCT(`Country`) FROM ismember 
)
SELECT dctr.`Country` FROM dctr
left join (select country from ismember where organisation=3) who on who.country = dctr.country
where who.country is null;                                                  

Statystyki:
Bez join

z join

Więc jak widzisz, join nie odstaje tutaj, a nawet tworzy jedną tabelę tymczasową mniej.

I to miałem na myśli psząc że przeczenie + optymalizacja to nie bardzo. Warto to zapamiętać, a przede wszystkim sprawdzać co robi silnik, a nie co się nam wydaje że robi.

Panczo odpowiedziałeś jak klaun, a nie ktoś kto poważnie polemizuje.

Po pierwsze nie porównałeś mojego rozwiązania, ze swoim oryginalnym, czyli:

SELECT 
  c.*
FROM 
  countries c
  left join (
       SELECT 
         country 
        FROM 
          countries
        WHERE 
          organization = 'WHO' 
       ) who on who.country=c.country
where
  who.country is null

Po drugie wziąłeś moje rozwiązanie z CTE, dokleiłeś do niego LEFT JOIN i to zaczynasz porównywać, kiedy mi chodziło o coś całkiem innego - sytuację bez CTE kiedy robi się JOIN dla każdego wiersza.

Po trzecie zmieniłeś strukturę bazy danych na bardziej optymalną, niż to co napisał OP, a do czego ja napisałem swoje rozwiązanie.

Ty się dobrze czujesz, wszystko w porządku? Może za dużo polityków się naoglądałeś, i dlatego zaczynasz robić chwyty erystyczne w ich stylu?

Panczo odpowiedziałeś jak klaun, a nie ktoś kto poważnie polemizuje.

Właściwie po takim stwierdzeniu powinienem odpuścić.

Po pierwsze nie porównałeś mojego rozwiązania, ze swoim oryginalnym

Celowo tego nie zrobiłem aby pokazać to na porównywalnych danych wyjściowych, ale skoro chcesz, to masz:

Zmiana jest, mam o jeden mnie scanów tabeli i żadnej tabeli tymczasowej, tak wiem mam wiecej examined rows.

Po trzecie zmieniłeś strukturę bazy danych na bardziej optymalną, niż to co napisał OP, a do czego ja napisałem swoje rozwiązanie.

Nie wiedziałem, że gdzieś było podana struktura na której operujemy. Zauważ, że to "bardziej optymalne" działa w dwie strony, zarówno w Twoim rozwiązaniu jak i moim. I nie ma znaczenia w tym co chciałem pokazać.

Może to tak działa na MySQL, ale ja działam w PostgreSQL, a OP pewnie jeszcze innej bazie danych.

Rozumiem, że tak traktujesz poważną polemikę. Ja polemizuje w ramach wątku, a w nim OP otagował to mysql. Widocznie nie mam Twoich zdolności aby znać strukturę danych oraz silnika którego on uzywa.

Ty się dobrze czujesz, wszystko w porządku? Może za dużo polityków się naoglądałeś, i dlatego zaczynasz robić chwyty erystyczne w ich stylu?

To gadamy poważnie, czy odnosimy się do samopoczucia i opinii politycznych?

Nie widzisz jaka jest struktura tabeli ismember?

Widzę i powtarzam nie ma ona znaczenia i nie wpływa na wyniki które przedstawiłem.

Tam jest tekst, nie ma intów. I powtarzam - porównaj to ze swoim oryginalnym pomysłem, i podaj wyniki używając takiej struktury jaka jest podana przez OP

Wiem, że tam jest tekst, wyniki podałem w poprzednim poście, typ organisation nie ma tu znaczenia

, zamiast manipulować jak to zrobiłeś.

No to masz niezmanipulowane wyniki:

Wskażesz różnice?

Ktoś kto w ten sposób manipuluje jest dla mnie pajacem

Typowe, brak argumentów zastępujemy niczym niepopartymi stwierdzeniami.

Wcześniej nie miałem czasu, ale jednak coś dodam - po pierwsze Twoje testy wyglądają podejrzanie bo masz inne nazewnictwo kolumn przy tworzeniu tabeli:

CREATE TABLE `ismember` (
  `kraj` varchar(1) NOT NULL,
  `organizacja` int(2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin2;

A inne w zapytaniach:

WITH dctr AS (
SELECT DISTINCT(`Country`) FROM ismember 
)
SELECT dctr.`Country` FROM dctr WHERE NOT EXISTS (SELECT * FROM ismember WHERE `Country` = dctr.`Country`
                                                  AND `Organisation` = 3);

To czyni całość Twoich testów podejrzanymi.

To teraz ja zrobiłem test na PostgreSQL:

CREATE TABLE IF NOT EXISTS public.ismember
(
    kraj character varying(1) COLLATE pg_catalog."default",
    organizacja smallint
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS public.ismember
    OWNER to postgres;

I teraz "moje" zapytanie bez JOINa:

WITH dctr AS (
SELECT DISTINCT(kraj) FROM ismember 
)
SELECT dctr.kraj FROM dctr WHERE NOT EXISTS (SELECT * FROM ismember WHERE kraj = dctr.kraj AND organizacja = 3);

Oraz z joinem:

WITH dctr AS (
SELECT DISTINCT(kraj) FROM ismember 
)
SELECT dctr.kraj FROM dctr
left join (select kraj from ismember where organizacja=3) who on who.kraj = dctr.kraj
where who.kraj is null;       

Produkuje w PostgreSQL ten sam plan i wykonuje się identycznie:

Więc coś chyba jest nie tak z MySQL-em że te dwa zapytania optymalizuje inaczej.

No dobra, ale ściągnąłem MySQL Workbench i robię test:

Dla EXISTS PLAN:

Dla JOIN Plan:

I znowu - wielkiej różnicy nie ma w każdym razie na MariaDB która jest klonem MySQL, poza tym, że przy NOT EXISTS ma nieco mniej pracy.

Więc Twój test jest trochę podejrzany - raz z powodu zapytań które różnią się nazwami od tego co jest w CREATE TABLE, a dwa rezultatami - w każdym razie tymi które ja otrzymałem.

I teraz gdybym miał wybierać przy tych samych rezultatach (ten sam plan i szybkość na PostgreSQL), to wybrałbym NOT EXISTS - bo zapytanie jest bardziej czytelne niż mieszanie z JOINEM odwołującym się do tej samej tabeli.

Chyba lubisz teorie spiskowe...
Roznica w nazwach jest spowodowana tym, ze najpierw zrobilem nazwy polskie, później zmienilem na angielskie, aby byly zgodne z forum. z rozpędu wkleilem nie tego ddl-a.

Skoro juz zainstalowałeś workbencha to kliknij "show statisticts" tam gdzie klikales pokazanie explain i zobaczysz dokladnie to co ja wklejalem.

wiem, że posgress robi to inaczej, ale o nim nie rozmawiamy.

Tak kliknąłem Show Statistics i dla NOT Exists pokazało mi same zera (mam dość szybkiego kompa) a dla JOINA jakieś milionowe części sekundy.

I tak najważniejszy jest plan ktory pokazałem, i tam widać, że dla JOINA musi oprócz "Using Where" zrobić także "Not exists" i "Using join" - czyli więcej pracy niż przy NOT EXISTS.

Tak jest w każdym razie na MariaDB, nie wiem jaką wersję MySQL Ty używasz.

Testowalem na mysql 8.

My się chyba nie rozumiemy co do genezy, bo porownywanie czasów na 22 rekordach mija się z celem. I jak widzisz ani razu nie odniosłem sie do czasu wykonania.

Pokusze się nawet o stwierdzenie ze not in zadziala rownie szybko.

Dlatego pisze, że najważniejszy jest plan - i jak widać, na MariaDB jest on praktycznie taki sam, przy czym nieco lepszy dla NOT EXISTS. Nie będę teraz odinstalowywal MariaDB i instalował MySQL 8 żeby sprawdzać i z Tobą dyskutować, bo to nie ma sensu, w każdym razie szkoda dla mnie czasu.

Ta rozmowa i tak jest oderwana od rzeczywistości bo w normalnej bazie miałbym tabele z krajami i nie potrzebowalbym cte do distinct.

I tak okazuje się, że przynajmniej w PostgreSQL najlepszy / najprostszy plan, i najszybsze wykonanie ma zapytanie @yarel

select 
 kraj
from ismember 
group by kraj 
having sum( case when organizacja=3 then 1 else 0 end )=0;

@TomRZ: i o to chodzi, zupełnie nie rozumiem, dlaczego tak emocjonalnie podchodzisz do tego wątku. Z wieloma osobami tu dyskutowałem, ale tylko z Toba ma wrażenie, ze od naszych postów zależy los świata...

Co tu sie odpierdala :o Proste pytanie a wy piszecie jakieś referaty dotorskie na ten temat haha