Retrieving Employee Information with Department Name

A classic SQL problem: employees are in one table, departments in another. You need both together.

This comes up in every application that has users and teams, products and categories, or any normalized data split across tables. The solution is always a JOIN.

The raw SQL

SELECT
    e.first_name,
    e.last_name,
    e.email,
    d.department_name
FROM
    employees e
JOIN
    departments d ON e.department_id = d.department_id

The JOIN connects each employee to their department via department_id. One query, two tables, full picture.

Using TypeORM in TypeScript

In application code, you rarely write raw SQL directly. Here's how the same query looks with TypeORM:

import { getConnection } from 'typeorm'

async function getEmployeesWithDepartments() {
    const connection = getConnection()
    const results = await connection
        .createQueryBuilder()
        .select([
            'e.first_name',
            'e.last_name',
            'e.email',
            'd.department_name'
        ])
        .from('employees', 'e')
        .innerJoin('departments', 'd', 'e.department_id = d.department_id')
        .getRawMany()

    return results
}

The query builder generates the same SQL under the hood. The benefit is type safety and composability -- you can add filters, pagination, and sorting programmatically.

LEFT JOIN vs INNER JOIN

The example above uses an INNER JOIN. This only returns employees who have a matching department. If an employee has no department assigned (null department_id), they're excluded.

If you want all employees regardless of department assignment, use a LEFT JOIN:

SELECT e.first_name, e.last_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id

Employees without a department will show NULL in the department_name column. Choose based on your requirements: do you want to hide unassigned employees or show them?