Introduction
Sequelize performs joins using the include option in queries, not raw SQL JOIN syntax. You first define associations between models (hasMany, belongsTo, hasOne, belongsToMany), then use include in findAll or findOne to eagerly load related data. Sequelize translates these into SQL LEFT OUTER JOIN statements by default. Understanding the association types and include syntax is the key to effective join queries in Sequelize.
Define Associations
1const { Sequelize, DataTypes } = require('sequelize');
2const sequelize = new Sequelize('sqlite::memory:');
3
4// Models
5const User = sequelize.define('User', {
6 name: DataTypes.STRING,
7 email: DataTypes.STRING,
8});
9
10const Post = sequelize.define('Post', {
11 title: DataTypes.STRING,
12 content: DataTypes.TEXT,
13});
14
15const Comment = sequelize.define('Comment', {
16 body: DataTypes.TEXT,
17});
18
19const Tag = sequelize.define('Tag', {
20 name: DataTypes.STRING,
21});
22
23// Associations
24User.hasMany(Post); // User has many Posts
25Post.belongsTo(User); // Post belongs to a User
26
27Post.hasMany(Comment); // Post has many Comments
28Comment.belongsTo(Post); // Comment belongs to a Post
29
30User.hasMany(Comment); // User has many Comments
31Comment.belongsTo(User); // Comment belongs to a User
32
33// Many-to-many through a join table
34Post.belongsToMany(Tag, { through: 'PostTags' });
35Tag.belongsToMany(Post, { through: 'PostTags' });
Basic Join: include
1// Find all users with their posts (LEFT JOIN)
2const users = await User.findAll({
3 include: [Post],
4});
5
6// SQL: SELECT "User".*, "Posts".* FROM "Users" AS "User"
7// LEFT OUTER JOIN "Posts" ON "User"."id" = "Posts"."UserId"
8
9console.log(JSON.stringify(users, null, 2));
10// [
11// {
12// "id": 1,
13// "name": "Alice",
14// "Posts": [
15// { "id": 1, "title": "First Post", "UserId": 1 },
16// { "id": 2, "title": "Second Post", "UserId": 1 }
17// ]
18// }
19// ]
Nested Joins (Multiple Levels)
1// Users → Posts → Comments (3-table join)
2const users = await User.findAll({
3 include: [{
4 model: Post,
5 include: [Comment], // Nested include
6 }],
7});
8
9// SQL: SELECT ... FROM "Users"
10// LEFT JOIN "Posts" ON ...
11// LEFT JOIN "Comments" ON ...
Filtering Joined Data (WHERE on Association)
1// Find users who have posts with specific titles
2const users = await User.findAll({
3 include: [{
4 model: Post,
5 where: { title: 'First Post' }, // Only include matching posts
6 }],
7});
8
9// This filters the JOIN — only returns users with a matching post
10// SQL: SELECT ... FROM "Users"
11// INNER JOIN "Posts" ON ... WHERE "Posts"."title" = 'First Post'
Adding where to an include changes it from LEFT JOIN to INNER JOIN. To keep LEFT JOIN with filtering:
1const users = await User.findAll({
2 include: [{
3 model: Post,
4 where: { title: 'First Post' },
5 required: false, // Keep LEFT JOIN even with where clause
6 }],
7});
required: true vs required: false
1// INNER JOIN — only users who have posts
2const users = await User.findAll({
3 include: [{
4 model: Post,
5 required: true, // INNER JOIN (default when 'where' is specified)
6 }],
7});
8
9// LEFT JOIN — all users, even without posts
10const allUsers = await User.findAll({
11 include: [{
12 model: Post,
13 required: false, // LEFT OUTER JOIN (default when no 'where')
14 }],
15});
Many-to-Many Join
1// Find posts with their tags
2const posts = await Post.findAll({
3 include: [Tag],
4});
5
6// SQL: SELECT ... FROM "Posts"
7// LEFT JOIN "PostTags" ON ...
8// LEFT JOIN "Tags" ON ...
9
10// Each post has a Tags array:
11// { "title": "My Post", "Tags": [{ "name": "javascript" }, { "name": "nodejs" }] }
Selecting Specific Columns
1// Only include specific attributes from the joined model
2const users = await User.findAll({
3 attributes: ['id', 'name'],
4 include: [{
5 model: Post,
6 attributes: ['title'], // Only fetch post titles, not content
7 }],
8});
Aliased Associations
1// When you have multiple associations to the same model
2User.hasMany(Post, { as: 'writtenPosts', foreignKey: 'authorId' });
3User.hasMany(Post, { as: 'editedPosts', foreignKey: 'editorId' });
4
5const users = await User.findAll({
6 include: [
7 { model: Post, as: 'writtenPosts' },
8 { model: Post, as: 'editedPosts' },
9 ],
10});
Ordering Joined Data
1// Order posts within each user
2const users = await User.findAll({
3 include: [Post],
4 order: [
5 ['name', 'ASC'], // Order users by name
6 [Post, 'createdAt', 'DESC'], // Order each user's posts by date
7 ],
8});
9
10// Nested ordering
11const users = await User.findAll({
12 include: [{
13 model: Post,
14 include: [Comment],
15 }],
16 order: [
17 [Post, Comment, 'createdAt', 'ASC'], // Order comments within posts
18 ],
19});
Counting and Aggregating with Joins
1// Count posts per user
2const users = await User.findAll({
3 attributes: {
4 include: [
5 [sequelize.fn('COUNT', sequelize.col('Posts.id')), 'postCount'],
6 ],
7 },
8 include: [{
9 model: Post,
10 attributes: [], // Don't include post fields
11 }],
12 group: ['User.id'],
13});
Common Pitfalls
Forgetting to define associations before querying: include: [Post] only works if User.hasMany(Post) (or Post.belongsTo(User)) was called first. Without the association definition, Sequelize throws Error: Post is not associated to User!.
N+1 query problem: Without include, accessing user.getPosts() for each user in a loop triggers a separate SQL query per user. Use include to load all related data in a single query (eager loading).
required: true when where is used: Adding where inside an include automatically switches to INNER JOIN. Users without matching posts are excluded from results. Set required: false explicitly if you want all users regardless of the filter match.
Circular or redundant includes: Including a model that includes its parent back creates circular queries and can cause performance issues or stack overflows. Only include in one direction per query.
Alias mismatch: If you define an association with as: 'writtenPosts', you must use the same as value in the include. Using the model directly (include: [Post]) will not find the aliased association.
Summary
Define associations first (hasMany, belongsTo, belongsToMany), then use include in queries
include: [Model] performs a LEFT OUTER JOIN by default
Add where inside include to filter joined data (switches to INNER JOIN)
Use required: false to force LEFT JOIN even with where conditions
Nest include within include for multi-level joins
Use attributes to limit which columns are fetched from joined tables
