如何将多结果查询与存在性校验查询合并为单条SQL语句？

Absolutely! You can combine your two queries into one by adding a correlated subquery in the SELECT clause that checks for the existence of each Volley.id in the likes table. Here's how to do it:

Merged SQL Query #

SELECT 
  `loginname`, 
  `Volley`.`id`, 
  `locality`, 
  `wherefrom`,
  -- Check if the current Volley id exists in the likes table
  IF(EXISTS(
    SELECT 1 
    FROM `likes` 
    WHERE `likes`.`Volleyid` = `Volley`.`id`
  ), 1, 0) AS is_liked
FROM `Volley` 
JOIN `register` ON `Volley`.`loginname` = `register`.`username` 
WHERE CONCAT_WS('', `wherefrom`, `locality`) LIKE '%$search%';

How It Works #

• The core of the query is your original join between Volley and register, fetching the fields you need.
• The new is_liked column uses IF(...) to return 1 if the current Volley.id exists in the likes table (via the EXISTS subquery), and 0 if it doesn't.
• The correlated subquery references Volley.id from the main query, so it runs once for each row returned by the join, checking the likes table for that specific ID.

Quick Tips #

• For better performance, add an index on likes.Volleyid—this will speed up the EXISTS check drastically, especially if the likes table is large.
• Security Note: Directly inserting $search into your query exposes you to SQL injection risks. Use prepared statements with parameterized queries instead to keep your code safe.

内容的提问来源于stack exchange，提问作者user2269164