De SQL-functie DECODE() begrijpen

Hoe gebruik je de SQL-functie DECODE()

De SQL-functie DECODE() wordt native ondersteund in de Oracle-database en is beschikbaar in het PL/SQL-dialect. Met de functie DECODE() kun je voorwaardelijke logica binnen de query implementeren. Deze techniek is belangrijk omdat ze helpt bij datatransformatie tijdens het ophalen van records uit de database.

Bekijk de onderstaande tabel employees, waar department_id numerieke waarden heeft. In plaats daarvan willen we dat department_id department_name heet en begrijpelijkere categorieën heeft.

Voorbeeldtabel om te transformeren met de SQL-functie DECODE(). Afbeelding door de auteur.

Het onderstaande voorbeeld laat zien hoe je de functie DECODE() gebruikt om department_id naar de echte namen te transformeren.

-- Using DECODE to transform department_id to department_name
SELECT 
    employee_id,
    first_name,
    last_name,
    DECODE(department_id, 
        1, 'IT', 
        2, 'HR', 
        3, 'Finance', 
        4, 'Marketing', 
        5, 'Sales', 
        'Unknown') AS department_name,
    salary,
    hire_date,
    city
FROM employees;Mogelijk gemaakt door

Voorbeelduitvoertabel na gebruik van de SQL-functie DECODE(). Afbeelding door de auteur.

De SQL-functie DECODE() begrijpen

De functie DECODE() is beschikbaar in de Oracle-database en stelt je in staat een kolom te transformeren met voorwaardelijke logica. De functie DECODE() maakt meerdere voorwaardelijke transformaties mogelijk met één eenvoudige query.

De syntax van de functie DECODE() is als volgt:

DECODE(expression, search1, result1, search2, result2, ..., default)Mogelijk gemaakt door

Waarbij:

• expression: De waarde om te vergelijken.

• search: De waarde waartegen de expressie wordt vergeleken.

• result: De waarde die wordt geretourneerd als de expressie overeenkomt met de zoekwaarde.

• default: De waarde die wordt geretourneerd als er geen overeenkomst is gevonden (optioneel).

Geavanceerde technieken en aandachtspunten met de SQL-functie DECODE()

Met de functie DECODE() kun je enkele geavanceerde SQL-bewerkingen uitvoeren. De geavanceerde technieken maken complexere transformaties met voorwaardelijke logica mogelijk. 

DECODE() met aggregatiefuncties

De functie DECODE() in Oracle kan worden gebruikt in de SELECT-instructie om een variabele opnieuw te categoriseren, en kan ook worden gebruikt in de SELECT-instructie met aggregatiefuncties voor een complexere groepering, wat de flexibiliteit en interpretatie van de resultaten verder verbetert. 

In de volgende query gebruiken we DECODE() om department_id om te zetten naar department_name. We gebruiken DECODE() ook om het gemiddelde salaris te categoriseren als ‘High Paying’ of 'Low Paying'.

SELECT 
    department_id,
    
    -- Use DECODE to transform department_id into department_name
    DECODE(
        department_id,
        1, 'IT', 
        2, 'HR', 
        3, 'Finance', 
        4, 'Marketing', 
        5, 'Sales', 
        'Unknown'
    ) AS department_name,
    
    SUM(salary) AS total_salary, 
    
    -- Calculate the average salary for each department and round to nearest integer
    ROUND(AVG(salary), 0) AS average_salary,
    
    -- Use DECODE to categorize average salary into 'High Paying' or 'Low Paying'
    DECODE(
        SIGN(AVG(salary) - 65000), 
        1, 'High Paying', 
        0, 'High Paying', 
        -1, 'Low Paying' 
    ) AS salary_category
FROM 
    employees
GROUP BY 
    department_id; Mogelijk gemaakt door

Voorbeelduitvoer van tabel na gebruik van meerdere SQL DECODE()-functies. Afbeelding door de auteur

Geneste DECODE()-instructies

