SQL OUTER JOIN - Left Join, Right Join and Full Outer Join

Summary: in this tutorial, you will learn how to use SQL outer join including left outer join, right outer join and full outer join.

If you want to learn about SQL INNER JOIN, check it out the SQL INNER JOIN tutorial.

There are three kinds of OUTER JOIN: left outer join, right outer join and full outer join. Let’s examine each kind of join in more detail.

SQL OUTER JOIN – left outer join

SQL left outer join is also known as SQL left join. Suppose, we want to join two tables: A and B. SQL left outer join returns all rows in the left table (A) and all the matching rows found in the right table (B). It means the result of the SQL left join always contains the rows in the left table.

The following illustrate SQL left outer syntax of joining 2 tables: table_A and table_B:

SELECT column1, column2... 
FROM table_A
LEFT JOIN table_B ON join_condition
WHERE row_conditionCode language: SQL (Structured Query Language) (sql)

SQL OUTER JOIN – left outer join example

The following query selects all customers and their orders:

SELECT c.customerid, 
       c.companyName,
       orderid
FROM customers c
LEFT JOIN orders o ON o.customerid = c.customerid
ORDER BY orderidCode language: SQL (Structured Query Language) (sql)