How to filter result by WHERE and then execute query on that?

Question

First I had a table user_profiles where users could have their own profile and I would use the following query to get all users who do not have a profile.

user_profiles

id	user_id
1	2
2	4
3	7
4	6

SELECT * FROM users 
    LEFT JOIN user_profiles
    ON users.id = user_profiles.user_id
WHERE user_profiles.user_id IS NULL

Later on I added a type column since I didn't want to make dedicated table for each type, and since there could also be more types in the future. So I changed the table to:

user_profiles

id	user_id	type
1	2	1
2	4	2
3	2	2
4	4	1

So then I changed the query by adding an extra WHERE to the following:

SELECT * FROM users 
    LEFT JOIN user_profiles
    ON users.id = user_profiles.user_id
WHERE user_profiles.type = 1 OR user_profiles.user_id IS NULL

But it should be more like:

SELECT * FROM users 
    LEFT JOIN user_profiles
    ON users.id = user_profiles.user_id
WHERE user_profiles.type = 1 -- DO THIS FIRST
WHERE user_profiles.user_id IS NULL -- THEN THIS ON THE RESULT OF THE PREVIOUS

So basically I want to do the WHERE user_profiles.user_id IS NULL AFTER I did the WHERE user_profiles.type = 1. How could I achieve that in one query?

Is that possible and how could I achieve that with one query?

After? Simply AND the two conditions. – jarlh Aug 18 '21 at 14:36 — jarlh, Aug 18 '21 at 14:36

score 0 · Accepted Answer · answered Aug 18 '21 at 15:00

When you want to filter on the second table in a LEFT JOIN, the condition should be in the ON clause:

SELECT u.*, up.*
FROM users u LEFT JOIN
     user_profiles up
     ON u.id = up.user_id AND up.type = 1;

Your version filtering in the WHERE clause is doing something else. It is either returning users with no match or a match on type = 1. It is not returning all users, with the information from type = 1 if available.

How to filter result by WHERE and then execute query on that?

1 Answers1