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 →
Thumbnail image for SQL: Compare Two Tables for Differences in Data

SQL: Compare Two Tables for Differences in Data

How to compare two tables that share the same structure, to identify data differences between the two tables: missing rows, extra rows, or mismatch in attribute values.

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

You can easily transpose table columns to rows in Oracle 11G+ using the UNPIVOT clause is SQL. A comparison of the pre-11G DECODE solution with the 11G+ UNPIVOT method.

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 →