Select_queries
Chapter 17: Select Queries & Joins
Section titled “Chapter 17: Select Queries & Joins”Advanced SELECT and JOIN Operations
Section titled “Advanced SELECT and JOIN Operations”17.1 SELECT Deep Dive
Section titled “17.1 SELECT Deep Dive”Select All Columns
Section titled “Select All Columns”// Select all columns from main tableconst users = await userRepository .createQueryBuilder('user') .getMany();
// SQL: SELECT * FROM users userSelect Specific Columns
Section titled “Select Specific Columns”// Select specific columnsconst users = await userRepository .createQueryBuilder('user') .select([ 'user.id', 'user.name', 'user.email', ]) .getMany();
// SQL: SELECT user.id, user.name, user.email FROM users userSelect with Aliases
Section titled “Select with Aliases”// Select with custom column namesconst result = await userRepository .createQueryBuilder('user') .select('user.name', 'userName') .addSelect('user.email', 'userEmail') .addSelect('COUNT(post.id)', 'postCount') .leftJoin('user.posts', 'post') .groupBy('user.id') .getRawMany();
// Returns: [{ userName: 'John', userEmail: 'john@example.com', postCount: '5' }]Select with Expressions
Section titled “Select with Expressions”// Select with SQL expressionsconst result = await userRepository .createQueryBuilder('user') .select('CONCAT(user.firstName, \' \', user.lastName)', 'fullName') .addSelect('LOWER(user.email)', 'emailLower') .addSelect('EXTRACT(YEAR FROM user.createdAt)', 'joinYear') .getRawMany();
// SQL: SELECT CONCAT(user.firstName, ' ', user.lastName) AS "fullName",// LOWER(user.email) AS "emailLower",// EXTRACT(YEAR FROM user.createdAt) AS "joinYear"// FROM users userSelect Distinct
Section titled “Select Distinct”// Select distinct valuesconst result = await userRepository .createQueryBuilder('user') .select('user.role', 'role') .distinct(true) .getRawMany();
// SQL: SELECT DISTINCT user.role FROM users user17.2 JOIN Types
Section titled “17.2 JOIN Types” JOIN Types Visualized +------------------------------------------------------------------+ | | | Table A Table B | | +--------+ +--------+ | | | id | | id | | | +--------+ +--------+ | | | 1 | | 1 | | | +--------+ +--------+ | | | 2 | | 2 | | | +--------+ +--------+ | | | 3 | | 4 | | | +--------+ +--------+ | | | | INNER JOIN: Only matching rows | | +--------+ | | | A.id 1 | <-- Matches B.id 1 | | +--------+ | | | A.id 2 | <-- Matches B.id 2 | | +--------+ | | | | LEFT JOIN: All from A, matching from B | | +--------+--------+ | | | A.id 1 | B.id 1 | | | +--------+--------+ | | | A.id 2 | B.id 2 | | | +--------+--------+ | | | A.id 3 | NULL | <-- No match in B | | +--------+--------+ | | | | RIGHT JOIN: All from B, matching from A | | +--------+--------+ | | | A.id 1 | B.id 1 | | | +--------+--------+ | | | A.id 2 | B.id 2 | | | +--------+--------+ | | | NULL | B.id 4 | <-- No match in A | | +--------+--------+ | | | +------------------------------------------------------------------+17.3 LEFT JOIN
Section titled “17.3 LEFT JOIN”Basic Left Join
Section titled “Basic Left Join”// Left join - includes users without postsconst users = await userRepository .createQueryBuilder('user') .leftJoin('user.posts', 'post') .getMany();
// SQL: SELECT * FROM users user// LEFT JOIN posts post ON post.authorId = user.idLeft Join and Select
Section titled “Left Join and Select”// Left join with selection - loads related dataconst users = await userRepository .createQueryBuilder('user') .leftJoinAndSelect('user.posts', 'post') .getMany();
// Returns users with their posts loaded// users[0].posts = [{ id: 1, title: 'Post 1' }, ...]Left Join with Conditions
Section titled “Left Join with Conditions”// Left join with additional conditionconst users = await userRepository .createQueryBuilder('user') .leftJoinAndSelect( 'user.posts', 'post', 'post.published = :published', { published: true } ) .getMany();
// SQL: LEFT JOIN posts post// ON post.authorId = user.id AND post.published = trueSelect Specific Columns from Join
Section titled “Select Specific Columns from Join”const users = await userRepository .createQueryBuilder('user') .select(['user.id', 'user.name']) .leftJoin('user.posts', 'post') .addSelect(['post.id', 'post.title']) .getMany();
// Returns users with posts containing only id and title17.4 INNER JOIN
Section titled “17.4 INNER JOIN”Basic Inner Join
Section titled “Basic Inner Join”// Inner join - only users with postsconst users = await userRepository .createQueryBuilder('user') .innerJoin('user.posts', 'post') .getMany();
// SQL: SELECT * FROM users user// INNER JOIN posts post ON post.authorId = user.idInner Join and Select
Section titled “Inner Join and Select”const users = await userRepository .createQueryBuilder('user') .innerJoinAndSelect('user.posts', 'post') .getMany();
// Only returns users that have postsInner Join with Conditions
Section titled “Inner Join with Conditions”const users = await userRepository .createQueryBuilder('user') .innerJoinAndSelect( 'user.posts', 'post', 'post.viewCount > :minViews', { minViews: 100 } ) .getMany();17.5 Multiple Joins
Section titled “17.5 Multiple Joins”Join Multiple Tables
Section titled “Join Multiple Tables”const users = await userRepository .createQueryBuilder('user') .leftJoinAndSelect('user.posts', 'post') .leftJoinAndSelect('post.comments', 'comment') .leftJoinAndSelect('comment.author', 'commentAuthor') .getMany();
// Returns users with posts, comments, and comment authorsMixed Join Types
Section titled “Mixed Join Types”const users = await userRepository .createQueryBuilder('user') // All users, with their profile if exists .leftJoinAndSelect('user.profile', 'profile') // Only users with published posts .innerJoinAndSelect('user.posts', 'post', 'post.published = :pub', { pub: true }) // All posts, with comments if exist .leftJoinAndSelect('post.comments', 'comment') .getMany();17.6 JOIN without Relations
Section titled “17.6 JOIN without Relations”Join Arbitrary Tables
Section titled “Join Arbitrary Tables”import { User } from './user.entity';import { Post } from './post.entity';
const result = await userRepository .createQueryBuilder('user') .innerJoin(Post, 'post', 'post.authorId = user.id') .select(['user.id', 'user.name', 'post.title']) .getRawMany();
// Join without entity relation definedJoin with Subquery
Section titled “Join with Subquery”const result = await userRepository .createQueryBuilder('user') .innerJoin( (qb) => qb .select('authorId') .from(Post, 'post') .where('post.viewCount > :views', { views: 100 }) .groupBy('post.authorId'), 'popular_authors', 'popular_authors.authorId = user.id' ) .getMany();17.7 Self Join
Section titled “17.7 Self Join”// Self join for hierarchical dataimport { Category } from './category.entity';
const categories = await categoryRepository .createQueryBuilder('category') .leftJoinAndSelect('category.parent', 'parent') .leftJoinAndSelect('category.children', 'children') .getMany();
// Returns categories with parent and children17.8 JOIN with Pagination
Section titled “17.8 JOIN with Pagination”// Pagination with joinsconst [users, total] = await userRepository .createQueryBuilder('user') .leftJoinAndSelect('user.posts', 'post') .where('user.isActive = :active', { active: true }) .orderBy('user.createdAt', 'DESC') .skip(0) .take(10) .getManyAndCount();
console.log(`Found ${total} users, showing ${users.length}`);17.9 Aggregation with Joins
Section titled “17.9 Aggregation with Joins”Count with Join
Section titled “Count with Join”const result = await userRepository .createQueryBuilder('user') .leftJoin('user.posts', 'post') .select('user.id', 'userId') .addSelect('user.name', 'userName') .addSelect('COUNT(post.id)', 'postCount') .groupBy('user.id') .getRawMany();
// Returns: [{ userId: 1, userName: 'John', postCount: '5' }, ...]Sum with Join
Section titled “Sum with Join”const result = await userRepository .createQueryBuilder('user') .leftJoin('user.orders', 'order') .select('user.id', 'userId') .addSelect('SUM(order.total)', 'totalSpent') .groupBy('user.id') .getRawMany();Average with Join
Section titled “Average with Join”const result = await postRepository .createQueryBuilder('post') .leftJoin('post.ratings', 'rating') .select('post.id', 'postId') .addSelect('AVG(rating.value)', 'averageRating') .groupBy('post.id') .getRawMany();17.10 Complex Join Example
Section titled “17.10 Complex Join Example”// Complex query with multiple joins and conditionsconst result = await userRepository .createQueryBuilder('user') // Select user columns .select([ 'user.id AS "userId"', 'user.name AS "userName"', 'user.email AS "userEmail"', ]) // Join posts .leftJoin('user.posts', 'post') // Add post count .addSelect('COUNT(DISTINCT post.id)', 'postCount') // Join comments .leftJoin('post.comments', 'comment') // Add comment count .addSelect('COUNT(comment.id)', 'commentCount') // Join profile .leftJoin('user.profile', 'profile') // Add profile bio .addSelect('profile.bio', 'bio') // Where conditions .where('user.isActive = :active', { active: true }) .andWhere('user.role IN (:...roles)', { roles: ['admin', 'user'] }) // Group by user .groupBy('user.id') // Add profile to group by .addGroupBy('profile.id') // Having condition .having('COUNT(post.id) > :minPosts', { minPosts: 0 }) // Order by post count .orderBy('postCount', 'DESC') // Limit .limit(10) // Get raw results .getRawMany();
// Generated SQL:// SELECT user.id AS "userId",// user.name AS "userName",// user.email AS "userEmail",// COUNT(DISTINCT post.id) AS "postCount",// COUNT(comment.id) AS "commentCount",// profile.bio AS "bio"// FROM users user// LEFT JOIN posts post ON post.authorId = user.id// LEFT JOIN comments comment ON comment.postId = post.id// LEFT JOIN profiles profile ON profile.userId = user.id// WHERE user.isActive = true AND user.role IN ('admin', 'user')// GROUP BY user.id, profile.id// HAVING COUNT(post.id) > 0// ORDER BY postCount DESC// LIMIT 1017.11 Performance Tips for Joins
Section titled “17.11 Performance Tips for Joins” JOIN Performance Tips +------------------------------------------------------------------+ | | | 1. Use innerJoin when you don't need NULL values | | - Faster than leftJoin | | - Smaller result set | | | | 2. Select only needed columns | | - Reduces data transfer | | - Better performance | | | | 3. Add indexes on join columns | | - Foreign keys should be indexed | | - Speeds up join operations | | | | 4. Limit result set | | - Use skip/take for pagination | | - Avoid loading all data | | | | 5. Use getRawMany() for aggregates | | - Avoids entity hydration overhead | | - Faster for simple queries | | | +------------------------------------------------------------------+17.12 Summary
Section titled “17.12 Summary” JOIN Quick Reference +------------------------------------------------------------------+ | | | Join Type | Method | Result | | -------------------|---------------------------|---------------| | LEFT JOIN | leftJoin() | All + matches | | LEFT JOIN SELECT | leftJoinAndSelect() | + loaded data | | INNER JOIN | innerJoin() | Matches only | | INNER JOIN SELECT | innerJoinAndSelect() | + loaded data | | RIGHT JOIN | rightJoin() | All B + match | | RIGHT JOIN SELECT | rightJoinAndSelect() | + loaded data | | | +------------------------------------------------------------------+Next Chapter
Section titled “Next Chapter”Chapter 18: Where Clauses & Conditions
Last Updated: February 2026