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.
- Matching records manually takes extra work.
- Students without clubs need separate handling.
- Club records without matching students can be missed.
- 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.
3 Arjun · 4 Meena
5 Dance
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
| 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.