Szokásomhoz híven átlapoztam a “Nagy OpenSource Kód Gyűjteményt” (van aki StackOverflow-ként ismeri), és megint szembesültem az élet egyik alaptételével: nincs új a nap alatt. Gyakorlatilag ugyan azok a kérdések bukkannak fel újra és újra, ezzel az elfogadott close votes számát növelve a reputation helyett.

Hogy valami hasznunk is legyen az egészből, összegyűjtöttem néhány gyakran elkövetett SQL lekérdezésekkel, illetve adatbázisokkal kapcsolatos hibát/kérdést, és ha már itt vagyok és gépelek, akkor legjobb tudásom szerint meg is válaszolom őket. Több részletben.

Igyekszem menő módon platform független válaszokat adni, de az adatbázisok világában ez nem mindig könnyű. Általánosságban MySQL lesz terítéken (mert ez az egység sugarú fejlesztő első választása).

Helyenként Hunglish-t használok, mert egyes kifejezéseknek egyszerűen nincs jó magyar fordítása, vagy mert az adott kifejezést mindig angolul használjuk, amikor egy másik fejlesztővel tanácskozunk.

NULL

Azok a fejlesztők, akiknek az SQL a második-harmadik-negyedik nyelve, hajlamosak megfeledkezni a NULL-ról és arról, hogy majdnem mindig külön kezelni kell. Erre sajnos rájátszik az is, hogy sok nyelvben az SQL NULL-t az adott nyelv null-jához kötik (lásd pl PHP, JAVA), a kettő azonban nem teljesen equivalens.

Mi a NULL? A NULL egész egyszerűen annyit jelent, hogy nincs beállított érték, vagyis röviden: ismeretlen. A NULL nem egyenlő a nulla (0) értékkel, sem az üres stringgel.

A NULL-nak van néhány nagyon fontos tulajdonsága:

  • a NULL nem tesztelhető a szokványos összehasonlító operátorokkal (<, >, =, <>, !=, etc), az ilyen összehasonlítás eredménye ismeretlen (!), amit a legtöbb RDBMS implicit konvertál FALSE-ra.
  • NULL-t tartalmazó kifejezés eredménye NULL, azaz 35431 + NULL eredménye ismeretlen (azaz NULL)
  • azt, hogy egy kifejezés értéke NULL-e, az IS NULL operátorral tesztelhetjük (illetve ennek ellenkezőjét az IS NOT NULL operátorral)

Nézzünk egy példát

Tegyük fel, hogy a felhasználóink regisztrációnál megadhatnak egy kupon kódot és kíváncsiak vagyunk, hogy mely kupon kódokat nem használták még fel.

USE test;
CREATE TABLE users (
  user_id INT NOT NULL,
  coupon_code VARCHAR(16) NULL,
  PRIMARY KEY (user_id)
);

CREATE TABLE coupon_codes(
  coupon_code VARCHAR(16) NOT NULL,
  coupon_id   INT         NOT NULL,
  PRIMARY KEY (coupon_code)
);

INSERT INTO coupon_codes (coupon_code, coupon_id) VALUES
('aaa', 1), ('bbb', 2), ('ccc', 1);

INSERT INTO users (user_id, coupon_code) VALUES
(1, NULL), (2, 'bbb'), (3, NULL), (4, 'aaa');

SELECT
  coupon_code
FROM
  coupon_codes
WHERE
  coupon_code NOT IN (SELECT coupon_code FROM users);


A sok munka után szomorúan tapasztaljuk, hogy az eredmény egyetlen kis megjegyzés: 0 rows retrieved in 4ms (5ms total)

Szikét elő, boncolunk:

SELECT coupon_code
WHERE coupon_code NOT IN (SELECT coupon_code FROM users);

/* Helyettesítsük be az értékeket a táblából */
SELECT coupon_code
WHERE coupon_code NOT IN (NULL, 'bbb', NULL, 'aaa');

/* Az IN egy rakás OR feltétel, szóval */
SELECT coupon_code
WHERE NOT (coupon_code=NULL OR coupon_code='bbb' OR coupon_code='ccc');

/* Akkor most bontsuk ki azt a NOT-ot */
SELECT coupon_code
WHERE coupon_code!=NULL AND coupon_code!='bbb' AND coupon_code!='ccc';

