Inner Join and Outer Join in SQL
We use Join when we want to get records which match condition(s) in two or more tables. This kind of cross-table query is very common. Generally speaking, there are two generic types of Join:
- Inner Join (inner join in SQL): Return the records which satisfy the given condition in both tables.
- Outer Join: there are three specific types of outer join in SQL
- Left Join (left join in SQL): Return all records from the left table, and the matched records from the right table
- Right Join (right join in SQL): Return all records from the right table, and the matched records from the left table
- Full Join (full join in SQL): Return all records from both left and right tables, including the matched records satisfying in both tables
For outer join, if there is no match, the data of a specific field will be NULL.

To demonstrate different join types, we will create tables first via PostgreSQL and use its own GUI tool (pgAdmin) to manage databases.
Step 1: Create a table named “books” with create table command
create table books (
book_code char(5) primary key,
title varchar(40),
author_code char(5)
);Step 2: Insert records to “books” table with insert into command
insert into books (book_code, title, author_code)
values ('B0001', 'Book 1', 'A0002'),
('B0002', 'Book 2', 'A0005'),
('B0003', 'Book 3', 'A0007');Step 3: Check contents in “books” table with select command

Step 4: Create a table named “authors” with create table command
create table authors(
author_code char(5) primary key,
name varchar(40)
);Step 5: Insert records to “authors” table with insert into command
insert into authors (author_code, name)
values ('A0001', 'John'),
('A0002', 'Mary'),
('A0003', 'Eason');Step 6: Check contents in “authors” table with select command

Let’s try: “books” table inner join “authors” table


Let’s try: “books” table left join “authors” table

For “books” table left join “authors” table, the result contains all the records in the “books” table (at the left side) and also the matched records in the “authors” table (at the right side). If there is no match from “authors” table, the data is NULL (so it is blank in the figure above). For example, “Book 2” and “Book 3” have no corresponding author name in the “authors” table.
Let’s try: “books” table right join “authors” table

Regarding “books” table right join “authors” table, the result contains all the records in the “authors” table (at the right side) and also the matched records in the “books” table (at the left side). If there is no match from “books” table, the data is NULL. For example, the authors “Eason” and “John” do not publish any books.
Let’s try: “books” table full join “authors” table

When we do “books” table full join “authors” table, the result contains all records from both “books” table (at the left side) and “authors” table (at the right side), including the matched records satisfying in both tables. If there is no match, the data is NULL.
Question: What if we want to know the number of books written by each author?
Since we focus on “by each author”, we have to include all the authors in the result. So, we should make it as:
select something
from authors left join booksOK, now we can use group by to get our result like this:

Group by is often used together with aggregation function, such as count. Like the part of the question: the number of books, which is expressed as count(books.book_code) in SQL.
Wait! What if we want to show the result with NumberOfBooks in the ascending order?
No problem! We can sort the result with order by command!

Good! So, from this article, you know:
- How to create a table
- How to insert records into an existing table
- How to use inner join, and outer join (including left join, right join, and full join)
- How to group query result
- How to sort query result