Het implementeren van de functie DECODE() met subquery’s stelt je in staat geavanceerde transformaties met voorwaardelijke logica op de data uit te voeren. De geneste DECODE()-instructie in Oracle helpt wanneer we meerdere voorwaarden in een tabel willen evalueren.

In het onderstaande voorbeeld hebben we de geneste DECODE()-instructie gebruikt om werknemers te categoriseren op basis van hun afdeling en salaris.

-- Select the columns
SELECT 
    employee_id,
    first_name, 
    last_name, 
    department_id,
    salary, 
    
    -- Use DECODE to categorize salary based on department and salary thresholds
    DECODE(department_id,
        
        1, DECODE( -- If department_id is 1 (IT)
            CASE 
                WHEN salary > 65000 THEN 'High' 
                ELSE 'Low' 
            END, 
            'High', 'IT High Salary', 
            'Low', 'IT Low Salary' 
        ),
        
        2, DECODE( -- If department_id is 2 (HR)
            CASE 
                WHEN salary > 55000 THEN 'High'
                ELSE 'Low' 
            END, 
            'High', 'HR High Salary', 
            'Low', 'HR Low Salary' 
        ),
        
        3, DECODE( -- If department_id is 3 (Finance)
            CASE 
                WHEN salary > 70000 THEN 'High' 
                ELSE 'Low' 
            END, 
            'High', 'Finance High Salary', 
            'Low', 'Finance Low Salary' 
        ),
        
        4, DECODE( -- If department_id is 4 (Marketing)
            CASE 
                WHEN salary > 68000 THEN 'High'
                ELSE 'Low' 
            END, 
            'High', 'Marketing High Salary', 
            'Low', 'Marketing Low Salary' 
        ),
        
        5, DECODE( -- If department_id is 5 (Sales)
            CASE 
                WHEN salary > 60000 THEN 'High' 
                ELSE 'Low'  
            END, 
            'High', 'Sales High Salary', 
            'Low', 'Sales Low Salary'
        ),
        
        'Unknown Department' -- Default value if department_id does not match any case
    ) AS salary_category
FROM 
    employees;Mogelijk gemaakt door

Voorbeelduitvoer van tabel na gebruik van de geneste DECODE()-functie. Afbeelding door de auteur

Hoe DECODE() NULL-waarden afhandelt

De functie DECODE() gaat op twee manieren anders om met NULL-waarden. Om te beginnen behandelt DECODE() twee NULL-waarden als gelijkwaardig. Dit betekent dat NULL-waarden als een match worden beschouwd wanneer kolomwaarden met NULL worden vergeleken. Let wel op onverwachte resultaten waarbij niet expliciet rekening is gehouden met NULL, omdat NULL-waarden hierdoor tijdens de transformatie gemaskeerd kunnen worden.

Weet ook dat, net als bij de CASE WHEN-instructie, de functie DECODE() een NULL-waarde retourneert als er geen overeenkomst is gevonden.

Het onderstaande voorbeeld laat zien hoe de functie DECODE() null-waarden retourneert waar department_id en salary ontbreken.

-- Select columns
SELECT 
    employee_id,
    first_name,
    last_name, 
    department_id, 
    salary, 
    -- Determine the salary category based on the salary value
    DECODE(salary, 
        NULL, 'No Salary Information',
        60000, 'Standard Salary',
        'Other Salary') AS salary_category, 
    -- Determine the department name based on the department ID
    DECODE(department_id, 
        1, 'IT',
        2, 'HR',
        3, 'Finance',
        4, 'Marketing',
        5, 'Sales') AS department_name 
        -- If department_id does not match any of the above, return NULL
FROM 
    employees;Mogelijk gemaakt door

Voorbeelduitvoer van tabel na gebruik van DECODE() om NULL-waarden af te handelen. Afbeelding door de auteur

DECODE vs. CASE WHEN in Oracle

