Confusion over using LEFT JOIN on multiple tables

0 votes
2 answers
7660 views
                          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.
                        
Asked by pezza3434 (1 rep)
Dec 5, 2021, 08:10 PM
Last activity: Jul 25, 2025, 12:02 PM
Confusion over using LEFT JOIN on multiple tables

Related Questions
