An approach for comparing two tables for differences in data, using a pure SQL solution:
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 more columns in a row (identified by a primary key) had changed values
- Delete Required [DEL]: If the shadow table had rows not present in the source table
Comparing two tables for differences (INS/UPD/DEL) needed a different solution from a simple MERGE SQL statement. Here an upsert was not to be executed, only the nature of differences to be identified.
[click to continue…]
For those new to SQL, terms like INNER JOIN and OUTER JOIN can seem like fearsome foes. As the wise say, understanding conquers fear. Behind those geeky terms lie concepts rooted in simple real-world knowledge.
Here’s a quickstart guide to these two basic joins in SQL: INNER JOIN and OUTER JOIN.
[click to continue…]
The DISTINCT 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:
[click to continue…]
A subquery in the SELECT clause of the main query is called a scalar subquery.
This is a single row, single column query, which looks just like a column or function in the SELECT clause. The structure is:
[click to continue…]
