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

Joins Introduction

SQL Server JOINs are used to combine related data from two or more tables based on a related column.

In real-world databases, data is usually divided into multiple tables. JOINs allow us to retrieve meaningful information by combining data from those tables.

What is a JOIN?

A JOIN is an SQL operation used to combine rows from two or more tables based on a related column between those tables.

For example, suppose we have a Students table and a Courses table. Both tables may have a common column such as CourseId.

We can use a JOIN to display the student name together with the course name.

Why Do We Use JOINs?

JOINs are important because related information is often stored in different tables instead of keeping everything in a single table.

  • To combine data from multiple tables.
  • To retrieve related information.
  • To avoid unnecessary duplicate data.
  • To work with normalized database structures.
  • To create meaningful reports and results.

Example Tables

Consider the following two tables.

Students Table

StudentId StudentName CourseId
101 Rahul 1
102 Priya 2
103 Amit 1
104 Neha 3

Courses Table

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

Related Column

In our example, the common column is CourseId.

The Students table stores CourseId to identify the course selected by the student, while the Courses table contains the actual course information.

SQL
Students.CourseId = Courses.CourseId

Basic JOIN Syntax

SQL
SELECT columns
FROM Table1
JOIN Table2
ON Table1.CommonColumn = Table2.CommonColumn;

The ON condition specifies how the records in the two tables are related.

Simple JOIN Example

To display student names along with their course names, we can join the Students and Courses tables.

SQL
SELECT
    Students.StudentName,
    Courses.CourseName
FROM Students
JOIN Courses
    ON Students.CourseId = Courses.CourseId;

Result

StudentName CourseName
Rahul .NET Full Stack
Priya Java Full Stack
Amit .NET Full Stack
Neha Python Full Stack

Types of JOINs

SQL Server provides different types of JOINs for retrieving related data in different ways.

JOIN Type Purpose
INNER JOIN Returns matching records from both tables.
LEFT JOIN Returns all records from the left table and matching records from the right table.
RIGHT JOIN Returns all records from the right table and matching records from the left table.
FULL OUTER JOIN Returns matching and non-matching records from both tables.
CROSS JOIN Returns every possible combination of rows from both tables.

Important

  • JOINs are mainly used to combine related data.
  • The ON condition defines the relationship between tables.
  • Tables commonly have a primary key and foreign key relationship.
  • Different JOIN types produce different result sets.

Key Points

  • JOIN combines data from two or more tables.
  • A common column is generally used to relate tables.
  • The ON clause defines the JOIN condition.
  • INNER, LEFT, RIGHT, FULL OUTER and CROSS JOIN are covered in this module.
  • The correct JOIN should be selected according to the required result.

Summary

In this section, we learned what SQL JOINs are, why JOINs are used, how tables are related using common columns, and the basic JOIN syntax. We also introduced the major JOIN types that will be covered in the upcoming sections.