Confusion over using LEFT JOIN on multiple tables

Question

I may be misunderstanding something basic here, but I'm struggling to find this issue being explained in my research.

Let's say we have a users table and other tables that relate to the user, let's call them:

• orders (contains a userId)
• reviews (contains a userId)
• vaccinations (contains a userId)

Each user can have many orders, or reviews, or vaccinations.

Now let's say for whatever code I'm writing I want to get all users, all their orders, reviews and vaccinations.

Should I be writing one query that left joins everything together, or three separate queries?

I.E should it be something like:

SELECT  *
    FROM  users
    LEFT JOIN  orders  ON orders.userId = users.id
    LEFT JOIN  reviews  ON reviews.userId = users.id
    LEFT JOIN  vaccinations  ON vaccinations.userId = users.id 

Or three completely separate queries like:

1. SELECT * FROM users LEFT JOIN orders ON orders.userId = users.id

2. SELECT * FROM users LEFT JOIN reviews ON reviews.userId = users.id

3. SELECT * FROM users LEFT JOIN vaccinations ON vaccinations.userId = users.id

Some background

I think what's causing me confusion is that most my time spent querying SQL is using the node ORM Sequelize. It allows me to happily query the database using a single query that on the face of it makes sense. Something like this:

return models.users.findAll({
include: [{
    model: models.orders
    required: false
},
{ 
    model: models.reviews,
    required: false
}, 
{
    model: models.vaccinations, 
    required: false
}],

});

In code it returns the results to me in a really nice ordered way that makes a lot of sense. However, what I realised when looking at the MySQL 'slow query' log is that some of these joins were returning hundreds of thousands of results per query. I guess this is due to how one extra row in one of the tables means the query then returns many more results.

Just to repeat the question to end with Should I be writing one query that left joins everything together, or three separate queries?

Thank you so much for your help.

Answer 1

There is no general answer to this. Basically: if a join explodes the numbers, it may make sense to do that in a different way, executing multiple queries, but this is basically a tradeoff you must evaluate on a case by case basis.

In your example i would generally refuse the query - there is hardly any scenario you need ALL users with all that expanded. The problem here does not start with the expansions, it starts with no filtering. Not even paging. THIS is where your problems (in that beample) start.

Answer 2

Query 1: This returns lots of rows because there is no relationship between all 4 queries. It also returns NULLs for anti-vaxxers:

SELECT  *
    FROM  users
    LEFT JOIN  orders  ON orders.userId = users.id
    LEFT JOIN  reviews  ON reviews.userId = users.id
    LEFT JOIN  vaccinations  ON vaccinations.userId = users.id 

Query 2: This removes them from the list.

SELECT  *
    FROM  users
    JOIN  orders  ON orders.userId = users.id
    JOIN  reviews  ON reviews.userId = users.id
    JOIN  vaccinations  ON vaccinations.userId = users.id 

Query 3: This gives only one line per user, and includes some counts:

SELECT  users.*,
        ( SELECT COUNT(*) FROM orders  ON orders.userId = users.id ) AS order_ct,
        ( SELECT COUNT(*) FROM reviews  ON reviews.userId = users.id ) AS review_ct
        ( SELECT COUNT(*) FROM vaccinations  ON vaccinations.userId = users.id ) AS vax_ct
    FROM  users

Query 4: This gives 3 sets of info. It does not list vaccine info for those who have not had the jab.

SELECT  *  FROM  users
    JOIN  orders  ON orders.userId = users.id;
SELECT  *  FROM  users
    JOIN  reviews  ON reviews.userId = users.id;
SELECT  *  FROM  users
    JOIN  vaccinations  ON vaccinations.userId = users.id ;

Query 5: This gives 3 sets of info, but includes NULLs for the anti-vaxxers:

SELECT  *  FROM  users
    LEFT JOIN  orders  ON orders.userId = users.id;
SELECT  *  FROM  users
    LEFT JOIN  reviews  ON reviews.userId = users.id;
SELECT  *  FROM  users
    LEFT JOIN  vaccinations  ON vaccinations.userId = users.id ;

It is unclear what you want. Query 4 or 5 may be what you need.

(I am not knowledgeable enough in the framework, so I can't further obfuscate into its formulation.)