SQL Outer Joins With Example

An outer join is a type of join in SQL that combines rows from two or more tables based on a specified condition, including all matching and non-matching rows. There are three types of outer joins: left outer join, right outer join, and full outer join.

Here's the syntax for each type of outer join:

Left outer join:

SELECT column1, column2, ...

FROM table1

LEFT OUTER JOIN table2

ON table1.column = table2.column;

This will return all the rows from table1 and the matching rows from table2. If there are no matching rows in table2, the result will contain NULL values for the columns of table2.

Example of Left Outer Join

SELECT orders.order_id, customers.customer_name

FROM orders

LEFT OUTER JOIN customers

ON orders.customer_id = customers.customer_id;

This will return all the rows from the orders table and the matching rows from the customers table based on the customer_id column. If there are no matching rows in the customers table, the result will contain NULL values for the customer_name column.

Right outer join:

SELECT column1, column2, ...

FROM table1

RIGHT OUTER JOIN table2

ON table1.column = table2.column;

This will return all the rows from table2 and the matching rows from table1. If there are no matching rows in table1, the result will contain NULL values for the columns of table1.

Full outer join:

SELECT column1, column2, ...

FROM table1

FULL OUTER JOIN table2

ON table1.column = table2.column;

This will return all the rows from both table1 and table2, including the matching rows and the non-matching rows. If there are no matching rows in either table, the result will contain NULL values for the columns of the corresponding table.