How do I store intermediate data and share it between SQL queries?

Let's say I have four table (A,B,C,D), empId is common in all.

After running some complex query I get list of eligible empIds.

Now I need to run Select .... from ..... where empId IN LIST.

I want to store this list of empIds in some variable so that I don't have run complex query every time to get list of empIds.

Is there anyway to store empIds in some temporary space?

This way I can cut down i开发者_运维知识库n running this complex query every time.

You could create a table in memory using the syntax:

Select field1,field2,...fieldN INTO #tmp FROM SourceTable

Now you can access the contents of the table #tmp.

Since databases are optimized for joins you should store the expensive-to-get empIds in a table or view, and then join to that.

1. create table
2. insert ids to that table
3. join to temp table for free (or for cheap)

Since MySQL is allegedly "The world's most popular open source database" here is the syntax from their documentation:

Step 1
http://dev.mysql.com/doc/refman/5.1/en/create-table.html

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options]
    [partition_options]

Example for didactic use only:

CREATE TABLE expensive_ids (id);

Step 2
http://dev.mysql.com/doc/refman/5.1/en/insert-select.html

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name [(col_name,...)]
    SELECT ...
    [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

Example for didactic use only:

INSERT INTO expensive_ids (id) VALUES (1);

Step 3
http://dev.mysql.com/doc/refman/5.1/en/select.html

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
      [HIGH_PRIORITY]
      [STRAIGHT_JOIN]
      [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
      [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr ...]
    [FROM table_references

Example for didactic use only:

SELECT my_other_table.id, expensive_ids.id
  FROM my_other_table 
       LEFT JOIN expensive_ids 
       ON expensive_ids.id = my_other_table.id;