PostgreSQL SQL Inner Join Example

What is SQL Inner Join?

Inner joins in SQL is one of the easiest joins that can be used by any SQL Developer. You can also call it as join or Equi Join. In SQL, joins work with two or more tables to get data from both tables with the help of a common field. For now, we are considering two tables inside our database.

In SQL Inner join, data is extracted from both tables which satisfy certain conditions. There is one common row used to join both the tables together. On the basis of that particular field, both tables get combined and we can access any data which satisfies the condition.

To understand it in a better way, let me show you an example of SQL Inner Join.

Image of Inner Join

How to use SQL inner join in Database?

In the section below, I’m going to share you snippets that might help you understand how Inner Joins work?

Inner Join Syntax

Here we are considering two tables:

innerjointable1.column1 , 2. innerjointable2.column1

SELECT innerjointable1.column1,innerjointable1.column2,
innerjointable2.column1,....
FROM innerjointable1
INNER JOIN innerjointable2 ON
innerjointable1.matching_column = innerjointable2.matching_column;
innerjointable1: FIRST TABLE.
innerjointable2: SECOND TABLE.
matching_column: COLUMN common TO BOTH the TABLES.

Inner Join Syntax Explanation

Select: A SQL command to access data from the database. innerjointable1.column1 & innerjointable2.column1: Here we are selecting columns from both tables using INNER JOIN.

INNER JOIN: this is the keyword to create an inner join between tables.

FROM innerjointable1 INNER JOIN innerjointable2 ON: This syntax snippet is really important. Here we are telling our database system that we are joining table 1 with table 2.

After ON, we are specifying the conditions where we are matching columns from table 1 with table 2.

Let’s see an Inner Join with some example data. So, I’m going to use a demo database with 3 columns in table A and 3 columns in table B.

For this article we’re focusing on the INNER JOIN and so I’m not writing the query code to create the demo database and tables. Assume we have the following tables:

  1. BOOKCUSTOMERS – It contains data about our customers.
  2. ORDERS — This table contains when they have bought something and how much it costs.

Goal: Here the goal is to access the “Name” field from the “BOOKCUSTOMERS” table and the “AMOUNT” they spent in one table.

TABLE A: BOOKCUSTOMERS

IDNAMEAGEADDRESSSALARY
1SAM32Austin8000.00
2John25Ohio9500.00
3Angela23New York7000.00
4Joe25Texas6500.00
5April27San Marcos8500.00

TABLE B: ORDERS

OIDDATECUSTOMER_IDAMOUNT
1022019-12-08 00:00:0034000
1002019-09-08 00:00:0033500
1012019-08-20 00:00:0026560
1032018-05-20 00:00:0042060

SQL Query to achieve our goal: `sql SQL> SELECT ID, NAME, AMOUNT FROM BOOKCUSTOMERS INNER JOIN ORDERS ON BOOKCUSTOMERS.ID = ORDERS.BOOKCUSTOMERS; `

We are accessing ID, NAME from “BOOKCUSTOMERS” then the inner join is used on the “ORDERS” table. From the “ORDERS” table, we are accessing the amount they spent.

When you hit enter, you will see a result like below:

Result:

IDNAMEAMOUNT
3Angela4000
3Angela3500
2John6560
4Joe2060

Conclusion

In this short tutorial we explained what an INNER JOIN is and how to use it. We also demonstrated the syntax using an example. These joins are super common in PostgreSQL and any other SQL based database technology so they are good concept to understand well.

If you need your production data managed by someone you trust please don’t hesitate to reach out to Object Rocket to ask how we can help. Thanks!

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.