是否可以在一个MySQL查询中执行此操作？

I have two tables:

conversation_participants

id      | conversation_id | user_id
int(11) |  int(11)        |   int(11)

users

id      | username   | ....
int(11) | varchar(20)| ....

I am trying to select all the users which have a certain string in their username which are not (yet) participants in a particular conversation. Currently I have two SQL queries:

SELECT user_id FROM conversation_participants WHERE conversation_id=?

and

SELECT id, username from users WHERE username LIKE '%%%{$_GET['q']}%%' ORDER BY id DESC LIMIT 10

And remove all the results from the second query which have the same user_id as the ones from the first query. Is there any way to merge these two queries into one ?

Thank you in advance

SELECT id, username from users 
WHERE username LIKE '%%%{$_GET['q']}%%' 
and username not in 
(
  SELECT user_id FROM conversation_participants 
  WHERE conversation_id=users.conversation_id
)
ORDER BY id DESC LIMIT 10

Don't build your sql statements concatenating strings. You should use some sort of utility method to sanitize your input. Your statement is prone to sql injection attacks.

PHP has several functions for this. Look into mysql_real_escape_string

In addition to the above answer I have to add, that you should not use $_GET (etc) parameters directly in your query without checking it for SQL injections.

You can also use JOIN, which might be a bit faster.

SELECT u.id, u.username from users u LEFT JOIN conversation_participants c ON c.user_id = u.id WHERE u.username LIKE '%%%{$_GET['q']}%%' AND c.conversation_id != ? ORDER BY u.id DESC LIMIT 10

In addition to the above answer, you could remove your "id" field in the table conversation_participants and define the 2 fields conversation_id and user_id as primary key.

You can do this with a LEFT JOIN together with IS NULL

SELECT 
    u.id, 
    u.username 
from 
    users u
    LEFT JOIN
         conversation_participants cp 
    ON (u.id=cp.user_id AND cp.conversation_id=?)
WHERE 
    username LIKE '%%%{$_GET['q']}%%' AND
    cp.user_id IS NULL
ORDER BY id DESC LIMIT 10

I believe this is a more optimal solution than using NOT IN() and a sub-query for performance reasons.