title: mysql: How to exclude rows from table which exist in table_alias with good perfomanse?
body: <p>I've sql with NOT EXIST and <strong>it works very slowly</strong> in big db:</p>
  
  <pre><code>SELECT *
  FROM
      (
          SELECT * FROM profiles ORDER BY id DESC
          /* I need this order HERE! More info: https://stackoverflow.com/q/43516402/2051938 */
      ) AS users
  WHERE
      NOT EXISTS (
          SELECT *
          FROM request_for_friendship
          WHERE
              (
                  request_for_friendship.from_id = 1
                  AND
                  request_for_friendship.to_id = users.id
              )
              OR
              (
                  request_for_friendship.from_id = users.id
                  AND
                  request_for_friendship.to_id = 1
              )
      )
  LIMIT 0 , 1;
  </code></pre>
  
  <p>And I think I need to get <code>request_for_friendship</code> with some <code>WHERE</code> and after that check <code>NOT EXIST</code>, like this:</p>
  
  <pre><code>SELECT users.*
  FROM
      (
          SELECT * FROM profiles ORDER BY id DESC
      ) AS users,
      (
          SELECT *
          FROM request_for_friendship
          WHERE
              request_for_friendship.from_id = 1
              OR
              request_for_friendship.to_id = 1
      ) AS exclude_table
  WHERE
      NOT EXISTS
      (
          SELECT *
          FROM exclude_table /* #1146 - Table 'join_test.exclude_table' doesn't exist */
          WHERE
              request_for_friendship.from_id = users.id
              OR
              request_for_friendship.to_id = users.id
      )
  LIMIT 0 , 1;
  </code></pre>
  
  <p>But it doesn't work: <code>#1146 - Table 'join_test.exclude_table' doesn't exist</code></p>
  
  <p>My tables:</p>
  
  <p>1) <code>profiles</code></p>
  
  <pre><code>+----+---------+
  | id | name    |
  +----+---------+
  |  1 | WILLIAM |
  |  2 | JOHN    |
  |  3 | ROBERT  |
  |  4 | MICHAEL |
  |  5 | JAMES   |
  |  6 | DAVID   |
  |  7 | RICHARD |
  |  8 | CHARLES |
  |  9 | JOSEPH  |
  | 10 | THOMAS  |
  +----+---------+
  </code></pre>
  
  <p>2) <code>request_for_friendship</code></p>
  
  <pre><code>+----+---------+-------+
  | id | from_id | to_id |
  +----+---------+-------+
  |  1 |       1 |     2 |
  |  2 |       1 |     3 |
  |  3 |       1 |     8 |
  |  5 |       4 |     1 |
  |  6 |       9 |     1 |
  +----+---------+-------+
  </code></pre>
  
  <p><strong>How to do some like this or better for perfomance?</strong></p>
  
  <p>p.s. I need to get <strong>only 1 row</strong> from table</p>
  
  <p><a href="http://rextester.com/DTA64368" rel="nofollow noreferrer"><strong>Demo: http://rextester.com/DTA64368</strong></a></p>
  
  <p>I've already tried <code>LEFT JOIN</code>, but I've problem with order with him. <a href="https://stackoverflow.com/q/43678470/2051938">mysql: how to save ORDER BY after LEFT JOIN without reorder?</a></p>