=====
demo a SQL union operation!
=====
consider this relation:
EMPL_LAST_NAME JOB_TITLE
--------------- ----------
Jones Manager
Blake Manager
Raimi Manager
and consider this relation:
EMPL_LAST_NAME JOB_TITLE
--------------- ----------
Michaels Sales
Ward Sales
Martin Sales
Turner Sales
and here is their union:
EMPL_LAST_NAME JOB_TITLE
--------------- ----------
Blake Manager
Jones Manager
Martin Sales
Michaels Sales
Raimi Manager
Turner Sales
Ward Sales
7 rows selected.
=====
another UNION example:
dept_nums of employees in Sales and dept_nums of
departments located in Dallas
=====
consider this (flaky) relation:
DEP
---
300
300
300
300
and consider this relation:
DEP
---
200
here is union of these two relations:
DEP
---
200
300
=====
some COMMON ERRORS using the UNION operator:
=====
ERROR: you CANNOT use union with two sub-selects with different
numbers of projected columns!
(select dept_num
*
ERROR at line 1:
ORA-01789: query block has incorrect number of result columns
ERROR: you CANNOT use union with two sub-selects whose respective
respective columns have INCOMPATIBLE domains (at least not in a data-type
sense)
(select salary
*
ERROR at line 1:
ORA-01790: expression must have same datatype as corresponding expression
BUT BE CAREFUL; if the projected columns are TYPE-compatible
but not (truly) UNION-compatible -- if they don't have the
same MEANING -- you can get NONSENSE results and NO syntax error:
DEPT_NAME
---------------
Accounting
Adams
Blake
Ford
James
Jones
King
Management
Martin
Michaels
Miller
DEPT_NAME
---------------
Operations
Raimi
Research
Sales
Scott
Smith
Turner
Ward
19 rows selected.
=====
a COOL use of union: when you WANT to better deal with NULL
values in certain columns:
=====
for example, I want TOTAL recompense for all employees,
salary + commission,
even though some have null commissions
EMPL_LAST_NAME total of salary + commission
--------------- ----------------------------
Adams 1100
Blake 2850
Ford 3000
James 950
Jones 2975
King 5000
Martin 2650
Michaels 1900
Miller 1300
Raimi 2450
Scott 3000
EMPL_LAST_NAME total of salary + commission
--------------- ----------------------------
Smith 800
Turner 1500
Ward 1750
14 rows selected.
=====
domain meanings are the point, not column names...
=====
EMPL
----
7499
7654
7788
7902
=====
demo of ampersand for interactive input into a SQL statement:
(remember to type job_ttl IN single quotes!)
=====
Enter value for job_ttl: 'Clerk'
old 3: where job_title = &job_ttl
new 3: where job_title = 'Clerk'
SALARY
----------
800
1100
950
1300
=====
ANOTHER demo of ampersand,
in single quotes, so DO NOT enter single quotes here:
=====
Enter value for job_ttl: Clerk
old 3: where job_title = '&job_ttl'
new 3: where job_title = 'Clerk'
SALARY
----------
800
1100
950
1300