PostgreSQL INNER JOIN

PostgreSQL INNER JOIN

PostgreSQL INNER JOIN

An INNER JOIN is used to retrieve data from two tables where there is a match between the specified columns in both tables. Only rows that satisfy the join condition will be included in the result. If there is no match, the row is excluded from the final result.

1. Basic Syntax of INNER JOIN

SELECT column1, column2, ... FROM table1 INNER JOIN table2 ON table1.column = table2.column;
  • table1 and table2: The tables that are being joined.
  • table1.column = table2.column: The condition that must be met for the rows to be matched between the two tables.

2. Example: Using INNER JOIN to Retrieve Data from Two Tables

Example 1: Get All Customers and Their Orders

Consider two tables:

  • customers: Contains customer details (e.g., customer_id, first_name, last_name).
  • orders: Contains order details (e.g., order_id, customer_id, order_date).

Query: Retrieve All Customers with Orders

SELECT customers.first_name, customers.last_name, orders.order_date FROM customers INNER JOIN orders ON customers.customer_id = orders.customer_id;
  • This query returns customers who have placed orders, along with the order date. If a customer has no order, they will not appear in the result.

Result:

first_namelast_nameorder_date
JohnDoe2025-01-01
AliceJohnson2025-01-05
  • Note: Only customers who have placed orders are included in the result.

3. Example: INNER JOIN with Multiple Tables

Example 2: Get Employees and Their Assigned Projects

Consider the following tables:

  • employees: Contains employee details (employee_id, first_name, last_name).
  • projects: Contains project details (project_id, employee_id, project_name).

Query: Retrieve Employees and Their Assigned Projects

SELECT employees.first_name, employees.last_name, projects.project_name FROM employees INNER JOIN projects ON employees.employee_id = projects.employee_id;
  • This query returns employees who are assigned to projects, including the project name.

4. Example: Filtering with WHERE Clause

You can combine INNER JOIN with a WHERE clause to filter the result further.

Example 3: Get Customers and Orders on a Specific Date

Query: Retrieve Orders Made by Customers on '2025-01-01'

SELECT customers.first_name, customers.last_name, orders.order_date FROM customers INNER JOIN orders ON customers.customer_id = orders.customer_id WHERE orders.order_date = '2025-01-01';
  • This query returns customers who placed orders on January 1st, 2025.

5. Difference Between INNER JOIN and LEFT JOIN

  • INNER JOIN: Only includes rows where there is a match in both tables.
  • LEFT JOIN: Includes all rows from the left table, and matching rows from the right table. If no match is found, the result will have NULL values for columns from the right table.

Example: Customers and Orders with LEFT JOIN

SELECT customers.first_name, customers.last_name, orders.order_date FROM customers LEFT JOIN orders ON customers.customer_id = orders.customer_id;
  • In the case of LEFT JOIN, even customers who haven't placed any orders will appear, with NULL in the order_date field.

6. Performance Considerations

  • Efficiency: INNER JOIN is typically faster than LEFT JOIN because it does not need to return rows from the left table when there is no matching data in the right table.
  • Indexes: Creating indexes on the columns used in the ON condition can help improve performance for INNER JOIN queries, especially when working with large datasets.

7. Summary of INNER JOIN

OperationSQL Command
Retrieve Rows with Matching Data from Two TablesSELECT * FROM table1 INNER JOIN table2 ON condition;
Filter Data Based on Join ConditionOnly rows that match the condition are included in the result.
When to UseUse when you need only rows with matching data in both tables.
Would you like to see more examples or use cases with different kinds of joins (e.g., multiple INNER JOIN operations)? 🚀
Soeng Souy

Soeng Souy

Website that learns and reads, PHP, Framework Laravel, How to and download Admin template sample source code free.

Post a Comment

CAN FEEDBACK
close