• Joins
• CASE Statements
• NULLIF Function
• COALESCE Function
• Scalar Subqueries
• Explicit Defaults

Joins 

Een scala van nieuwe join syntaxis is beschikbaar die voldoen aan de ANSI/ISO SQL: 1999 standaards.

De CROSS JOIN produceert een cartesian product:

ANSI/ISO Syntax

SQL>SELECT first_name, 
        last_name, 
        department_name 
 FROM   employees 
        CROSS JOIN departments; 

Bestaande Syntax

SQL>SELECT first_name, 
       last_name, 
       department_name 
FROM   employees, 
       departments; 

De NATURAL JOIN doet een join voor alle kolommen met gelijke namen in de 2 tabellen:

ANSI/ISO Syntax

 SQL>SELECT department_name, 
        city 
 FROM   departments 
        NATURAL JOIN locations; 

Bestaande Syntax

SQL>SELECT d.department_name, 
        l.city 
 FROM   departments d, 
        locations l 
 WHERE  d.location_id = l.location_id 
 AND    d.country     = l.country;        

De USING clausule wordt gebruikt als een aantal kolommen de zelfde naam hebben, maar je wilt niet al deze gewone kolommen joinen. De kolommen die getoond worden in de USING clause kunnen geen qualifiers in het statement hebben, inclusief de WHERE clause:

ANSI/ISO Syntax

 SQL>SELECT d.department_name, 
        l.city 
 FROM   departments d 
        JOIN locations l USING (location_id); 

Bestaande Syntax

SQL>SELECT d.department_name, 
        l.city 
 FROM   departments d, 
        locations l 
 WHERE  d.location_id = l.location_id; 

De ON clause wordt gebruikt om tabellen te joinen waarvan de kolom namen niet overeenkomen. De join condities zijn van de filter condities verwijderd in de WHERE clause:

ANSI/ISO Syntax

 SQL>SELECT d.department_name, 
        l.city 
 FROM   departments d 
        JOIN locations l ON (d.location_id = l.id); 

Bestaande Syntax

SQL>SELECT d.department_name, 
        l.city 
 FROM   departments d, 
        locations l 
 WHERE  d.location_id = l.id; 
 
Muteerbare Joins zijn die joins waar meer dan 2 tabellen worden gejoined. De SQL: 1999 standaard gaat er van uit dat de tabellen worden gejoined van links naar rechts, waar de join condities alleen kunnen refereren aan kolommen van de huidige join en enige vorige join naar links:

SQL>SELECT employee_id, 
       city, 
       department_name 
FROM   locations l 
       JOIN departments d ON (d.location_id = l.location_id) 
       JOIN employees e ON (d.department_id = e.department_id); 

Bestaande Syntax

SQL>SELECT employee_id, 
        city, 
        department_name 
 FROM   locations l, 
        departments d, 
        employees e 
 WHERE  d.location_id   = l.location_id 
 AND    d.department_id = e.department_id; 

Er zijn 3 variaties van de outer join. De LEFT OUTER JOIN geeft alle rijen terug van de table aan de linkerzijde van de join, samen met de waarden van de rechterkant, of NULLs als een overeenkomstige rij niet bestaat. De RIGHT OUTER JOIN doet het omgeleerde. De FULL OUTER JOIN geeft alle rijen van beide tabellen terug waar de ‘blanks’ met NULLs worden opgevuld:

ANSI/ISO Syntax

 
 SQL>SELECT e.last_name, 
        d.department_name 
 FROM   employees e 
        LEFT OUTER JOIN departments d ON (e.department_id = d.department_id); 

Bestaande Syntax

SQL>SELECT e.last_name, 
        d.department_name 
 FROM   employees e, 
        departments d 
 WHERE  e.department_id = d.department_id(+);

ANSI/ISO Syntax

 SQL>SELECT e.last_name, 
        d.department_name 
 FROM   employees e 
        RIGHT OUTER JOIN departments d ON (e.department_id = d.department_id); 

Bestaande Syntax

