What's the difference between LEFT JOIN and LEFT OUTER JOIN?
Overview
In SQL, the LEFT JOIN or LEFT OUTER JOIN returns all the records from the left table (table1) and the records that are matched from the right table (table 2). If none of the records are matched from the right table, only the records from the left table are returned.
The syntax for LEFT JOIN
SELECT column_nameFROM table1LEFT JOIN table2 ON table1.column_name=table2.column_name;
The syntax for LEFT OUTER JOIN
SELECT column_nameFROM table1LEFT OUTER JOIN table2 ON table1.column_name=table2.column_name;
Basically, `LEFT JOIN` and `LEFT OUTER JOIN` are equivalent and have no difference— both return the same result. It's just a difference of terminologies. For example, MY SQL uses `LEFT JOIN` whereas the SQL server uses `LEFT OUTER JOIN`.
Example 1
Let's see an example of LEFT JOIN in the code snippet below:
/* Create table Student */CREATE TABLE Student(ID INT NOT NULL,NAME VARCHAR (20) NOT NULL,AGE INT NOT NULL,PRIMARY KEY (ID));/* Insert values in table Student */INSERT INTO Student (ID, NAME, AGE)VALUES (1, 'Shubham', 22);INSERT INTO Student (ID, NAME, AGE)VALUES (2, 'Parth', 21);INSERT INTO Student (ID, NAME, AGE)VALUES (3, 'Pratik', 23);/* Create table Score */CREATE TABLE Score(ID INT NOT NULL,SCORE INT NOT NULL,StudentID INT NOT NULL,PRIMARY KEY (ID));/* Insert values in table Score */INSERT INTO Score (ID, SCORE, StudentID)VALUES (1, 90, 1);INSERT INTO Score (ID, SCORE, StudentID)VALUES (2, 96, 2);INSERT INTO Score (ID, SCORE, StudentID)VALUES (3, 98, 3);/* Execute LEFT JOIN */SELECT * FROM StudentLEFT JOIN Score ONStudent.ID = Score.StudentID;
Explanation
- Line 2-7: We create a table,
Student. - Line 10-17: We insert a few values in the
Studenttable. - Line 20-25: We create a table,
Score. - Line 28-35: We insert a few values in the table,
Score.
- Line 38-40: We execute the
LEFT JOIN.
Example 2
Let's see an example of LEFT OUTER JOIN in the code snippet below:
/* Create table Student */CREATE TABLE Student(ID INT NOT NULL,NAME VARCHAR (20) NOT NULL,AGE INT NOT NULL,PRIMARY KEY (ID));/* Insert values in table Student */INSERT INTO Student (ID, NAME, AGE)VALUES (1, 'Shubham', 22);INSERT INTO Student (ID, NAME, AGE)VALUES (2, 'Parth', 21);INSERT INTO Student (ID, NAME, AGE)VALUES (3, 'Pratik', 23);/* Create table Score */CREATE TABLE Score(ID INT NOT NULL,SCORE INT NOT NULL,StudentID INT NOT NULL,PRIMARY KEY (ID));/* Insert values in table Score */INSERT INTO Score (ID, SCORE, StudentID)VALUES (1, 90, 1);INSERT INTO Score (ID, SCORE, StudentID)VALUES (2, 96, 2);INSERT INTO Score (ID, SCORE, StudentID)VALUES (3, 98, 3);/* Execute LEFT OUTER JOIN */SELECT * FROM StudentLEFT OUTER JOIN Score ONStudent.ID = Score.StudentID;
Explanation
- Line 2-7: We create a table,
Student. - Line 10-17: We insert a few values in the
Studenttable. - Line 20-25: We create a table,
Score. - Line 28-35: We insert a few values in the
Scoretable.
- Line 38-40: We execute the
LEFT OUTER JOIN.
Output
When we compare the output of `LEFT JOIN` and `LEFT OUTER JOIN`, we can see that both of them return the same result and have no difference, except the terminology.
