Inner and Outer Joins

Outer and Inner SQL Joins

SQL is such a wonderful language and one question I get from people that I talk with is what is the difference between Outer and Inner Joins. Before we talk about that lets talk about what a Join is. A join is used to compare and combine — literally join — and return specific rows of data from two or more tables in a database. It really is that simple. Now an inner join finds and returns matching data from tables, while an outer join finds and returns matching data and some dissimilar data from tables.

Inner Join
An inner join targets on the commonality between two tables. When using an inner join, there must be at least some matching data between two (or more) tables that are being compared. An inner join searches tables for matching or overlapping data. Upon finding it, the inner join combines and returns the information into one new table.

Outer Join
An outer join returns a set of records (or rows) that include what an inner join would return but also includes other rows for which no corresponding match is found in the other table.

There are three types of outer joins:

  • Left Outer Join (or Left Join) – returns only unmatched rows from the left table.
  • Right Outer Join (or Right Join) – returns only unmatched rows from the right table.
  • Full Outer Join (or Full Join) – returns unmatched rows from both tables.

Each of these outer joins refers to the part of the data that is being compared, combined, and returned. Sometimes nulls will be produced in this process as some data is shared while other data is not.

To see examples of LEFT OUTER and RIGHT OUTER JOINS look back at this blog RIGHT JOIN and LEFT JOIN.

SQL Right Joins

RIGHT JOIN Keyword

Since I did LEFT JOIN the other day, I figured I might as well do a blog on the Right Join to keep it fair.

Define


In SQL the RIGHT JOIN keyword will return all records from the right table (table b), and the matched records from the left table (table a). The result is NULL from the left side, if there is no match.

RIGHT JOIN Syntax

SELECT column_names(s)
FROM table_a
RIGHT JOIN table_b ON table_b.column_name = table_a.column_name

Instructional


In this instruction we will use two tables to demonstrate using RIGHT JOIN within a SQL statement. Lets first define two database tables as well as include the data within those tables.

User table

Id Name Address Zip_Code
5 Bob Vance 45 Geear St 12345
6 Dwight Schrute 55 Sweet St 65565
7 Jim Halbret 65 Nice Rd 43455

Order Table

Id User_Id Employee_Id Order_Date
3 5 67 2019-01-01
45 15 89 2019-01-01
55 16 34 2019-01-01

Example


The following SQL statement will select all users, and any orders they might have.

SQL Demo

SELECT User.Name, Order.Id
FROM Order
RIGHT JOIN User ON Order.User_Id = User.Id
ORDER BY User.Name DESC;

SQL Left Joins

LEFT JOIN Keyword

I wanted to write a quick blog on LEFT JOIN because I get so many questions about this topic from young DB Admin’s and developers as well as Business Managers.

Define


In SQL the LEFT JOIN keyword will return all records from the left table (table a), and the matched records from the right table (table b). The result is NULL from the right side, if there is no match.

LEFT JOIN Syntax

SELECT column_names(s)
FROM table_a
LEFT JOIN table_b ON table_a.column_name = tableb.column_name

Instructional


In this instruction we will use two tables to demonstrate using LEFT JOIN within a SQL statement. Lets first define two database tables as well as include the data within those tables.

User table

Id Name Address Zip_Code
5 Bob Vance 45 Geear St 12345
6 Dwight Schrute 55 Sweet St 65565
7 Jim Halbret 65 Nice Rd 43455

Order Table

Id User_Id Employee_Id Order_Date
3 5 67 2019-01-01
45 15 89 2019-01-01
55 16 34 2019-01-01

Example


The following SQL statement will select all users, and any orders they might have.

SQL Demo

SELECT User.Name, Order.Id
FROM User
LEFT JOIN Order ON User.Id = Order.User_Id
ORDER BY User.Name DESC;