PL/SQL Left Join

In the world of database management, efficiently retrieving and combining data from multiple tables is crucial. Among these the LEFT JOIN is particularly important because it includes all records from one table even when there are no corresponding records in another table.

In this article, we will PL/SQL Left Join by understanding its syntax and various practical examples.

PL/SQL Left Join

A LEFT JOIN in PL/SQL combines data from two tables by returning all rows from the left table and the matched rows from the right table.

If there is no match in the right table then LEFT JOIN will still show all rows from the left table with NULLs in place of any missing data from the right table.

Key Points:

• Left Table: All the rows from the table will be included in result regardless of the whether there is matching rows in right table.
  
• Right Table: Only rows that have the corresponding matches in left table are to be included. If there is no match, NULL values are returned for the columns from right table.
  
• Use Case: LEFT JOIN is commonly used when we want to ensure that the all records from primary dataset (left table) will be included in the query result even if some related data (right table) is missing.

Syntax:

SELECT 
    columns
FROM 
    left_table
LEFT JOIN 
    right_table
ON 
    left_table.column_name = right_table.column_name;

Key terms:

1. SELECT columns: It specifies which columns to retrieve, allowing us to select data from both the left and right tables.
   
2. FROM left_table: Indicates the left table, from which all records will be included in the result.
   
3. LEFT JOIN right_table: Specifies the right table and indicates that the join will include all records from the left table and matching records from the right table
   
4. ON left_table.column_name = right_table.column_name: Defines the condition for matching rows between the tables, typically involving a foreign key in the left table and a primary key in the right table.

Examples of PL/SQL Left Join

In this example, we demonstrate how to create two tables, orders and customers, and use a LEFT JOIN in PL/SQL to retrieve all records from the orders table, along with matching records from the customers table based on customer_id.

Example 1:

Create a Orders table with order_id, customer_id and order_amount columns. The CREATE TABLE statement is used to create the orders table with the order_id, customer_id and order_amount attributes. The INSERT INTO statement is used to insert the values into the table with their respective columns.

Query:

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_amount DECIMAL(10, 2)
);
INSERT INTO orders (order_id, customer_id, order_amount) 
VALUES (1001, 201, 150.00); 
INSERT INTO orders (order_id, customer_id, order_amount) 
VALUES (1002, 202, 200.00); 
INSERT INTO orders (order_id, customer_id, order_amount) 
VALUES (1003, NULL, 50.00);
INSERT INTO orders (order_id, customer_id, order_amount) 
VALUES (1004, 203, 300.00);

Output:

Explanation:

• The order_id is the primary key, and some orders might not have a corresponding customer, as seen in the case of customer_id being NULL.
  
• The table lists order details, including customer IDs associated with each order, except for one record (order_id 1003), which has a NULL customer ID.

Lets Create a customers table with customer_id and customer_name. In above code, the CREATE TABLE statement is used to create the customers table with customer_id and customer_name attributes.

INSERT INTO statement is used to insert the values into the table with their respective columns. This query is the same as the one used for the orders table, retrieving all records from orders and matching customer names from customers.

Query:

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR2(20)
);

INSERT INTO customers (customer_id, customer_name) VALUES (201, 'Michael');
INSERT INTO customers (customer_id, customer_name) VALUES (202, 'Sarah');
INSERT INTO customers (customer_id, customer_name) VALUES (203, 'David');

Output:

Explanation:

The above output table contains customer IDs and names for the customers who placed orders in the orders table where customer_id is the primary key.

Example 2: Filtering and Displaying Specific Columns

Given the `Orders` and `Customers` tables, we need to retrieve and display all orders along with the corresponding customer names. If an order does not have a matching customer, we still want to show the order details, with missing customer information represented as `NULL`.

Additionally, we want to filter the results to only show orders with an amount greater than 100.

SELECT o.order_id, o.order_amount, c.customer_name
FROM Orders o
LEFT JOIN Customers c
ON o.customer_id = c.customer_id
WHERE o.order_amount > 100;

Output:

Explanation:

In this query, we perform a LEFT JOIN between the Orders and Customers tables, similar to the first example. However, we add a WHERE clause to filter the results to include only those orders with an order_amount greater than 100.

• Orders with order_amount greater than 100 are included in the results.
• The customer_name is included if there is a matching customer.
• Orders with order_amount less than or equal to 100 (e.g., order_id 1003) are excluded from the result set.

Conclusion

In this article, we explored how to use SQL commands to manage and manipulate data across multiple tables in Oracle databases.

We focused on the LEFT JOIN operation in PL/SQL, which combines data from different tables while ensuring all records from the left (primary) table are included, even if there are no matches in the right (secondary) table. By mastering these concepts, you can efficiently retrieve and analyze data, build robust database applications, and maintain data integrity.