SQL JOIN: inner and outer

When joining two tables in an SQL statement, there are two options. You can use an inner join, or you can use an outer join. Let’s see what the differences are.

Inner Join

An inner join (aka simple join) returns those records, and only those records, where the join condition matches. What that means is that if there are records on either table not matched in the other, those records will be omitted from your results. Here’s an example of the syntax

SELECT t1.*, t2.* FROM t1 INNER JOIN t2 ON t1.key = t2.key;

or simply

SELECT t1.*, t2.* FROM t1 JOIN t2 ON t1.key = t2.key;

Records in t1 without a record with the same key in t2 AND records in t2 without a corresponding key in t1 will be omitted.

Outer Join

In an outer join, one of the two tables involved works much like a master table. All records from that table will be returned, and only those that match the join condition from the other table will also be pulled out. For those records only available in the master table but not in the secondary one, the fields from the latter table will all be set to NULL.

Because of its very nature, there are two types of outer join, a LEFT outer join (the first table in the SQL statement is the master table) and a RIGHT outer join (the second table is the master). Her’s a few examples

SELECT t1.*, t2.* FROM t1 LEFT OUTER JOIN t2 ON t1.key = t2.key;

SELECT t1.*, t2.* FROM t1 RIGHT OUTER JOIN t2 ON t1.key = t2.key;

In the first statement you get all records from t1 and only those records that have a key matching in t1 from t2. In the second statement it’s exactly the opposite: all records from t2 and only matching records from t1.

Because the keywords ‘left’ and ‘right’ only make sense for an outer join, you can omit the ‘outer’ keyword like so

SELECT t1.*, t2.* FROM t1 LEFT JOIN t2 ON t1.key = t2.key;
SELECT t1.*, t2.* FROM t1 RIGHT JOIN t2 ON t1.key = t2.key;

Leave a comment

Your email address will not be published. Required fields are marked *


*