Is it possible to perform an outer join between temporary tables?

Yes, temporary tables can be joined outside. 

In MySQL, you can create temporary tables with CREATE TEMPORARY TABLE , but these temporary tables can be outer-joined just like regular tables.

To test with actual SQL, first create a temporary table with the following sample code and register test data.

/ * Create a temporary table for transactions * /
CREATE TEMPORARY TABLE ORDER_LIST (
 ORDER_ID INT,
 ITEM_ID INT
);

/ * Create master temporary table * /
CREATE TEMPORARY TABLE ITEM_LIST (
 ITEM_ID INT,
 ITEM_NAME VARCHAR (20)
);

/ * Register test data * /
INSERT INTO ORDER_LIST VALUES (1, 1);
INSERT INTO ORDER_LIST VALUES (2, 1);
INSERT INTO ORDER_LIST VALUES (3, 2);
INSERT INTO ORDER_LIST VALUES (4, 3);
INSERT INTO ORDER_LIST VALUES (5, 2);
INSERT INTO ITEM_LIST VALUES (1, 'cocoa');
INSERT INTO ITEM_LIST VALUES (2, 'coffee');
INSERT INTO ITEM_LIST VALUES (3, 'tea');

Try to join the created temporary tables with the following SQL.

/ * Externally join temporary tables to extract all records * /
SELECT
 ORDER_LIST.ORDER_ID,
 ORDER_LIST.ITEM_ID,
 ITEM_LIST.ITEM_NAME
FROM
 ORDER_LIST
 LEFT OUTER JOIN ITEM_LIST
     ON ORDER_LIST.ITEM_ID = ITEM_LIST.ITEM_ID
ORDER BY
 ORDER_LIST.ORDER_ID;

The execution result is as follows, and it can be confirmed that temporary tables can be joined with each other.

+ ---------- + --------- + ----------- +
| ORDER_ID | ITEM_ID | ITEM_NAME |
+ ---------- + --------- + ----------- +
| 1 | 1 | cocoa |
| 2 | 1 | cocoa |
| 3 | 2 | coffee |
| 4 | 3 | tea |
| 5 | 2 | coffee |
+ ---------- + --------- + ----------- +
5 rows in set (0.00 sec)

This post is submitted by one of our members. You may submit a new post here.