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 master records that have identical sets of values in the detail table? Can a single SQL list master records with identical detail records?
[click to continue…]
The SQL WITH clause is similar in concept to a function definition in procedural code. In a function, we factor the common code, put it all together at one place and call it as many times as needed in the main program. That’s precisely how we use the WITH clause in SQL – factor out the common subquery, put it all together at one place and call it as many times as needed in the main query.
BUT there is a difference.
[click to continue…]
The WITH clause, also known as the subquery factoring clause, was introduced in Oracle 9i as a way to make queries more efficient and readable. Let’s see how WITH works with a basic example.
[click to continue…]
