Joins in de praktijk (2)

Queries worden van links naar rechts uitgevoerd en in het geval van subqueries van binnen naar buiten.
Dit betekent dus dat ORDER BY en LIMIT op het totaal van de gejoinde tabellen uitgevoerd worden.

Met dit in het achterhoofd terug naar de webshop:
We willen een overzicht van producten (incl. specificaties) uit een bepaalde categorie, gesorteerd op prijs (oplopend), gefilterd op fabrikant en 15 producten per pagina. Stel dat we een LIMIT aan het einde van de query zouden zetten, dan zou het resultaat 15 rijen zijn en niet 15 producten.
Dit kan worden opgelost door eerst met een subquery de producten te selecteren en dan daarna de specs aan het product te koppelen:

Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SELECT
    p.prd_id, p.prd_name, p.prd_descr, p.prd_price,
    sp.spec_label, sp.spec_value
FROM
    (SELECT
        prd_id, prd_name, prd_descr, prd_price
    FROM
        products
    WHERE
        cat_id = 24 AND manu_id = 5
    ORDER BY
        prd_price
    LIMIT
        0,15
    ) AS p
LEFT JOIN
    jn_product_specs AS psp ON p.prd_id = psp.prd_id
LEFT JOIN
    product_specs AS sp ON psp.spec_id = sp.spec_id
ORDER BY p.prd_price, p.prd_id

Let op de LEFT JOIN's. Het is mogelijk dat een product geen specs heeft maar die moeten wel meegenomen worden in het resultaat. Normaal zou je denken de join van product_specs op de koppeltabel een INNER JOIN zou moeten zijn, maar dat zou tot gevolg hebben dat de eerdere LEFT JOIN geen effect meer heeft, maar zich gedraagt als een INNER JOIN.

Joins in plaats van WHERE (NOT) IN (of EXISTS) subquery's:
Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
2
3
4
5
6
SELECT
    product_id,
    product_name
FROM
    products
WHERE product_id IN (SELECT DISTINCT product_id FROM order_products)

Het is beter om deze query om te schrijven naar een join:
Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
2
3
4
5
6
7
8
SELECT DISTINCT
    product_id,
    p.product_name
FROM
    products p
INNER JOIN
    order_products o
    USING (product_id)

Hetzelfde met NOT IN:
Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
2
3
4
5
6
SELECT
    product_id,
    product_name
FROM
    products
WHERE product_id NOT IN (SELECT DISTINCT product_id FROM order_products)

Het is beter om deze query om te schrijven naar een left join:
Code (php)
PHP script in nieuw venster Selecteer het PHP script
1
2
3
4
5
6
7
8
9
SELECT
    product_id,
    p.product_name
FROM
    products p
LEFT JOIN
    order_products o
    USING (product_id)
WHERE o.order_id IS NOT NULL

« Lees de omschrijving en reacties

 
 

Om de gebruiksvriendelijkheid van onze website en diensten te optimaliseren maken wij gebruik van cookies. Deze cookies gebruiken wij voor functionaliteiten, analytische gegevens en marketing doeleinden. U vindt meer informatie in onze privacy statement.