null handling

SQL XML in Oracle: Generating XML from Relational Data

Generating XML from Relational Data using Oracle SQL/XML

How to generate XML from relational data using a combination of Oracle SQL/XML functions such as XMLELEMENT, XMLATTRIBUTES, XMLFOREST, XMLAGG, XMLSERIALIZE.

Read the Full Article →
SQL: Compare Two Tables for Differences

SQL: Compare Two Tables for Differences

In a recent project, a shadow table had to be compared periodically with its main source table to identify the differences between the two tables. The nature of differences fell into one of these buckets: Insert Required [INS]: If the source had rows not present in the shadow table Update Required [UPD]: If one or […]

Read the Full Article →
Thumbnail image for UNPIVOT in Oracle 11G to Select Columns As Rows

UNPIVOT in Oracle 11G to Select Columns As Rows

When the need is to transpose columns as rows, Oracle 11G+ enables you to easily implement this using the UNPIVOT clause is SQL. A typical scenario:

Read the Full Article →
Oracle Collections and Object Types: Comparing for Equality

Oracle Collections and Object Types: Comparing for Equality

Oracle can easily compare data items of scalar data types (those that hold a single data value with no internal components – e.g. NUMBER, DATE or VARCHAR2). So, if a and b are two NUMBER variables, all you need to do to check whether they are the same or not, is test "if (a=b)". Can […]

Read the Full Article →
15 Things You Should Know about the ORDER BY Clause

15 Things You Should Know about the ORDER BY Clause

When multiple columns/expressions are specified in the ORDER BY clause, the precedence of sorting is left to right. The ORDER BY clause can order in ascending (ASC) or descending (DESC) sequence, or a mix of both. If ASC or DESC is not explicitly stated, then ASC is the default. ORDER BY ASC places NULL values […]

Read the Full Article →
MERGE Statement in Oracle

MERGE: Insert New Rows, Update Existing Rows in One Shot

MERGE in Oracle combines the power of INSERT and UPDATE into one power-packed statement

Read the Full Article →
ORDER BY Nulls First or Last

How to Manage those Pesky NULLs when Sorting Data

How to position NULL values in the sort column – send them to the top or the bottom in the result set.

Read the Full Article →
Thumbnail image for IN = EXISTS, but beware: NOT IN is not the same as NOT EXISTS!

IN = EXISTS, but beware: NOT IN is not the same as NOT EXISTS!

In our last article we said that in SQL queries, IN and EXISTS are interchangeable. Many of us assume therefore, that NOT IN and NOT EXISTS are also interchangeable. A big mistake. See how NOT IN and NOT EXISTS behave differently in this small example.

Read the Full Article →
Difference Between DECODE and CASE

The Difference Between DECODE and CASE

DECODE and CASE statements in Oracle both provide a conditional construct, of this form: if A = n1 then A1 else if A = n2 then A2 else X Databases before Oracle 8.1.6 had only the DECODE function. CASE was introduced in Oracle 8.1.6 as a standard, more meaningful and more powerful function. Everything DECODE […]

Read the Full Article →