CODEWITHSUNDEEP
sqlpostgresql
Vortico
Vortico

Reputation: 2749

Select rows in SQL that do not exist, given the existence of their references

Suppose that I have a table rating which is unique to the pair user and client. The schema of rating would hold user_id and client_id along with some other information (like score and comment).

At some point I might have the following situation where a * represents the existence of a rating for the corresponding user_id and client_id.

        user1 user2 user3 user4
client1   *     *     *     *
client2   *     *     *     *
client3   *           *      
client4   *           *     *

In pure SQL, how can one select the rows with elements of user_id and client_id where a * does not exist?

In my example, the correct result should be

user_id  client_id
-------- ---------
2        3
2        4
4        3

Upvotes: 0

Views: 63

Answers (2)

IMSoP
IMSoP

Reputation: 97688

The way I would approach this would be by constructing a query, involving LEFT JOIN, which included every combination whether valid or not, and then filtering to just those which have no data from the rating part of the JOIN.

In this case, we want a row for every combination of user and client, so that part requires a CROSS JOIN, with the ratings table LEFT JOINed onto that.

Here's the query with all possible combinations, and ratings where available (Live Demo):

SELECT
  U.user_name,
  C.client_name,
  R.rating
FROM
  users as U
CROSS JOIN
  clients as C
LEFT JOIN
  ratings as R
  On R.user_id = U.user_id
  And R.client_id = C.client_id
ORDER BY
  U.user_name,
  C.client_name;

From that output, it's hopefully clear that to find combinations where no rating exists, we can just add WHERE R.rating IS NULL (Live Demo):

SELECT
  U.user_name,
  C.client_name,
  R.rating
FROM
  users as U
CROSS JOIN
  clients as C
LEFT JOIN
  ratings as R
  On R.user_id = U.user_id
  And R.client_id = C.client_id
WHERE
  R.rating IS NULL
ORDER BY
  U.user_name,
  C.client_name;

(Note that there is no specific syntax in SQL to say "where the Left Join didn't match anything in the Right table", you just have to pick a non-nullable column, like I have with rating in this example.)

Upvotes: 1

Ihor Romanchenko
Ihor Romanchenko

Reputation: 28521

Try something like:

SELECT *
FROM users
CROSS JOIN clients
WHERE NOT EXISTS (SELECT 1 
                  FROM rating
                  WHERE rating.user_id = users.user_id
                    AND rating.client_id = clients.client_id)

Upvotes: 3

Related Questions