Techie BabajiLearn to Code. Build Real Skills.
SQL Server · Combining Data

Joins

Relational data is split across tables. Joins put it back together. INNER JOIN and LEFT JOIN cover the vast majority of real queries.

Beginner 5 min Lesson 13 of 31Examples run on SQL Server 2022 and Azure SQL Database.
Before you start:SELECTPrimary and Foreign Keys
0/31 in SQL Server
On this page · 5 min · Beginner

On this page

  1. In simple words
  2. Learning objectives
  3. Explanation
  4. Visual explanation
  5. Syntax
  6. Code example
  7. Step by step
  8. Real-world use
  9. Common mistakes
  10. Best practices
  11. Remember this
  12. Practice exercise
  13. Mini quiz
  14. Related topics
  15. My notes
Reading time
5 min
Difficulty
Beginner
Updated
Oct 1, 2026

Related tutorials

Interview questions

In simple words#

Students are in one register and their fee payments in another. A JOIN lays the two side by side using the roll number so you can see each student with their payments.

INNER JOIN
Only rows that match in both tables.
LEFT JOIN
All rows from the left, matches where available.
ON
The matching rule (Students.Id = Payments.StudentId).

Learning objectives#

  • Write INNER JOIN and LEFT JOIN queries
  • Explain which rows each join returns
  • Use table aliases
  • Find rows with no match

Explanation#

INNER JOIN returns only rows that have a match in both tables.

LEFT JOIN returns every row from the left table. Where there is no match on the right, the right-hand columns are NULL.

RIGHT JOIN is the mirror image of LEFT JOIN, and FULL JOIN returns all rows from both sides. In practice, most developers rewrite RIGHT JOIN as a LEFT JOIN for readability.

Visual explanation#

Venn diagrams of INNER, LEFT, RIGHT and FULL joinsABINNER JOINMatches onlyABLEFT JOINAll of A + matchesABRIGHT JOINAll of B + matchesABFULL JOINEverything
Which rows each join returns. Shaded areas are included in the result.

Syntax#

SQL Illustrative
SELECT columns
FROM TableA AS a
INNER JOIN TableB AS b ON a.Key = b.Key;

Code example: Students and enrolments#

SQLStudents and enrolments Illustrative
CREATE TABLE Students (StudentId INT PRIMARY KEY, Name NVARCHAR(50));
CREATE TABLE Enrollments (
  EnrollmentId INT PRIMARY KEY,
  StudentId INT REFERENCES Students(StudentId),
  Course NVARCHAR(50));


INSERT INTO Students VALUES (1, N'Asha'), (2, N'Ravi'), (3, N'Meera');
INSERT INTO Enrollments VALUES (10, 1, N'React'), (11, 1, N'SQL'), (12, 2, N'Angular');


-- Students with at least one enrolment
SELECT s.Name, e.Course
FROM Students AS s
INNER JOIN Enrollments AS e ON e.StudentId = s.StudentId
ORDER BY s.Name;


-- All students, including those with no enrolment
SELECT s.Name, e.Course
FROM Students AS s
LEFT JOIN Enrollments AS e ON e.StudentId = s.StudentId
ORDER BY s.Name;
Expected output
-- INNER JOIN
Name   Course
Asha   React
Asha   SQL
Ravi   Angular

-- LEFT JOIN
Name   Course
Asha   React
Asha   SQL
Meera  NULL
Ravi   Angular

This example is illustrative. Copy or download it and run it in your own environment; the expected output is shown above.

Step-by-step explanation#

  1. Two tables are related through StudentId, a foreign key in Enrollments.
  2. INNER JOIN keeps only students with a matching enrolment, so Meera is excluded.
  3. LEFT JOIN keeps every student; Meera appears with NULL for Course.
  4. Aliases (s, e) keep the query short and make column origins explicit.

Real-world use case#

Orders with customer names, products with categories, employees with departments — nearly every report or API listing uses a join.

Common mistakes#

Filtering a LEFT JOIN in WHERE

WHERE e.Course = 'React' removes the NULL rows and turns the LEFT JOIN into an INNER JOIN. Put the condition in the ON clause instead.

Missing join condition

Forgetting ON (or joining on the wrong column) multiplies rows unexpectedly.

Best practices#

  • Always alias tables in multi-table queries
  • Index foreign-key columns used in joins
  • Select only the columns you need
  • Prefer LEFT JOIN over RIGHT JOIN for readability

Remember this#

INNER JOIN = only matching rows.

LEFT JOIN = all left rows + matches (NULL if none).

Join ON keys: Orders.CustomerId = Customers.Id.

Practice exercise#

Your task: List students who are NOT enrolled in any course.

Show solution
SQL Illustrative
SELECT s.Name
FROM Students AS s
LEFT JOIN Enrollments AS e ON e.StudentId = s.StudentId
WHERE e.EnrollmentId IS NULL;   -- or: WHERE NOT EXISTS (…)

Mini quiz#

Joins quizMultiple choice Question 1 of 2

Which join returns all rows from the left table even without a match?

Score so far: 0/0

Want more? Practise all SQL Server MCQs · SQL Server interview questions

My notes#

Saved in this browser.

Progress is saved in this browser. Sign in to sync.