Summary
Highlights
When combining tables, avoid using SELECT *; instead, explicitly list all columns. This practice makes schema changes more apparent and helps prevent mismatches due to altered column order or type. Additionally, adding a 'Source Table' column can indicate where each record originated, providing valuable context for data analysis.
Data engineers can use the EXCEPT operator to identify new data (delta) generated from source systems to be loaded into a data warehouse or data lake. By comparing current data with the previous load using EXCEPT, only the new, unique records are identified for insertion, optimizing data pipelines and preventing redundant data loading.
SQL set operators combine the results of multiple queries into a single result set. Unlike SQL JOINS, which combine columns side-by-side, set operators append rows from different queries one beneath the other. Set operators do not require a key column but demand the same number, order, and compatible data types for the columns being combined.
The syntax involves placing a set operator between two SELECT statements. Key rules include: ORDER BY can only be used once at the end of the entire query, all queries must have the same number of columns with matching data types and the same order, the first query defines column names and data types, and users are responsible for correctly mapping information between queries.
UNION returns all distinct rows from both queries, removing duplicates. UNION ALL returns all rows from both queries, including duplicates. UNION ALL offers better performance than UNION because it doesn't perform the additional step of removing duplicates. UNION ALL is useful when preserving all data, including duplicates, or for data quality checks.
EXCEPT returns distinct rows from the first query that are not found in the second query. The order of the queries is crucial, as swapping them will yield different results. The second query is used only for checking; no data from it is included in the output. EXCEPT is useful for identifying unique data in one set compared to another.
INTERSECT returns only distinct rows that are common to both queries. Similar to an INNER JOIN, it finds matching data in both sets. The order of the queries does not affect the result. INTERSECT is simple and effective for finding shared elements between two datasets.
Set operators are vital for combining similar tables (e.g., employees, customers, suppliers) into a single, unified table before performing data analysis. This approach simplifies reporting, ensures logic consistency, and prevents data inconsistencies across multiple, similar queries. It's particularly useful when tables are split for performance optimization, like orders by year.
The EXCEPT operator is highly valuable for ensuring data completeness during migrations between databases. By performing EXCEPT queries in both directions (source EXCEPT target and target EXCEPT source), any missing records or unexpected additions are highlighted. An empty result from both checks confirms that the tables are identical, ensuring a complete and accurate migration.
SQL set operators combine rows from multiple queries into one result. UNION combines distinct rows, UNION ALL combines all rows (including duplicates), EXCEPT finds rows in the first query not in the second, and INTERSECT finds common rows. Adhering to SQL rules (same number/order/type of columns, first query controls aliases) is crucial. Use cases include combining data for reports, identifying data deltas, and performing data completeness checks for quality assurance.