Everyone should learn the difference between Union and Union All. Knowing it will make you a better programmer, and it’s fairly trivial to understand. SELECT * FROM apples UNION SELECT * FROM oranges When you know for a fact that there will never be any common rows between the apples table and the oranges table, [...]
One way to get the count of distinct variables, which works in most flavors of SQL, is to use a subquery. For instance, in Oracle this is: SELECT count(SELECT DISTINCT foo FROM table) FROM dual In SAS, using PROC SQL, you can do that too, but you can also simply do this: SELECT count(distinct foo) [...]
If your SQL code has a nested select that references a column in an outer select, such as the following, it may be possible to rewrite to perform orders of magnitude faster. proc sql; create table new_rates as select from work.exchange_rate n where not exists( select from imf.exchange_rate o where n.effective_date=o.effective_date and n.iso_char_code=o.iso_char_code ); NOTE: [...]
Databases are very very good at storing tabular, dimensional data; and in a world where everything is a spreadsheet (your finance department), this works very well. Often, though, there’s a need for an application to store and deal with a tree of data; such as in classification systems or management structures. The Library of Congress, [...]