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, this query will be [...]
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) FROM table
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 [...]
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, [...]