Master SQL Server From Scratch

Clear, interactive, and structured SQL Server lessons designed for beginners and developers.

COURSE SYLLABUS
1. Introduction to SQL Server
Introduction to SQL Server SQL Server Installation SQL Server Fundamentals
2. Data Types
Data Types Numeric Data Types Character & String Data Types Date & Time Data Types Other SQL Server Data Types
3. Database & Tables
Database & Tables Introduction Creating a Database Creating Tables Table Structure Altering Tables Dropping Tables
4. Constraints
Constraints Introduction PRIMARY KEY FOREIGN KEY UNIQUE Constraint NOT NULL & DEFAULT CHECK Constraint
5. SQL Basics
SQL Basics Introduction SQL Commands SQL Command Categories SELECT Statement INSERT, UPDATE & DELETE
6. CRUD Operations
CRUD Operations Introduction INSERT Operation SELECT Operation UPDATE Operation DELETE Operation
7. SQL Queries
Queries Introduction WHERE Clause ORDER BY Clause DISTINCT LIKE Operator IN, BETWEEN & IS NULL
8. Joins
Joins Introduction INNER JOIN LEFT JOIN RIGHT JOIN FULL OUTER JOIN CROSS JOIN
9. SQL Functions
SQL Functions Introduction String Functions Numeric Functions Date & Time Functions Conversion Functions NULL Functions
10. Grouping & Aggregate Functions
GROUP BY Aggregate Functions COUNT() SUM() AVG() MIN() & MAX()
11. Subqueries
Subqueries Introduction Single-Row Subquery Multiple-Row Subquery Correlated Subquery Subquery with DML
12. Views
SQL Views Introduction Creating a View Updating a View Altering a View Dropping a View
13. Stored Procedures
Stored Procedures Introduction Creating a Stored Procedure Input Parameters Output Parameters Executing & Modifying Stored Procedures
14. Triggers
Triggers Introduction AFTER INSERT Trigger AFTER UPDATE Trigger AFTER DELETE Trigger INSTEAD OF Trigger
15. Transactions
Transactions Introduction COMMIT ROLLBACK SAVEPOINT Transaction with TRY...CATCH
16. Indexes
Indexes Introduction Clustered Index Non-Clustered Index Creating & Dropping Indexes Index Performance
17. Common Table Expressions
CTE Introduction Simple CTE CTE with JOIN CTE with Aggregate Functions Recursive CTE
18. Window Functions
Window Functions Introduction ROW_NUMBER() RANK() and DENSE_RANK() LEAD() and LAG() PARTITION BY
19. Advanced SQL
Advanced SQL Introduction Temporary Tables Table Variables Dynamic SQL Error Handling Performance Optimization

CTE with JOIN

Learn how to use Common Table Expressions with JOIN operations in SQL Server.

What is CTE with JOIN?

A CTE can be used together with SQL JOIN operations to combine data from multiple tables.

This approach is useful when we first want to prepare a temporary result set and then join that result with another table.

Example Tables

Employees Table

EmployeeId | EmployeeName | DepartmentId | Salary
-----------------------------------------------------
101        | Amit          | 1            | 60000
102        | Rahul         | 2            | 45000
103        | Priya         | 1            | 70000
104        | Sneha         | 3            | 55000

Departments Table

DepartmentId | DepartmentName
-----------------------------
1             | IT
2             | HR
3             | Finance

CTE with INNER JOIN

We can create a CTE containing employee information and then use an INNER JOIN with the Departments table.

WITH EmployeeData AS
(
    SELECT EmployeeId,
           EmployeeName,
           DepartmentId,
           Salary
    FROM Employees
)
SELECT
    EmployeeData.EmployeeId,
    EmployeeData.EmployeeName,
    Departments.DepartmentName,
    EmployeeData.Salary
FROM EmployeeData
INNER JOIN Departments
    ON EmployeeData.DepartmentId = Departments.DepartmentId;

Result

EmployeeId EmployeeName DepartmentName Salary
101 Amit IT 60000
102 Rahul HR 45000
103 Priya IT 70000
104 Sneha Finance 55000

CTE with WHERE and JOIN

We can filter records inside the CTE and then join the filtered result with another table.

WITH HighSalaryEmployees AS
(
    SELECT EmployeeId,
           EmployeeName,
           DepartmentId,
           Salary
    FROM Employees
    WHERE Salary > 50000
)
SELECT
    HighSalaryEmployees.EmployeeName,
    Departments.DepartmentName,
    HighSalaryEmployees.Salary
FROM HighSalaryEmployees
INNER JOIN Departments
    ON HighSalaryEmployees.DepartmentId =
       Departments.DepartmentId;

CTE with LEFT JOIN

A CTE can also be used with a LEFT JOIN.

WITH EmployeeData AS
(
    SELECT EmployeeId,
           EmployeeName,
           DepartmentId
    FROM Employees
)
SELECT
    EmployeeData.EmployeeName,
    Departments.DepartmentName
FROM EmployeeData
LEFT JOIN Departments
    ON EmployeeData.DepartmentId =
       Departments.DepartmentId;

CTE with Multiple Tables

A CTE can also be combined with multiple tables when more complex information is required.

WITH EmployeeData AS
(
    SELECT EmployeeId,
           EmployeeName,
           DepartmentId,
           Salary
    FROM Employees
)
SELECT
    EmployeeData.EmployeeName,
    Departments.DepartmentName,
    EmployeeData.Salary
FROM EmployeeData
INNER JOIN Departments
    ON EmployeeData.DepartmentId =
       Departments.DepartmentId
WHERE EmployeeData.Salary > 50000
ORDER BY EmployeeData.Salary DESC;

How CTE with JOIN Works

  1. First, the CTE creates a temporary result set.
  2. The CTE result is given a meaningful name.
  3. The main query joins the CTE with another table.
  4. The JOIN condition connects related records.
  5. The final result is returned by the main query.

Advantages

  • Makes complex JOIN queries easier to understand.
  • Separates data preparation from the main query.
  • Improves query readability.
  • Allows filtering before performing the JOIN.
  • Makes complex query logic easier to maintain.

Important Points

  • A CTE can be joined with one or more tables.
  • INNER JOIN, LEFT JOIN and other JOIN types can be used.
  • JOIN conditions should use related columns.
  • Filtering can be performed inside the CTE or in the main query depending on the requirement.

Summary

A CTE can be combined with JOIN operations to create readable and organized SQL queries. The CTE can prepare the required data first, and the main query can then join that result with other tables.