LATEST 🚀 Welcome to Upgradix by Shrash Studio – Your destination for free technology learning and career growth.
🕘 Updated:
MS SQL Server

SQL Joins – INNER, LEFT, RIGHT, FULL and CROSS JOIN

SQL JOINs combine records from two or more related tables. SQL Server provides INNER, LEFT, RIGHT, FULL OUTER and CROSS JOINs. Each JOIN returns records based on different matching rules.

SQL Joins – INNER, LEFT, RIGHT, FULL and CROSS JOIN

Quick Answer: SQL JOINs combine records from two or more related tables. SQL Server provides INNER, LEFT, RIGHT, FULL OUTER and CROSS JOINs. Each JOIN returns records based on different matching rules.

1. Why Do We Need SQL Joins?

Imagine Lakshmi manages student records for a school with 1,000 students. She maintains student details separately from their club memberships.

Every student has an ID and a name. Some students join clubs, while others do not.

How can Lakshmi find students and their club details together?

The problem without SQL JOINs

We could retrieve each table separately. Lakshmi would then need to compare the records manually.

Example 1: Retrieve student and club records separately

Run this setup script in one SQL Server session.

CREATE TABLE #Students
(StudentId INT PRIMARY KEY, StudentName VARCHAR(30));

CREATE TABLE #Clubs
(StudentId INT, ClubName VARCHAR(30));

INSERT INTO #Students VALUES
(1,'Ravi'),(2,'Priya'),(3,'Arjun'),(4,'Meena');

INSERT INTO #Clubs VALUES
(1,'Chess'),(2,'Music'),(5,'Dance');

SELECT * FROM #Students;
SELECT * FROM #Clubs;

Expected output: Students

StudentId  StudentName
1          Ravi
2          Priya
3          Arjun
4          Meena

Expected output: Clubs

StudentId  ClubName
1          Chess
2          Music
5          Dance

Both queries return correct records. However, Lakshmi still needs to match them.

  1. Matching records manually takes extra work.
  2. Students without clubs need separate handling.
  3. Club records without matching students can be missed.
  4. Reporting becomes harder when tables contain many records.

Now imagine Lakshmi managing thousands of students and hundreds of clubs.

We need a way to combine related records directly inside SQL Server.

The solution: SQL JOINs

JOIN = a SQL operation that combines records from related tables.

Join condition = the rule SQL Server uses to match records.

In Lakshmi's school, StudentId connects student records with club memberships.

Each JOIN type decides which matching and non-matching records appear.

Animation 1: How SQL JOINs combine school records

Watch Lakshmi connect two tables and explore five different JOIN results.

Lakshmi School teacher · manages club records
checks school records
Students 1 Ravi · 2 Priya
3 Arjun · 4 Meena
Clubs 1 Chess · 2 Music
5 Dance
matches StudentId
JOIN Condition Students.StudentId = Clubs.StudentId
chooses the JOIN rule
INNER JOIN Ravi · Priya
LEFT JOIN All four students
RIGHT JOIN All three club records
FULL OUTER JOIN All records from both tables
CROSS JOIN Every student with every club
Source tables Matching process JOIN results Current animation stage

Lakshmi matches StudentId and chooses a JOIN type. Each type produces a different result.

2. Implement SQL JOINs in SQL Server

Let's use Lakshmi's two tables throughout these examples.

We will use #Students and #Clubs created earlier.

1. INNER JOIN

INNER JOIN = returns only records that match in both tables.

Ravi and Priya have club memberships. Arjun and Meena do not.

Example 2: Retrieve students with matching club memberships

Run this query after creating the two temporary tables.

SELECT
    S.StudentName,
    C.ClubName
FROM #Students S
INNER JOIN #Clubs C
    ON S.StudentId = C.StudentId;

Expected output

StudentName  ClubName
Ravi         Chess
Priya        Music

Notice that only matching StudentId values appear. Other records are excluded.

2. LEFT JOIN

LEFT JOIN = returns every left-table record and matching right-table records.

If no club exists, SQL Server returns NULL for club details.

SELECT S.StudentName, C.ClubName
FROM #Students S
LEFT JOIN #Clubs C
    ON S.StudentId = C.StudentId;

Expected output

StudentName  ClubName
Ravi         Chess
Priya        Music
Arjun        NULL
Meena        NULL

All four students appear, including those without club memberships.

3. RIGHT JOIN

RIGHT JOIN = returns every right-table record and matching left-table records.

Here, the Dance membership has no matching student record.

SELECT S.StudentName, C.ClubName
FROM #Students S
RIGHT JOIN #Clubs C
    ON S.StudentId = C.StudentId;

Expected output

StudentName  ClubName
Ravi         Chess
Priya        Music
NULL         Dance