Az utolsó variáció már egyértelmű: nem lehet a feltételnek megfelelő rekord a táblában, hiszen a coupon_code!=NULL sosem teljesül.

Hogy gyógyíthatjuk meg a fenti lekérdezést?

  1. Tehetünk egy coupon_code IS NOT NULL feltételt a subquery-be
  2. IN helyett használhatunk NOT EXISTS-et
  3. Hasnálhatjuk az EXCEPT -et, hogy két lekérdezés eredményét kivonjuk egymásból (már ha az adott implementáció ismeri)
  4. Használhatunk LEFT JOIN-t és egy IS NULL feltételt
/** IS NOT NULL **/
SELECT
coupon_code
FROM
coupon_codes
WHERE
coupon_code NOT IN (SELECT coupon_code FROM users WHERE coupon_code IS NOT NULL);
;

/** EXISTS **/
SELECT
coupon_code
FROM
coupon_codes CC
WHERE
NOT EXISTS (SELECT * FROM users U WHERE CC.coupon_code = U.coupon_code);
;

/** EXCEPT (MySQL ezt nem tudja!) **/
SELECT coupon_code FROM coupon_codes
EXCEPT
SELECT coupon_code FROM users
;

/** LEFT JOIN és IS NULL **/
SELECT
C.coupon_code
FROM
coupon_codes CC
LEFT JOIN users U
ON CC.coupon_code = U.coupon_code
WHERE
U.coupon_code IS NULL
;

Éjszakai lekérdezés (SELECT *)

A SELECT * formula használata feltételezi, hogy a lekérdezésben használt táblák sosem változnak, de legalábbis azt, hogy az alkalmazás képes alkalmazkodni a változáshoz.

A SELECT * feleslegesen növeli a hálózaton átküldött adatmennyiséget (ha nem használjuk az összes mezőt), ezzel természetesen lassabbá téve az alkalmazást. Az extra datamennyiség fogadásán felül több memóriát kell lefoglalni és tovább tart az adatok feldolgozása, stb.

Fenáll az ‘adatvesztés’ veszélye: A SELECT * eredménye minden további nélkül tartalmazhat több ugyan olyan nevű mezőt is, amit nem minden esetben kezel helyesen a kliens:

Ha valaki töröl, esetleg átnevez egy mezőt valamelyik hivatkozott táblából, változik az eredmény, esetleg olyan módon, hogy azt csak nehezen lehet észlelni. Tegyük fel, hogy a products és shops táblákban is van egy is_active mező. Mindkettő bináris, a lekérdezésünk pedig a következő:

SELECT
  *
FROM
  products P
  INNER JOIN product_shops PS
    ON P.product_id = PS.product_id
  INNER JOIN shops S
    ON PS.shop_id = S.shop_id

Az alkalmazás legyen mondjuk egy PHP webshop, a lekérdezést pedig a jól bevált FETCH_ASSOC-al dolgozzuk fel. A felületen megjelenítünk egy kis ikont annak függvényében, hogy az adott üzlet aktív-e vagy sem ($row['is_active'] == 1). Jön egy feture request, hogy az üzletnek lehessen egy harmadik állapota is, ezért létrehozunk egy új mezőt, és törljük az is_active mezőt. Mi történik a kódunkkal? Semmi. Se hiba, se notice, se semmi, csak a megjelenített információ helytelen, ugyanis a kis ikon mostantól a termék állapotát mutatja, nem az üzletét.

Most képzeljük el, mit történik, ha a shops táblában létrehozunk egy name mezőt és a products táblában már szerepel egy.

Ha vesszük a fáradtságot és felsoroljuk a mezőket a * helyett és aliassal látjuk el a őket, már a lekérdezés futtatásánál sírni fog az alkalmazásunk.

Két olyan eset van, amikor a *-nak létjogosultsága van: SELECT COUNT(*) és SELECT ... WHERE EXISTS (SELECT * ... ). Ezekben az esetekben a * jelentése ‘sor’ és nem ‘az összes mező’.

A következő részben

Az adatbázisokkal és SQL lekérdezésekkel való hadakozás nem egyszerű, így azt hiszem mára elég lesz ennyi, a következőkben lesz még szó indexelésről, adatbázis tervezésről és más nyalánkságokról is. Stay tuned.