Data fabrication is an intriguing idea. In it’s most general sense you fabricate data any time you write a query. A query takes data and represents it in a result set, which is a two-dimensional structure of information. The list of rows is like an array, and the elements of the array data structures defined in the data dictionary or catalog. Though list is more appropriate than array because a result set typically has no upward limit on the rows returned. At least, no limits except those provided by available hardware, like memory and disk space.
The general case of returning a query result from the database doesn’t exactly fit the notion and definition of data fabrication. Data fabrication is the idea that we add something to the retrieved data set. This is typically done through a query of a string literal, like this example of a Yes and No flag result set:
This syntax uses an inline view, which makes the subquery in the FROM clause into a view. The table alias in this case is a view alias named fabricated. The fabricated strings, numbers, or dates aren’t accessible outside of the inline view unless you provide column aliases. With all those pieces, you can query the results for the inline view from the containing query.
ANSI SQL:89
Oracle Syntax
The Oracle database syntax requires that we query literal values through the pseudo table DUAL. This syntax also works in MySQL and SQL Server because they facilitate cross-compatibility with Oracle.
1
2
3
4
| SELECT active_system, active_user
FROM (SELECT 'Y' AS active_system, 'Yes' AS active_user FROM dual
UNION ALL
SELECT 'N' AS active_system, 'No' AS active_user FROM dual) fabricated; |
SELECT active_system, active_user
FROM (SELECT 'Y' AS active_system, 'Yes' AS active_user FROM dual
UNION ALL
SELECT 'N' AS active_system, 'No' AS active_user FROM dual) fabricated;
MySQL & SQL Server Syntax
This syntax only works for MySQL and Microsoft™ SQL Server (and a few other databases, like PostSQL). Note that only the FROM dual was dropped from the inline view.
1
2
3
4
| SELECT active_system, active_user
FROM (SELECT 'Y' AS active_system, 'Yes' AS active_user
UNION ALL
SELECT 'N' AS active_system, 'No' AS active_user) fabricated; |
SELECT active_system, active_user
FROM (SELECT 'Y' AS active_system, 'Yes' AS active_user
UNION ALL
SELECT 'N' AS active_system, 'No' AS active_user) fabricated;
ANSI SQL:92
There is no difference between this syntax and the ANSI SQL:89 syntax because at this level, in isolation, there isn’t a join. The only difference linked to inline views between the two specification levels is their join syntax.
Oracle Syntax
The Oracle database syntax requires that we query literal values through the pseudo table DUAL. This syntax also works in MySQL and SQL Server because they facilitate cross-compatibility with Oracle.
1
2
3
4
| SELECT active_system, active_user
FROM (SELECT 'Y' AS active_system, 'Yes' AS active_user FROM dual
UNION ALL
SELECT 'N' AS active_system, 'No' AS active_user FROM dual) fabricated; |
SELECT active_system, active_user
FROM (SELECT 'Y' AS active_system, 'Yes' AS active_user FROM dual
UNION ALL
SELECT 'N' AS active_system, 'No' AS active_user FROM dual) fabricated;
MySQL & SQL Server Syntax
This syntax only works for MySQL and Microsoft™ SQL Server (and a few other databases, like PostSQL). Note that only the FROM dual was dropped from the inline view.
1
2
3
4
| SELECT active_system, active_user
FROM (SELECT 'Y' AS active_system, 'Yes' AS active_user
UNION ALL
SELECT 'N' AS active_system, 'No' AS active_user) fabricated; |
SELECT active_system, active_user
FROM (SELECT 'Y' AS active_system, 'Yes' AS active_user
UNION ALL
SELECT 'N' AS active_system, 'No' AS active_user) fabricated;
ANSI SQL: 1999
This syntax uses an subquerying factor clause or common table expression, which puts the subquery in the WITH clause as a view. The alias label in the WITH clause lets you reuse the result set like a table in the main query below. This essentially fabricates a reusable view anywhere in the query, which improves the old process where you might need to write the same inline view in multiple locations throughout the program, as subqueries and inline views. Fabricated strings, numbers, or dates aren’t accessible outside of the WITH clause unless you provide column aliases. With all those pieces, you can query the results for the inline view from the containing query.
Oracle Syntax
The Oracle database syntax requires that we query literal values through the pseudo table DUAL.
1
2
3
4
5
| WITH fabricated AS
(SELECT 'Y' AS active_system, 'Yes' AS active_user FROM dual
UNION ALL
SELECT 'N' AS active_system, 'No' AS active_user FROM dual)
SELECT active_system, active_user FROM fabricated; |
WITH fabricated AS
(SELECT 'Y' AS active_system, 'Yes' AS active_user FROM dual
UNION ALL
SELECT 'N' AS active_system, 'No' AS active_user FROM dual)
SELECT active_system, active_user FROM fabricated;
SQL Server Syntax
This syntax only works for Microsoft™ SQL Server (and a few other databases, like PostSQL). It isn’t supported in MySQL 5.1 (as of posting this article). Note that only the FROM dual was dropped from the inline view.
1
2
3
4
5
| WITH fabricated AS
(SELECT 'Y' AS active_system, 'Yes' AS active_user
UNION ALL
SELECT 'N' AS active_system, 'No' AS active_user)
SELECT active_system, active_user FROM fabricated; |
WITH fabricated AS
(SELECT 'Y' AS active_system, 'Yes' AS active_user
UNION ALL
SELECT 'N' AS active_system, 'No' AS active_user)
SELECT active_system, active_user FROM fabricated;
Rather than rehash everything in the fabricating in isolation section, this starts where that left off. You need to use joins, often cross joins, to leverage fabrication as a meaningful technique. The following query lets you grab rows from the ITEM table and augment them (that’s add to) with two potential values in an extra column for active status. This effectively multiplies the result set by the number of fabricated rows. So, for example, if you’d started with 21 rows, you’d now have 42 rows.
This is a trick to let SQL do what many feel must be done in a procedural programming language. You could then use the multiplied set to add another column for price, where the price for a row with an active column value differs from a row with an inactive column value.
ANSI SQL:89
Oracle Syntax
Like the previous example, the Oracle database syntax requires that we query literal values through the pseudo table DUAL. This syntax also works in MySQL and SQL Server if you drop the FROM dual because they facilitate cross-compatibility with Oracle. This uses a , (comma) between the ITEM table and the inline view to create a cross join. It returns two rows for each row in the ITEM table. On row has an active flag value while the other has an inactive flag value.
1
2
3
4
5
| SELECT i.item_title, fabricated.active_system
FROM item i
, (SELECT 'Y' AS active_system, 'Yes' AS active_user FROM dual
UNION ALL
SELECT 'N' AS active_system, 'No' AS active_user FROM dual) fabricated; |
SELECT i.item_title, fabricated.active_system
FROM item i
, (SELECT 'Y' AS active_system, 'Yes' AS active_user FROM dual
UNION ALL
SELECT 'N' AS active_system, 'No' AS active_user FROM dual) fabricated;
MySQL & SQL Server Syntax
As mentioned, this syntax only works for MySQL and Microsoft™ SQL Server (and a few other databases, like PostSQL). Note that only the FROM dual was dropped from the inline view.
1
2
3
4
5
| SELECT i.item_title, fabricated.active_system
FROM item i
, (SELECT 'Y' AS active_system, 'Yes' AS active_user
UNION ALL
SELECT 'N' AS active_system, 'No' AS active_user) fabricated; |
SELECT i.item_title, fabricated.active_system
FROM item i
, (SELECT 'Y' AS active_system, 'Yes' AS active_user
UNION ALL
SELECT 'N' AS active_system, 'No' AS active_user) fabricated;
ANSI SQL:92
Oracle Syntax
Like the previous example, the Oracle database syntax requires that we query literal values through the pseudo table DUAL. This syntax also works in MySQL and SQL Server if you drop the FROM dual because they facilitate cross-compatibility with Oracle. This uses a CROSS JOIN keyword between the ITEM table and the inline view to create the join. The join returns two rows for each row in the ITEM table. On row has an active flag value while the other has an inactive flag value.
1
2
3
4
5
| SELECT i.item_title, fabricated.active_system
FROM item i CROSS JOIN
(SELECT 'Y' AS active_system, 'Yes' AS active_user FROM dual
UNION ALL
SELECT 'N' AS active_system, 'No' AS active_user FROM dual) fabricated; |
SELECT i.item_title, fabricated.active_system
FROM item i CROSS JOIN
(SELECT 'Y' AS active_system, 'Yes' AS active_user FROM dual
UNION ALL
SELECT 'N' AS active_system, 'No' AS active_user FROM dual) fabricated;
MySQL & SQL Server Syntax
As mentioned, this syntax only works for MySQL and Microsoft™ SQL Server (and a few other databases, like PostSQL). Note that only the FROM dual was dropped from the inline view.
1
2
3
4
5
| SELECT i.item_title, fabricated.active_system
FROM item i CROSS JOIN
(SELECT 'Y' AS active_system, 'Yes' AS active_user
UNION ALL
SELECT 'N' AS active_system, 'No' AS active_user) fabricated; |
SELECT i.item_title, fabricated.active_system
FROM item i CROSS JOIN
(SELECT 'Y' AS active_system, 'Yes' AS active_user
UNION ALL
SELECT 'N' AS active_system, 'No' AS active_user) fabricated;
ANSI SQL: 1999
Oracle Syntax
The Oracle database syntax requires that we query literal values through the pseudo table DUAL.
1
2
3
4
5
6
| WITH fabricated AS
(SELECT 'Y' AS active_system, 'Yes' AS active_user FROM dual
UNION ALL
SELECT 'N' AS active_system, 'No' AS active_user FROM dual)
SELECT i.item_title, f.active_system
FROM item i CROSS JOIN fabricated f; |
WITH fabricated AS
(SELECT 'Y' AS active_system, 'Yes' AS active_user FROM dual
UNION ALL
SELECT 'N' AS active_system, 'No' AS active_user FROM dual)
SELECT i.item_title, f.active_system
FROM item i CROSS JOIN fabricated f;
SQL Server Syntax
This syntax only works for Microsoft™ SQL Server (and a few other databases, like PostSQL). It isn’t supported in MySQL 5.1 (as of posting this article). Note that only the FROM dual was dropped from the inline view.
1
2
3
4
5
6
| WITH fabricated AS
(SELECT 'Y' AS active_system, 'Yes' AS active_user
UNION ALL
SELECT 'N' AS active_system, 'No' AS active_user)
SELECT i.item_title, f.active_system
FROM item i CROSS JOIN fabricated f |
WITH fabricated AS
(SELECT 'Y' AS active_system, 'Yes' AS active_user
UNION ALL
SELECT 'N' AS active_system, 'No' AS active_user)
SELECT i.item_title, f.active_system
FROM item i CROSS JOIN fabricated f
About the “Fabricating thru Joins”:
It appears that the examples for oracle and mysql/ sql server are the same, with the “from dual” not being dropped.
Clay Mullins
22 Feb 10 at 6:29 pm
Good catch, I fixed it for consistency. BTW, you can include
FROM dualin both MySQL and SQL Server but they’re not required.michaelmclaughlin
23 Feb 10 at 1:14 am
Yes, but you should have had to modify them. I’ve fixed the script and reloaded it to iLearn.
michaelmclaughlin
23 Feb 10 at 1:20 am
Is the word “fabricated” a specific word to needed to create the inline view for data fabrication or can any meaningful name be used?
Spencer Wadsworth
7 Feb 11 at 6:51 pm
The idea of fabrication is that you use a small set of data to create a large set of data, or to build something from parts.
michaelmclaughlin
22 Oct 14 at 2:00 am