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

CROSS JOIN

CROSS JOIN returns every possible combination of rows from two tables.

Unlike INNER JOIN, LEFT JOIN, or RIGHT JOIN, CROSS JOIN does not require an ON condition. It creates a Cartesian Product of the two tables.

What is CROSS JOIN?

A CROSS JOIN combines every row from the first table with every row from the second table.

For example, if the first table contains 3 rows and the second table contains 4 rows, the result will contain:

3 × 4 = 12 rows

This type of result is called a Cartesian Product.

CROSS JOIN Syntax

SQL
SELECT columns
FROM Table1
CROSS JOIN Table2;
Important: CROSS JOIN does not normally use an ON condition.

Example Tables

Students Table

StudentId StudentName
101 Rahul
102 Priya
103 Amit

Courses Table

CourseId CourseName
1 .NET Full Stack
2 Java Full Stack
3 Python Full Stack

Basic CROSS JOIN Example

SQL
SELECT
    Students.StudentName,
    Courses.CourseName
FROM Students
CROSS JOIN Courses;

Result

Students contains 3 records and Courses contains 3 records.

Therefore:

3 × 3 = 9 combinations
StudentName CourseName
Rahul .NET Full Stack
Rahul Java Full Stack
Rahul Python Full Stack
Priya .NET Full Stack
Priya Java Full Stack
Priya Python Full Stack
Amit .NET Full Stack
Amit Java Full Stack
Amit Python Full Stack

Cartesian Product

The result produced by a CROSS JOIN is known as a Cartesian Product.

Every row of the first table is combined with every row of the second table.

SQL
Number of Result Rows
=
Rows in Table 1
×
Rows in Table 2

CROSS JOIN with Multiple Tables

CROSS JOIN can also be used with more than two tables.

SQL
SELECT
    S.StudentName,
    C.CourseName,
    T.TrainerName
FROM Students AS S
CROSS JOIN Courses AS C
CROSS JOIN Trainers AS T;

This produces combinations between all rows of the participating tables.

CROSS JOIN with WHERE

A WHERE condition can be used after a CROSS JOIN when specific combinations need to be selected.

SQL
SELECT
    S.StudentName,
    C.CourseName
FROM Students AS S
CROSS JOIN Courses AS C
WHERE C.CourseName = '.NET Full Stack';

This filters the combinations and returns only those involving the .NET Full Stack course.

Practical Uses of CROSS JOIN

CROSS JOIN is useful when every possible combination is required.

  • Creating combinations of products and sizes.
  • Generating combinations of courses and batches.
  • Creating possible schedules.
  • Generating test data combinations.
  • Creating all possible combinations between categories.

CROSS JOIN vs INNER JOIN

CROSS JOIN INNER JOIN
Returns every possible combination. Returns only matching records.
Does not require an ON condition. Usually uses an ON condition.
Produces a Cartesian Product. Produces related matching records.

Important Points

  • CROSS JOIN returns every possible combination between two tables.
  • CROSS JOIN does not require an ON condition.
  • The result is called a Cartesian Product.
  • Number of rows = rows in first table × rows in second table.
  • CROSS JOIN can produce a very large result when tables contain many rows.
  • CROSS JOIN should therefore be used carefully.

Summary

CROSS JOIN returns every possible combination of rows from two tables. It creates a Cartesian Product and does not require an ON condition. It is useful when all possible combinations between two sets of data are required.