Learn SQL outer join and its type with example queries.

 Features of SQL OUTER JOIN

Note: No Outer join notation exists in standard SQL. This means we need to use LEFT, RIGHT or FULL. Only writing OUTER JOIN in SQL query won’t work.

What does LEFT and RIGHT refer to in SQL joins?

The Left and Right in joins refer to the two sides of the join keywords in the SQL query.

LEFT OUTER JOIN / LEFT JOIN in SQL

For a given Table A and  Table B.

  • The result of LEFT OUTER JOIN or LEFT JOIN  will have all the records of ‘left: Table A’, even if no matching record is found in ‘right: Table B’.
  • Therefore, this means that if ON clause matches no records/ zero records in table B. The join will still return a row as a result and will contain NULL in each column from Table B.
  • In other words, a LEFT OUTER JOIN  returns all the values from an inner join plus all the values from the left A table whether it matches with the right table B or not.

Example: Display the selected columns from customer and payment tables, such that all the row from the customer table are displayed whether there is a match in payment or not.


SELECT c.name, p.checknumber, p.amount
FROM customer c LEFT OUTER JOIN payment p
ON c.customernumber=p.customernumber

RIGHT OUTER JOIN / RIGHT JOIN in SQL

  • RIGHT OUTER JOIN or RIGHT JOIN is almost the same as that of LEFT OUTER JOIN. Except, with the treatment of table reversed.
  • All the rows from the ‘right: Table B’ will be in the result in the joined tables at least once.
  • In case, no matching rows from left table A exists, NULL will appear in the column from A for records that have no match in table B.
  • Likewise, a RIGHT OUTER JOIN will return all the values from the right table and matched values from the left table.

Example: Display the selected columns from customer and payment tables, such that all the row from the payment table are displayed whether there is a match in customer or not.


SELECT c.name, p.checknumber, p.amount
FROM customer c RIGHT OUTER JOIN payment p
ON c.customernumber=p.customernumber

FULL OUTER JOIN in SQL

  • It is the combination of both LEFT  and RIGHT OUTER JOIN.
  • The result set will have NULL values for every column of the table that does not have a matching row.
  • If the record/values matches, a single row is returned in the result containing fields from both the tables.

Supported DB: Microsoft SQL Server, Oracle 10g, 11g, DB2
Unsupported in: Sybase, MySQL

Example: Left and Right join for both the tables customer and payment outer join.


SELECT c.name, p.checknumber, p.amount
FROM customer c FULL OUTER JOIN payment p
ON c.customernumber=p.customernumber

If you need help with SQL Database homework or Assignments, Feel free to drop us a text.
In conclusion, We hope this tutorial helped you understand – Outer Join. Keep learning keep sharing. Follow us on Facebook and Instagram.