A quick note on CROSS APPLY: think of it as an inner join to a table valued function that takes parameters, something a regular JOIN cannot do.
Multiset Intersection: Keep the Matching Duplicate Occurrences
Multiset intersection keeps the duplicate count shared by both inputs rather than returning each common value once. I pair numbered occurrences to express that requirement. The result should contain the smaller count for each equivalent value.
SQL SERVER – Explanation and Understanding NOT NULL Constraint
What a NOT NULL constraint does and two ways to add one: when the table is created, or after the table is created. My example covers both.
SQL SERVER – Three Rules to Use UNION
The three rules to use UNION from Ben Forta’s T-SQL book: two or more SELECT statements, the same columns in the same order, and compatible data types.
Multiset Difference: Remove Only Matching Duplicate Occurrences
Multiset difference removes matching occurrences while keeping any extra copies from the left input. I assign occurrence numbers before pairing rows. A value’s presence on the right should not automatically remove every left copy.
SQL SERVER – Correlated and Noncorrelated – SubQuery Introduction, Explanation and Example
What is the difference between correlated and noncorrelated subqueries? I explain both kinds with simple AdventureWorks examples.
SQL SERVER – 2005 – Use Always Outer Join Clause instead of (*= and =*)
My friend’s queries failed in SQL Server 2005 because they used *= and =* for joins. Always use the LEFT or RIGHT outer join clause instead.