exists

SQL to Find Master Records with Identical Detail Records

SQL to Find Master Records with Identical Detail Records

The query to identify duplicate records in Oracle in a single table is fairly straightforward. Things get tricky in a two-table (master-detail) scenario, in which the master table holds the header information (id, name, etc) and the detail table has sets of values associated with the master records. What if we need to find those […]

Read the Full Article →
Thumbnail image for DISTINCT and How NOT To Use It

DISTINCT and How NOT To Use It

The DISTICT keyword placed next to SELECT restricts the result to unique rows from a query. DISTINCT is also a much abused keyword, often used as a quick fix to bad queries. Take this example from Oracle Applications:

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 →
Thumbnail image for Which is faster – IN or EXISTS?

Which is faster – IN or EXISTS?

A question asked multiple times over on Oracle forums: Which is faster – IN or EXISTS? The short answer, post-Oracle 9i is: Both are pretty much the same! Huh? What about that thumb rule – IN for small inner query, EXISTS for big inner query?

Read the Full Article →