Why does an SQL query with CONCAT work if it finds an entry for CONCAT and fail if it doesn't? How to fix?

Question

MySQL

Hello. There is such a query (written in SQL and PHP):

foreach ($words as $word) {
  $tmp[] = 'CONCAT(UPPER(authors.title), " - ", UPPER(name)) LIKE UPPER("%' . $word . '%")';
}
if (!empty($tmp)) {
  $query = 'SELECT items.id FROM items';
  $query .= 'JOIN item_authors ON item_authors.item_id = items.id 
    JOIN authors ON authors.id = item_authors.author_id
    WHERE ' . implode(" OR ", $tmp);
  $items_ids = DB::query(Database::SELECT, $query)
    ->execute()
    ->as_array();
}

The query works if it finds an entry in the item_authors table. And it doesn't work if it doesn't. How to make it always work? I think this is because if the entry is not found, then there are problems with CONCAT (there is no first parameter). And that's why it doesn't work. How to fix?

Answer 1

LEFT JOIN