What's the difference between LEFT JOIN and LEFT OUTER JOIN?

Key takeaways:

  • LEFT JOIN, and LEFT OUTER JOIN gives the same results, returning all records from the left table and matched records from the right table.

  • The only difference is the optional OUTER keyword; both use similar syntax.

  • Both joins have identical performance characteristics; no difference in execution time.

Before writing an SQL query, it becomes important for a developer or student to understand the differences between different types of joins. The use of LEFT JOIN and LEFT OUTER JOIN can be confusing, especially when comparing inner vs. outer joins. For example, this confusion can keep valuable data out of reach and make it difficult to perform tasks as effectively as you would like. In this Answer, we will understand the difference between LEFT JOIN and LEFT OUTER JOIN.

LEFT JOIN vs. LEFT OUTER JOIN

In SQL, the LEFT JOIN and LEFT OUTER JOIN return all the records from the left table (Table 1) and those 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 and NULL values for the right table. The term “OUTE’’ is optional; it specifies that you are performing a type of outer join. Thus, LEFT JOIN is a shorthand for LEFT OUTER JOIN; it specifies that you perform a type of outer join.

The syntax for the LEFT JOIN SQL

SELECT column_name

FROM table1

LEFT JOIN table2 ON table1.column_name=table2.column_name;
The syntax for LEFT JOIN

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 Student 
  LEFT JOIN Score ON
  Student.ID = Score.StudentID;

Explanation

  • Lines 2–7: We create a table, Student.

  • Lines 10–17: We insert a few values in the Student table.

  • Lines 20–25: We create a table, Score.

  • Lines 28–35: We insert a few values in the table, Score.

  • Lines 38–40: We execute the LEFT JOIN.

The syntax for the LEFT OUTER JOIN SQL

SELECT column_name

FROM table1

LEFT OUTER JOIN table2 ON table1.column_name=table2.column_name;
The syntax for LEFT OUTER JOIN

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, MySQL uses LEFT JOIN, whereas the SQL server uses LEFT OUTER JOIN.

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 Student 
  LEFT OUTER JOIN Score ON
  Student.ID = Score.StudentID;

Explanation

  • Lines 2–7: We create a table, Student.

  • Lines 10–17: We insert a few values in the Student table.

  • Lines 20–25: We create a table, Score.

  • Lines 28–35: We insert a few values in the Score table.

  • Lines 38–40: We execute the LEFT OUTER JOIN.

LEFT JOIN vs. LEFT OUTER JOIN

Key Differences

LEFT JOIN

LEFT OUTER JOIN

Matching records

Includes matching records from the right table.

Same as LEFT JOIN.

Syntax

SELECT columns FROM left_table LEFT JOIN right_table ON condition;

SELECT columns FROM left_table LEFT OUTER JOIN right_table ON condition;

Good to use

Ideal when clarity in the type of join is not needed.

Ideal when clarity in the type of join is needed.

Performance

No difference in execution time.

No difference in execution time.

Wrapping up

When we compare the output of LEFT JOIN and LEFT OUTER JOIN, we can see that both return the same result and have no difference, except the terminology.

Frequently asked questions

Haven’t found what you were looking for? Contact Us

How do we join three or more tables in SQL?

We can join three or more tables in SQL using the JOIN keyword. Check out our detailed Answer on How to join 3 or more tables in SQL.

Which is faster LEFT JOIN or LEFT OUTER JOIN?

LEFT JOIN and LEFT OUTER JOIN both have same execution time.

What’s the difference between JOIN and INNER JOIN?

The JOIN syntax retrieves the same columns from two or more database tables or databases while INNER JOIN is functionally the same as JOIN; both produce the same results. Check out our detailed Answer on What’s the difference between JOIN and INNER JOIN?

What is FULL OUTER JOIN in PostgreSQL?

In PostgreSQL, FULL OUTER JOIN joins two tables’ values with a common attribute. The non-matching records are joined with the NULL value for both tables.

What is INNER JOIN in SQL?

The INNER JOIN keyword is used to join two tables in our database. The query returns records that have matching values in both tables.

What is LEFT() in SQL?

The LEFT() function returns a specific number of characters from the left side of a string.

Free Resources

License: Creative Commons-Attribution-ShareAlike 4.0 (CC-BY-SA 4.0)