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 →
Thumbnail image for Oracle Collections and Object Types: Comparing for Equality

Oracle Collections and Object Types: Comparing for Equality

Oracle collection or object type comparison – how to check if two Oracle collections or object types are equal or not.

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

15 Things You Should Know about the ORDER BY Clause

What is the precedence of sorting in a multi-column ORDER BY? How does ORDER BY sort nulls? What does ORDER SIBLINGS BY do? Answers to these questions and more about Oracle’s ORDER BY clause.

Read the Full Article →
Thumbnail image for MERGE: Insert New Rows, Update Existing Rows in One Shot

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 →
Thumbnail image for How to Manage those Pesky NULLs when Sorting Data

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 with this ORDER BY NULLs FIRST or LAST.

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 Oracle SQL queries, IN and EXISTS are interchangeable. Many of us assume therefore, that NOT IN and NOT EXISTS are also interchangeable. That’s not the case though.

Read the Full Article →
Thumbnail image for The Difference Between DECODE and CASE

The Difference Between DECODE and CASE

Both DECODE and CASE statements in Oracle are used for IF-THEN-ELSE conditional operations, but there are distinct differences in their power and behavior.

Read the Full Article →