Q:

SQL: DELETE,SELECT and LEFT JOIN - can I do that?

Basically I am trying to unfollow liked restaurant and get at the same time all followed restaurant without the one which has just been deleted and also select title from restaurant table all in one query. 
I got this so far: 
WITH deleted AS (DELETE from users_liked_restaurants WHERE users_liked_restaurants.user_id = 1 AND users_liked_restaurants.restaurant_id = 2 returning user_id)
SELECT restaurants.restaurant_id, restaurants.title FROM restaurants
LEFT JOIN users_liked_restaurants ON users_liked_restaurants.restaurant_id = restaurants.restaurant_id WHERE users_liked_restaurants.user_id = 1;

However this returns all liked restaurants by the user_id = 1 including the one I just deleted.

A:

The reason is that both parts of the query run inside the same statement, so one doesn't see the effects of the other.
Have the CTE return the restaurant_id of the deleted restaurant and exclude these restaurants from the query result:
WITH deleted AS (
  DELETE from users_liked_restaurants
  WHERE users_liked_restaurants.user_id = 1
    AND users_liked_restaurants.restaurant_id = 2
  RETURNING restaurant_id
)
SELECT restaurants.restaurant_id,
       restaurants.title
FROM restaurants
   LEFT JOIN users_liked_restaurants
      ON users_liked_restaurants.restaurant_id = restaurants.restaurant_id
WHERE users_liked_restaurants.user_id = 1
  AND NOT EXISTS (
         SELECT 1 FROM deleted
         WHERE deleted.restaurent_id = restaurants.restaurant_id
      );