SQL>SELECT e.last_name, 
        d.department_name 
 FROM   employees e, 
        departments d 
 WHERE  e.department_id(+) = d.department_id; 

ANSI/ISO Syntax

SQL>SELECT e.last_name, 
        d.department_name 
 FROM   employees e 
        FULL OUTER JOIN departments d ON (e.department_id = d.department_id); 

HEEFT GEEN EQUIVALENT!

Extra filter condities kunnen aan de join toegevoegd worden met AND om een complexe join te vormen. Deze zijn vaak nodig als filter condities nodig zijn om outer joins uit te sluiten. Als deze filter condities worden geplaatst in de WHERE clause en de outer join geeft een NULL waarde terug voor de filter kolom, zou de rij weggegooid worden. Als de filter conditie als onderdeel van de join is gecodeerd, kan deze situatie voorkomen .

CASE Statements

De case statement is een meer flexibele uitbreiding van het Decode statement. In zijn meest simpele vorm wordt het gebruikt om een waarde terug te geven als een match is gevonden:

SQL>SELECT last_name, commission_pct, 
   (CASE commission_pct 
      WHEN 0.1  THEN 'Low' 
      WHEN 0.15 THEN 'Average' 
      WHEN 0.2  THEN 'High' 
      ELSE 'N/A' 
    END) Commission 
 FROM employees 
 ORDER BY last_name; 

Een meer complexe versie is de Searched CASE expressie waar een vergelijkende expressie wordt gebruikt om een match te vinden:

SQL>SELECT last_name, job_id, salary, 
   (CASE 
      WHEN job_id LIKE 'SA_MAN' AND salary < 12000 THEN '10%' 
      WHEN job_id LIKE 'SA_MAN' AND salary >= 12000 THEN '15%' 
      WHEN job_id LIKE 'IT_PROG' AND salary < 9000 THEN '8%' 
      WHEN job_id LIKE 'IT_PROG' AND salary >= 9000 THEN '12%' 
      ELSE 'NOT APPLICABLE' 
   END) pay_raise 
 FROM employees; 

Return waarden kunnen niet NULL zijn.

NULLIF Function

De NULLIF functie komt met een NULL waarde terug als beide parameters gelijk in waarde zijn. De volgende query zou een NULL teruggeven:

SQL>SELECT NULLIF(1,1) FROM dual; 

COALESCE Function

SQL>SELECT COALESCE(NULL, NULL, '3') FROM dual; 

Scalar Subqueries

Scalar subqueries geven een enkele waarde terug. Ze zouden in eerdere versies van Oracle gebruikt kunnen worden, in sommige delen van een SQL statement, maar Oracle9i breidt het gebruik uit naar bijna elke plek waar een expressie kan worden gebruikt waaronder:

CASE expressions
SELECT statement
VALUES clause van een INSERT statement
WHERE clause 
ORDER BY clause
Als een parameter of een function

Bijvoorbeeld:

SQL>INSERT INTO my_table VALUES ((SELECT 1 FROM dual), NULL);  
 
SQL>SELECT Substr((SELECT 'ABC' FROM dual), 1, 1) FROM dual; 

Scalar subqueries kunnen maar met 1 enkele kolom en 1 enkele rij terugkomen. Indien er meer dan 1 rij terugkomt wordt er een error geproduceerd. Als er geen rijen terugkomen zal de waarde NULL gebruikt worden. Het datatype van de terugkomende waarde moet overeenkomen met het datatype waarmee wordt vergeleken.
Scalar subqueries kunnen niet gebrukt worden voor:

Default waarden voor kolommen
RETURNING clause
Hash expressions voor clusters
Functional index expressions
CHECK constraints op kolommen
WHEN condition van triggers
GROUP BY en HAVING clauses
START WITH en CONNECT BY clauses

Explicit Defaults

Het DEFAULT keyword kan gebruikt worden om expliciet een default waarde aan een kolom te geven tijdens een INSERT of UPDATE statement:

SQL>INSERT INTO my_table VALUES (1, 'OWNER', DEFAULT, NULL);  
SQL>UPDATE my_table SET column1 = DEFAULT;