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.
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
1 2 3 4 5 6 7 8 | 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:
- BOOKCUSTOMERS – It contains data about our customers.
- 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
| ID | NAME | AGE | ADDRESS | SALARY |
|---|---|---|---|---|
| 1 | SAM | 32 | Austin | 8000.00 |
| 2 | John | 25 | Ohio | 9500.00 |
| 3 | Angela | 23 | New York | 7000.00 |
| 4 | Joe | 25 | Texas | 6500.00 |
| 5 | April | 27 | San Marcos | 8500.00 |
TABLE B: ORDERS
| OID | DATE | CUSTOMER_ID | AMOUNT |
|---|---|---|---|
| 102 | 2019-12-08 00:00:00 | 3 | 4000 |
| 100 | 2019-09-08 00:00:00 | 3 | 3500 |
| 101 | 2019-08-20 00:00:00 | 2 | 6560 |
| 103 | 2018-05-20 00:00:00 | 4 | 2060 |
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:
| ID | NAME | AMOUNT |
|---|---|---|
| 3 | Angela | 4000 |
| 3 | Angela | 3500 |
| 2 | John | 6560 |
| 4 | Joe | 2060 |
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