Basic things of SQL

[Datura]

A. SQL Join:

1. SQL join is based on a cross-combination Cartesian product. SQL Outer Joins are augmentation of inner joins, it retrieves the matching rows by inner join plus the unmatched rows. You can understand it in this way, SQL does joins in 2 steps:

1) SQL builds a Cartesian product first, do the inner join, find the matched rows by inner join.
2) These matched rows then concatenate/insert back with the remaining unmatched rows in the original left/right table.

2. No column overlay. The value of a same-named variable from the latter table will NOT overwrite the value from the former table.

3. It can do both Equal and NE joining, which is impossible in SAS Merge.

B. SQL Union

When SQL unions data together, by default, it overlays columns by column positions rather than by names. Crazy and incredible? But it is true, be cautious when using UNION!

If you want it to overlay data by column names, use the CORRESPONDING (Corr) option.