[sql] "WHERE EXISTS " kontra "WHERE ... IN ... "

[mm34639]

mam dwie tablice

tablica 'gatunki' : w niej pola "gatunek" i "kontynent"
tablica 'zwierz': w niej pola "imie" , "gatunek", "waga"

każdy gatunek z tablicy zwierz jest też w tablicy gatunki, poza tym nie ma pustych pól i nie zajmujemy się szczegółami

jest zadanie: wypisać najstarszego zwierzaka dla każdego kontynentu (może się zdarzyć że na pewnym kontynencie dwa zwierzaki mają taką samą największą wagę)

w postgresie jest łatwo
 

Kod: Zaznacz cały

SELECT imie,gatunki.gatunek ,kontynent, waga FROM zwierz JOIN gatunki ON gatunki.gatunek=zwierz.gatunek WHERE (kontynent, waga) IN (SELECT kontynent,max(waga) FROM zwierz JOIN gatunki ON gatunki.gatunek=zwierz.gatunek GROUP BY kontynent)

niestety, Access nie trawi podzapytań mających więcej niż 1 kolumnę: tzn nie mogę dać:

Kod: Zaznacz cały

SELECT coś1,coś2,coś3 FROM ... WHERE (coś1,coś2) IN (SELECT (cośinnego1 ,cośinnego2) FROM ...);

a co najwyżej np.

Kod: Zaznacz cały

SELECT coś1,coś2,coś3 FROM ... WHERE coś1 IN (SELECT cośinnego1 FROM ...);

PODOBNO zamiast

Kod: Zaznacz cały

WHERE (coś1,coś2) IN (SELECT (cośinnego1,cośinnego2) FROM ...)

można to obejść używając jakoś

Kod: Zaznacz cały

..WHERE EXISTS..

Niestety, nie bardzo wiem jak i chyba nie do końca rozumiem jak to WHERE EXISTS działa. Może ktoś mógłby przepisać przy jego użyciu moje zapytanie i wyjaśnić z grubsza co i jak, albo zaproponować coś zupełnie innego?

W ogóle SQL w Accesie wydaje mi się jakiś ułomny. Nie wiecie czy moje pierwsze zapytanie przeszłoby w bazie Oracle albo MS SQL ?

[Vardamir]

Na bazie MSSQL takie coś by nie przeszło..

Za to przeszłoby coś takiego:

Kod: Zaznacz cały

WITH cte AS (
	SELECT	imie, g.gatunek, kontynent, waga, RANK() OVER(PARTITION BY kontynent ORDER BY waga DESC) AS Ranking
	FROM zwierz z
		JOIN gatunek g ON g.gatunek=z.gatunek
)
SELECT * FROM cte WHERE Ranking=1

Lub takiego:

Kod: Zaznacz cały

SELECT ca.imie, ca.gatunek, ca.kontynent, ca.waga
FROM (
	SELECT kontynent, MAX(waga) Maks
	FROM zwierz z
		JOIN gatunek g ON g.gatunek=z.gatunek
	GROUP BY kontynent
) kon_wag
CROSS APPLY (
	SELECT imie, gatunek, kontynent, waga
	FROM zwierz
	WHERE waga=kon_wag.Maks
) ca

Pierwsze wydaje się dużo lepszym rozwiązaniem. Dodatkowo pozwalającym na bardzo łatwe rozszerzenie o drugie, trzecie co do wagi zwierzęta.

[Afish]

Edycja: zagapiłem się i napisałem dla najlżejszego zwierzaka, ale to drobnostka.
Dla MS SQL 2014, powinno ruszyć w Accessie:

Kod: Zaznacz cały

http://sqlfiddle.com/#!6/410d2/1

Dane:

Kod: Zaznacz cały

CREATE TABLE Zwierz(imie varchar(50), gatunek int, waga decimal(8,2))

INSERT INTO Zwierz
VALUES ('E1', 1, 5), ('E2', 1, 10), ('E3', 1, 5), 
('A1', 2, 5), ('A2', 2, 4)

CREATE TABLE Gatunki(gatunek int, kontynent varchar(50))

INSERT INTO Gatunki
VALUES (1, 'Europa'), (2, 'Azja')

Zapytanie:

Kod: Zaznacz cały

SELECT z.imie, g.kontynent, g.gatunek, z.waga
FROM Zwierz AS z
JOIN Gatunki AS G ON z.gatunek = g.gatunek
WHERE EXISTS (
  SELECT z2.gatunek, MIN(z2.waga)
  FROM Zwierz AS z2
  GROUP BY z2.gatunek
  HAVING z2.gatunek = z.gatunek AND MIN(z2.waga) = z.waga
)

Edycja 2: Podzapytanie w sumie da się uprościć, wystarczy SELECT 1

[mm34639]

Dzięki