title: Use same alias for union query SQL
body: <p>So I came across a problem/question yesterday. <br>
  I am building a chat (with AJAX) and use two tables:</p>
  
  <p><code>TABLE users -&gt; 'name', 'username', 'password', 'time'</code> <br>
  <code>TABLE messages -&gt; 'sendFrom', 'sendTo', 'message', 'time'</code></p>
  
  <p>So an example message now would be <br>
  <code>'foo' | 'bar' | 'Hey, how are you?' | 130611134427611</code></p>
  
  <p>I was told the correct way to do this is, instead, to use an <code>ID</code> column, and use that as a Primary Key instead of the username (which, anyway, makes sense).</p>
  
  <p>OK, so now this looks like <br>
  <code>TABLE users -&gt; 'ID', 'name', 'username', 'password', 'time'</code> <br>
  <code>TABLE messages -&gt; 'sendFrom', 'sendTo', 'message', 'time'</code></p>
  
  <p>So an example message now would be <br>
  <code>'22' | '7' | 'Hey, how are you?' | 130611134427611</code></p>
  
  <p>I've managed to <code>JOIN</code> both tables to return the rows as on the first example message, but since I am detecting user keypresses too, I need to scan the table twice, so:</p>
  
  <pre><code>SELECT * 
  FROM (SELECT * 
        FROM (SELECT * 
              FROM messages 
              WHERE sendTo = '$username'
                AND time &gt; (SELECT time FROM users 
                            WHERE username = '$username' LIMIT 1)
                AND message &lt;&gt; '$keypressCode' 
              ORDER BY time DESC LIMIT 30) 
        ORDER BY time ASC) 
  UNION
  SELECT * 
  FROM (SELECT * 
        FROM messages 
        WHERE message = '$keypressCode'
          AND time &gt; (SELECT time FROM users 
                      WHERE username = '$username' LIMIT 1)
          AND sendTo = '$username' LIMIT 1);
  </code></pre>
  
  <p>But now, of course, I don't just select from messages; instead, I use a long query like</p>
  
  <pre><code>SELECT * FROM (
      SELECT u1.ID as sendTo, u2.ID as sendFrom, messages.message, .....
      .....
      .....
      .....
      .....
  ) as messages;
  </code></pre>
  
  <p>that MUST BE INSERTED just in the place of <code>messages</code> (I haven't tried this yet, but I think is like that. See, the thing is I DuckDuckGo'ed and Googled and found nothing, so I came here)</p>
  
  <hr>
  
  <p>My first question is:
  Is there a way to use <code>ALIAS</code> for the table <code>messages</code> so I don't have to scan it TWICE? So, instead, I just save the above query using <code>ALIAS</code> as a table called <code>messages</code> and select data from it twice, once in each part of <code>UNION</code>.</p>
  
  <p>In addition, the answer to the first question would also be an answer for:
  Is there a way to use <code>ALIAS</code> to save the <code>time</code> selected from the table? (since, again, I am searching for it TWICE).</p>
  
  <hr>
  
  <p>In practice, what I am doing may not be unefficient (since there will be at most 20 users), but what if?
  Also, I am a mathematician, and like it or not, I like to worry a lot about efficiency!</p>
  
  <p>Thank you so much in advance, I hope I made myself clear.</p>