All three club records appear, even without matching students.

4. FULL OUTER JOIN

FULL OUTER JOIN = returns all records from both tables.

Matching records combine. Non-matching records contain NULL values.

SELECT S.StudentName, C.ClubName
FROM #Students S
FULL OUTER JOIN #Clubs C
    ON S.StudentId = C.StudentId;

Expected output

StudentName  ClubName
Ravi         Chess
Priya        Music
Arjun        NULL
Meena        NULL
NULL         Dance

Notice that no student or club record is excluded from this result.

5. CROSS JOIN

CROSS JOIN = combines every left-table record with every right-table record.

It does not need an ON condition. Four students and three clubs produce twelve combinations.

SELECT S.StudentName, C.ClubName
FROM #Students S
CROSS JOIN #Clubs C;

Expected output: First and last combinations

StudentName  ClubName
Ravi         Chess
Ravi         Music
Ravi         Dance
Priya        Chess
...          ...
Meena        Chess
Meena        Music
Meena        Dance

Total rows: 12

Each student appears with every club. This is called a Cartesian product.

Important: CROSS JOIN can produce very large results. Four students and three clubs produce twelve rows. Always consider the resulting row count.

3. Quick Summary

INNER, LEFT, RIGHT, FULL and CROSS JOIN

Comparison of SQL Server JOIN types
JOIN Type What it returns Unmatched records
INNER JOIN Matching records from both tables. Excluded
LEFT JOIN All left records and matching right records. Left records retained
RIGHT JOIN All right records and matching left records. Right records retained
FULL OUTER JOIN All records from both tables. Both sides retained
CROSS JOIN Every possible combination. Not applicable

The important terms to remember

  • JOIN: Combines records from multiple tables.
  • ON: Defines the matching condition.
  • NULL: Represents a missing or unknown value.
  • Cartesian product: Every combination of two sets of records.
  • Table alias: A short name used to reference a table.

4. Common Mistakes and Best Practices

1. Forgetting the JOIN condition

Missing conditions can create incorrect combinations or unexpected results.

Better approach: Check the matching columns before writing the JOIN.

2. Filtering a LEFT JOIN incorrectly

Putting a right-table condition in WHERE can remove NULL records. This may make a LEFT JOIN behave like an INNER JOIN for that filter.

Better approach: Place optional right-table filters in the ON condition when unmatched left records must remain.

3. Using RIGHT JOIN unnecessarily

RIGHT JOIN can make table relationships harder to read in some queries.

Better approach: Consider reversing the table order and using LEFT JOIN.

Design tip: Use INNER JOIN when matching records are needed. Use LEFT JOIN when every left-side record must remain.

5. How SQL JOINs Come Up in Interviews

Question 1: What is the difference between INNER JOIN and LEFT JOIN?

What the interviewer expects: Explain how each JOIN handles unmatched records.

Strong answer: "INNER JOIN returns only matching student and club records. LEFT JOIN returns every student, even when no club exists."

Question 2: What is FULL OUTER JOIN?

What the interviewer expects: Explain how both tables contribute records.

Strong answer: "FULL OUTER JOIN returns matching and non-matching records from both tables. Missing values appear as NULL."

Question 3: What is CROSS JOIN?

What the interviewer expects: Explain the Cartesian product.

Strong answer: "CROSS JOIN combines every student with every club. Four students and three clubs produce twelve combinations."

What interviewers expect at different levels

  • Junior developer: Write basic INNER and LEFT JOIN queries.
  • Senior developer: Explain NULL handling and complex JOIN conditions.
  • Technical architect: Discuss query performance, relationships and data modelling.

6. Hands-On Practice

Beginner: Hospital appointments

Create Patients and Appointments tables. Find patients who have booked appointments.

Expected result: Show patient names and appointment details using INNER JOIN.

Intermediate: Bank accounts

Create Customers and BankAccounts tables. Find customers without bank accounts.

Hint: Use LEFT JOIN and filter unmatched account records using IS NULL.

Advanced: E-commerce orders

Create Customers, Orders and Payments tables.

  • Find customers with and without orders.
  • Find orders without payment records.
  • Compare INNER JOIN and FULL OUTER JOIN results.

Challenge: Create a report showing customers, orders and payment status.

7. Next Steps and Community

Previous article: SQL Operators – Comparison, Logical, BETWEEN, IN and LIKE

Next article: Aggregate Functions, GROUP BY and HAVING in SQL Server

For more details, read the official Microsoft documentation:

Try Lakshmi's school example with additional students and clubs. Share your queries and discuss different JOIN results with other learners.

Comments & Ratings (0)

Log in or create a student account to comment and rate this article.

Be the first to comment on this article.