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.
Students.CourseId = Courses.CourseId
Basic JOIN Syntax
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.
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.