PostgreSQL Tutorial
Data Types
Querying & Filtering Data
Managing Tables
Modifying Data
Conditionals
Control Flow
Transactions & Constraints
Working with JOINS & Schemas
Roles & Permissions
Working with Sets
Subquery & CTEs
User-defined Functions
Important In-Built Functions
PostgreSQL PL/pgSQL
Variables & Constants
Stored Procedures
Working with Triggers
Working with Views & Indexes
Errors & Exception Handling
In SQL, an INNER JOIN returns rows from both tables where there is a match based on the specified join condition. If there's no match, the row won't appear in the result set.
The INNER JOIN keyword in PostgreSQL (and most other relational database management systems) is used to combine rows from two or more tables based on a related column between them.
SELECT column1, column2, ... FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;
Let's say you have two tables: orders and customers, and you want to match them based on the customer_id.
orders table:
| order_id | product | customer_id |
|---|---|---|
| 1 | Apple | 3 |
| 2 | Banana | 2 |
| 3 | Cherry | 5 |
customers table:
| customer_id | customer_name |
|---|---|
| 1 | John |
| 2 | Jane |
| 3 | Bob |
| 4 | Alice |
The following SQL statement selects all orders with their associated customer names:
SELECT orders.order_id, orders.product, customers.customer_name FROM orders INNER JOIN customers ON orders.customer_id = customers.customer_id;
Result:
| order_id | product | customer_name |
|---|---|---|
| 1 | Apple | Bob |
| 2 | Banana | Jane |
Note that the result only includes rows where there's a match in both tables. For instance, there's no order for John or Alice, and there's no customer associated with the Cherry order in the orders table. Hence, they are excluded from the result.
You can use multiple INNER JOIN operations in a single query to combine more than two tables. For instance, if you have a third table for products and want to combine information from orders, customers, and products, you'd just add another INNER JOIN clause in the query.
When joining tables with columns that have the same name in both tables (like customer_id in the above example), it's a good practice to use the table name (or table alias) as a prefix to the column name to avoid ambiguity.
INNER JOIN can be abbreviated as JOIN in PostgreSQL and most other databases. Both mean the same and can be used interchangeably.
Always ensure proper indexing on columns that are frequently used in join conditions to optimize query performance.
How to use INNER JOIN in PostgreSQL:
SELECT * FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;
PostgreSQL INNER JOIN example:
SELECT orders.order_id, customers.customer_name FROM orders INNER JOIN customers ON orders.customer_id = customers.customer_id;
JOIN vs. INNER JOIN in PostgreSQL:
-- Both are equivalent SELECT * FROM table1 JOIN table2 ON table1.column_name = table2.column_name; SELECT * FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;
Multiple tables INNER JOIN in PostgreSQL:
SELECT * FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name INNER JOIN table3 ON table2.another_column = table3.another_column;
Filtering results with INNER JOIN in PostgreSQL:
SELECT * FROM orders INNER JOIN customers ON orders.customer_id = customers.customer_id WHERE orders.order_status = 'Shipped';
Using aliases with INNER JOIN in PostgreSQL:
SELECT o.order_id, c.customer_name FROM orders AS o INNER JOIN customers AS c ON o.customer_id = c.customer_id;
INNER JOIN and WHERE clause in PostgreSQL:
SELECT * FROM employees INNER JOIN departments ON employees.department_id = departments.department_id WHERE departments.department_name = 'Sales';
INNER JOIN with aggregate functions in PostgreSQL:
SELECT customers.customer_id, COUNT(orders.order_id) AS order_count FROM customers INNER JOIN orders ON customers.customer_id = orders.customer_id GROUP BY customers.customer_id;
Nested INNER JOIN in PostgreSQL:
SELECT * FROM table1 INNER JOIN (table2 INNER JOIN table3 ON table2.column_name = table3.column_name) ON table1.column_name = table2.column_name;
Combining INNER JOIN with other JOIN types in PostgreSQL:
SELECT * FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name LEFT JOIN table3 ON table2.another_column = table3.another_column;
INNER JOIN on non-primary key columns in PostgreSQL:
SELECT * FROM employees INNER JOIN departments ON employees.department_name = departments.department_name;
JOIN conditions and NULL values in INNER JOIN in PostgreSQL:
SELECT * FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name WHERE table1.column_name IS NOT NULL AND table2.column_name IS NOT NULL;
Using INNER JOIN with subqueries in PostgreSQL:
SELECT * FROM employees INNER JOIN (SELECT department_id FROM departments WHERE department_name = 'Sales') AS subquery ON employees.department_id = subquery.department_id;
ORDER BY with INNER JOIN in PostgreSQL:
SELECT * FROM products INNER JOIN categories ON products.category_id = categories.category_id ORDER BY categories.category_name, products.product_name;
Comparing INNER JOIN with EXISTS in PostgreSQL:
-- INNER JOIN
SELECT customers.customer_id, customers.customer_name
FROM customers
INNER JOIN orders ON customers.customer_id = orders.customer_id;
-- EXISTS
SELECT customer_id, customer_name
FROM customers
WHERE EXISTS (
SELECT 1
FROM orders
WHERE customers.customer_id = orders.customer_id
);
INNER JOIN and foreign key relationships in PostgreSQL:
SELECT employees.employee_name, departments.department_name FROM employees INNER JOIN departments ON employees.department_id = departments.department_id;