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

INNER JOIN

INNER JOIN is used to return only the records that have matching values in both tables.

It is one of the most commonly used JOINs in SQL Server. When two tables are related through a common column, INNER JOIN can be used to retrieve matching information from both tables.

What is INNER JOIN?

An INNER JOIN returns rows when there is a matching value in both the left table and the right table.

If a record exists in one table but there is no matching record in the other table, that record is not included in the result.

INNER JOIN Syntax

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

Example Tables

Students Table

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

Courses Table

CourseId CourseName
1 .NET Full Stack
2 Java Full Stack
3 Python Full Stack
4 Software Testing

Common Column

In this example, CourseId is the common column between the Students and Courses tables.

SQL
Students.CourseId = Courses.CourseId

Basic INNER JOIN Example

To display student names along with their course names, we can use the following query:

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

Result

INNER JOIN returns only those students whose CourseId exists in the Courses table.

StudentName CourseName
Rahul .NET Full Stack
Priya Java Full Stack
Amit .NET Full Stack
Neha Python Full Stack
Notice: Rohan has CourseId 5, but CourseId 5 does not exist in the Courses table. Therefore, Rohan is not included in the INNER JOIN result.

INNER JOIN with WHERE

INNER JOIN can be combined with a WHERE clause to filter the matching records.

SQL
SELECT
    Students.StudentName,
    Courses.CourseName
FROM Students
INNER JOIN Courses
    ON Students.CourseId = Courses.CourseId
WHERE Courses.CourseName = '.NET Full Stack';

This returns only students enrolled in the .NET Full Stack course.

INNER JOIN using Table Aliases

Table aliases can make JOIN queries shorter and easier to read.

SQL
SELECT
    S.StudentName,
    C.CourseName
FROM Students AS S
INNER JOIN Courses AS C
    ON S.CourseId = C.CourseId;

Here, S represents Students and C represents Courses.

Selecting Specific Columns

We do not always need to select every column from both tables. We can select only the required columns.

SQL
SELECT
    S.StudentId,
    S.StudentName,
    C.CourseName
FROM Students AS S
INNER JOIN Courses AS C
    ON S.CourseId = C.CourseId;

INNER JOIN with Multiple Tables

INNER JOIN can also be used to combine more than two tables.

SQL
SELECT
    S.StudentName,
    C.CourseName,
    T.TrainerName
FROM Students AS S
INNER JOIN Courses AS C
    ON S.CourseId = C.CourseId
INNER JOIN Trainers AS T
    ON C.TrainerId = T.TrainerId;

This example demonstrates how multiple related tables can be combined using INNER JOIN.

INNER JOIN vs JOIN

In SQL Server, JOIN without a specified JOIN type is treated as an INNER JOIN.

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

The above query produces the same type of result as an INNER JOIN.

Important Points

  • INNER JOIN returns matching records from both tables.
  • Non-matching records are excluded.
  • The ON clause defines the relationship between the tables.
  • INNER JOIN can be combined with WHERE, ORDER BY and other SQL clauses.
  • Table aliases make complex JOIN queries easier to read.
  • JOIN without a specified type means INNER JOIN.

Summary

INNER JOIN combines two or more related tables and returns only the rows that have matching values in both tables. The ON clause specifies the relationship between the tables. INNER JOIN is commonly used to retrieve related information from normalized database tables.