#1 India's Top IT Training Institute
×

About Uncodemy

Know more about who we are and what we stand for.

About Us

About Us

Discover our mission to equip individuals with essential IT skills and industry expertise.

Read More →
UnCodeMy Gallery

UnCodeMy Gallery

Explore highlights from our IT training sessions and success stories.

Read More →
Mentors

Mentors

Meet the industry experts who train and guide you through your course.

Read More →
Free Resources

Free Resources

Free tutorials, interview questions, tools and calculators to help you learn.

Explore →
×

Free Resources

Learn, practice and plan your career — bilkul free.

Tutorials

Tutorials

Step-by-step written tutorials to learn any technology at your own pace.

Read More →
Free Counselling

Free Counselling

Book a free video counselling session and pick the right course for you.

Read More →
Online Compiler

Online Compiler

Write, run and test your code online — no setup required.

Read More →
MySQL Tutorial · Data Analytics

MySQL JOINs Explained: INNER, LEFT, RIGHT and FULL

A join combines rows from two tables using a column they share. The join type decides one thing only: what happens to rows that have no partner in the other table. INNER JOIN drops them, LEFT JOIN keeps the unmatched rows of the left table, RIGHT JOIN keeps those of the right table, and a full outer join keeps both. MySQL supports the first three directly; the fourth needs a small workaround, shown below.

Two small tables with deliberate gaps

CREATE TABLE students (student_id INT PRIMARY KEY, name VARCHAR(20), city VARCHAR(15));
CREATE TABLE enrolments (enrol_id INT PRIMARY KEY, student_id INT, course VARCHAR(20));

INSERT INTO students VALUES
 (1,'Aman','Patna'), (2,'Bhavna','Ranchi'), (3,'Chirag','Surat'), (4,'Divya','Mysuru');
INSERT INTO enrolments VALUES
 (101,1,'SQL'), (102,1,'Power BI'), (103,2,'Python'), (104,3,'SQL'), (105,7,'Tableau');

Divya has not enrolled in anything. Enrolment 105 belongs to student 7, who is not in the students table (perhaps the record was deleted, and there is no foreign key to stop it). These two gaps are what make the join types behave differently.

INNER JOIN: only matching pairs

SELECT s.name, e.course
FROM students s
INNER JOIN enrolments e ON e.student_id = s.student_id
ORDER BY e.enrol_id;
namecourse
AmanSQL
AmanPower BI
BhavnaPython
ChiragSQL

Four rows. Divya is missing (no enrolment) and so is the Tableau enrolment (no student). Aman appears twice because he has two matching enrolments: a join returns one row per matching pair, not one per student. JOIN on its own means INNER JOIN.

LEFT JOIN: every row from the left table

SELECT s.name, e.course
FROM students s
LEFT JOIN enrolments e ON e.student_id = s.student_id
ORDER BY s.student_id, e.enrol_id;
namecourse
AmanSQL
AmanPower BI
BhavnaPython
ChiragSQL
DivyaNULL

Divya is back, with NULL in the columns that would have come from enrolments. That NULL makes a very useful pattern possible, the anti-join: "students who have not enrolled".

SELECT s.name
FROM students s
LEFT JOIN enrolments e ON e.student_id = s.student_id
WHERE e.enrol_id IS NULL;
name
Divya

Test the IS NULL on a column that can never be NULL in a real match, such as the right table's primary key.

RIGHT JOIN: every row from the right table

SELECT e.enrol_id, e.student_id, s.name, e.course
FROM students s
RIGHT JOIN enrolments e ON e.student_id = s.student_id
ORDER BY e.enrol_id;
enrol_idstudent_idnamecourse
1011AmanSQL
1021AmanPower BI
1032BhavnaPython
1043ChiragSQL
1057NULLTableau

Now the orphan enrolment 105 is kept and Divya is not. A right join is just a left join with the tables swapped: FROM enrolments e LEFT JOIN students s ... returns the same rows. Most teams write only left joins because a query reads more naturally when the "main" table comes first. A side-by-side comparison is in LEFT JOIN vs RIGHT JOIN.

FULL OUTER JOIN: not supported, so emulate it

Writing FULL OUTER JOIN in MySQL gives ERROR 1064 (syntax error); MySQL 8 still does not implement it. Combine a left join with the unmatched rows of a right join:

SELECT s.name, e.course
FROM students s
LEFT JOIN enrolments e ON e.student_id = s.student_id
UNION ALL
SELECT s.name, e.course
FROM students s
RIGHT JOIN enrolments e ON e.student_id = s.student_id
WHERE s.student_id IS NULL;
namecourse
AmanPower BI
AmanSQL
BhavnaPython
ChiragSQL
DivyaNULL
NULLTableau

Both gaps appear: Divya with no course and Tableau with no student. (Add an ORDER BY if you need a fixed row order.) You will often see this written as LEFT JOIN ... UNION ... RIGHT JOIN without the WHERE. That also works on this data, but plain UNION removes duplicates, so it would wrongly collapse two genuinely identical rows. The UNION ALL + IS NULL version is safe. The difference is explained in UNION vs UNION ALL.

The mistake that turns a LEFT JOIN into an INNER JOIN

Goal: list every student and show their SQL enrolment if they have one. This looks right but is not:

SELECT s.name, e.course
FROM students s
LEFT JOIN enrolments e ON e.student_id = s.student_id
WHERE e.course = 'SQL';
namecourse
AmanSQL
ChiragSQL

The WHERE runs after the join and throws away every row where e.course is NULL, so Bhavna and Divya vanish. Move the condition on the right-hand table into the ON clause:

SELECT s.name, e.course
FROM students s
LEFT JOIN enrolments e
       ON e.student_id = s.student_id
      AND e.course = 'SQL'
ORDER BY s.student_id;
namecourse
AmanSQL
BhavnaNULL
ChiragSQL
DivyaNULL

Rule of thumb: in a left join, conditions on the right table go in ON; conditions on the left table go in WHERE.

Quick comparison

JoinRows returned on this dataUnmatched left rows (Divya)Unmatched right rows (enrolment 105)
INNER JOIN4DroppedDropped
LEFT JOIN5Kept, right side NULLDropped
RIGHT JOIN5DroppedKept, left side NULL
FULL (emulated)6KeptKept

Other mistakes to watch for

Next: join a table to itself in self JOIN in MySQL, and compare joins with subqueries in JOIN vs subquery performance.

Become a Job-Ready Data Analyst

Master MySQL, Advanced Excel, Power BI, Tableau and Python with Uncodemy's Data Analytics Training in Noida — live projects, expert mentors and Dedicated Placement Support.

Enroll in Data Analytics Course →