De functie DECODE() werd als eerste geïntroduceerd en hoewel deze is opgevolgd door de CASE WHEN-instructie, heeft DECODE() nog steeds de voorkeur in sommige contexten. Zo is de syntax van de functie DECODE() eenvoudiger; als de logica vrij simpel is, leest en begrijpt die makkelijker. 

Ook is de functie DECODE() meestal sneller, omdat de prestaties zijn geoptimaliseerd als ingebouwde functie, al kan het verschil verwaarloosbaar zijn. Tot slot kan het in verouderde Oracle-systemen nodig zijn om de ingebouwde functie DECODE() te gebruiken. 

Alternatieven voor DECODE() in andere databases

De functie DECODE() wordt alleen ondersteund in de Oracle-database. SQL Server, PostgreSQL en MySQL bieden echter alternatieve methoden om voorwaardelijke transformatielogica toe te passen.

De functie DECODE() is nuttig bij datatransformatie, omdat ze complexe logica vereenvoudigt door het gebruik van IF ELSE- of CASE-instructies te vermijden. De functie DECODE() verbetert ook de leesbaarheid van queries, wat efficiënte datamanipulatie mogelijk maakt. Laten we de verschillende use-cases van de functie DECODE() bekijken.

CASE WHEN gebruiken in SQL Server, PostgreSQL en MySQL

De CASE WHEN-instructie biedt een alternatief voor de functie DECODE() om SQL’s voorwaardelijke logica te implementeren. De syntax en implementatie van CASE WHEN zijn consistent in SQL Server-, PostgreSQL- en MySQL-databases.

Het onderstaande voorbeeld laat zien hoe je de CASE WHEN-instructie gebruikt om data te transformeren binnen de SELECT-instructie. De query laat ook zien hoe je de CASE WHEN-instructie combineert met andere SQL-functies voor complexe voorwaardelijke transformaties.

 -- Select department ID
SELECT 
    department_id,
    
    -- Use CASE to transform department_id into department_name
    CASE 
        WHEN department_id = 1 THEN 'IT'
        WHEN department_id = 2 THEN 'HR'
        WHEN department_id = 3 THEN 'Finance'
        WHEN department_id = 4 THEN 'Marketing'
        WHEN department_id = 5 THEN 'Sales'
        ELSE 'Unknown' 
    END AS department_name,
    
    SUM(salary) AS total_salary,
    
    -- Calculate the average salary for each department and round to the nearest whole number
    ROUND(AVG(salary), 0) AS average_salary,
    
    -- Use CASE to categorize average salary into 'High Paying' or 'Low Paying'
    CASE 
        WHEN AVG(salary) > 65000 THEN 'High Paying'
        ELSE 'Low Paying' 
    END AS salary_category
FROM 
    employees
GROUP BY 
    department_id; -- Group results by department ID   Mogelijk gemaakt door

Conclusie en verder leren

De Oracle-functie DECODE() begrijpen is belangrijk om te leren hoe je data effectief transformeert. De functie DECODE() biedt diverse use-cases voor het toepassen van voorwaardelijke logica tijdens datatransformatie. Het is ook belangrijk dat je alternatieve voorwaardelijke transformaties leert in SQL Server-, PostgreSQL- en MySQL-databases. Ik moedig je aan om de functie DECODE() te oefenen met verschillende datasets en use-cases om je transformatieskills te verfijnen.

Als je je data-analysevaardigheden wilt uitbreiden, raad ik DataCamp’s cursus Reporting in SQL aan om je analyse- en presentatieskills verder te ontwikkelen. Bekijk ook ons Associate Data Analyst in SQL-carrièrepad om de voortgang bij te houden van de benodigde SQL-kennis voor je loopbaan als data-analist. Tot slot raad ik DataCamp’s project Data-Driven Decision Making in SQL ten zeerste aan om je beheersing van verschillende SQL-technieken voor analyse en rapportage te laten zien, zodat je je onderscheidt van andere data-analisten.