INNER JOIN in PostgreSQL 🚀

An INNER JOIN in PostgreSQL returns only the rows that have matching values in both tables. If there is no match between the tables, the row is excluded from the result set. This is the most common type of join.

1. Basic Syntax

SELECT column1, column2FROM table1INNER JOIN table2ON table1.common_column = table2.common_column;

• The INNER JOIN retrieves only the rows where the common_column in table1 matches the common_column in table2.

2. Example: Get All Orders with Their Customers

SELECT orders.order_id, customers.customer_nameFROM ordersINNER JOIN customersON orders.customer_id = customers.id;

🔹 Returns only orders that have a corresponding customer. Orders without customers are excluded.

3. Example: Get Employees and Their Departments

SELECT employees.name, departments.department_nameFROM employeesINNER JOIN departmentsON employees.dept_id = departments.id;

🔹 Retrieves only employees that belong to a department (excludes employees without a department).

4. Example: Get Products with Their Categories

SELECT products.product_name, categories.category_nameFROM productsINNER JOIN categoriesON products.category_id = categories.id;

🔹 Returns products that belong to a category, excluding products without a category.

5. Example: INNER JOIN with Multiple Tables

SELECT orders.order_id, customers.name, products.product_nameFROM ordersINNER JOIN customersON orders.customer_id = customers.idINNER JOIN productsON orders.product_id = products.id;

🔹 Retrieves orders, with corresponding customers and products. Only orders with matching customers and products are returned.

6. Example: INNER JOIN with WHERE Clause

SELECT employees.name, departments.department_nameFROM employeesINNER JOIN departmentsON employees.dept_id = departments.idWHERE departments.department_name = 'Sales';

🔹 Returns employees who belong to the Sales department.

7. Example: INNER JOIN with Aggregation

SELECT departments.department_name, COUNT(employees.id) AS num_employeesFROM departmentsINNER JOIN employeesON employees.dept_id = departments.idGROUP BY departments.department_name;

🔹 Returns departments and the count of employees in each department. Only departments with employees are shown.

8. Example: INNER JOIN with ORDER BY

SELECT orders.order_id, customers.customer_nameFROM ordersINNER JOIN customersON orders.customer_id = customers.idORDER BY customers.customer_name;

🔹 Sorts the results by customer name.

9. Difference Between INNER JOIN, LEFT JOIN, and RIGHT JOIN

Example Comparison:

Using INNER JOIN

SELECT * FROM employeesINNER JOIN departments ON employees.dept_id = departments.id;

🔹 Only employees with departments are shown.

Using LEFT JOIN

SELECT * FROM employeesLEFT JOIN departments ON employees.dept_id = departments.id;

🔹 All employees are shown, even those without departments.

Using RIGHT JOIN

SELECT * FROM employeesRIGHT JOIN departments ON employees.dept_id = departments.id;

🔹 All departments are shown, even if no employees belong to a department.

10. Performance Considerations

• INNER JOIN is generally more efficient than LEFT JOIN or RIGHT JOIN because it only returns rows where there is a match.
• Ensure that columns used in the ON clause are indexed for better performance, especially with large datasets.
• Use EXPLAIN ANALYZE to check the performance of your queries.

Next Steps

✅ Use INNER JOIN for matching rows across tables
✅ Combine INNER JOIN with aggregations and filters
✅ Integrate INNER JOIN in Go Fiber queries