Literal Fabrication
Learning Outcomes
- Learn how to fabricate a unique record set.
- Learn how to fabricate a non-unique record set.
Lesson Materials
Unique Sets
You can query a literal value by using the dual pseudo table, like this:
COL id FORMAT 9999 COL text FORMAT A10 SELECT 1 AS id , 'One' AS text FROM dual; |
It returns:
ID TEXT
----- ----------
1 One |
You join two duplicate queries with the UNION operator, like
COL id FORMAT 9999 COL text FORMAT A10 SELECT 1 AS id , 'One' AS text FROM dual UNION SELECT 1 AS id , 'One' AS text FROM dual; |
It returns one copy of the unique row because it sorts the data to find and return only the unique set:
ID TEXT
----- ----------
1 One |
If the literal values of the top and bottom queries differ, like this:
COL id FORMAT 9999 COL text FORMAT A10 SELECT 1 AS id , 'One' AS text FROM dual UNION SELECT 2 AS id , 'Two' AS text FROM dual; |
It returns one copy of the unique rows:
ID TEXT
----- ----------
1 One
2 Two |
Non-unique Sets
You join two duplicate queries with the UNION ALL operator, like
COL id FORMAT 9999 COL text FORMAT A10 SELECT 1 AS id , 'One' AS text FROM dual UNION ALL SELECT 1 AS id , 'One' AS text FROM dual; |
It returns all rows from the query because a UNION ALL operator does not sort the return set to a unique set:
ID TEXT
----- ----------
1 One
1 One |
Please note the examples follow the rules qualified in the UNION and UNION ALL web pages.
