Correct SQL Query for Active Loans

The SQL query is written correctly and effectively for retrieving clients with active loans. It properly uses the END_DATE IS NULL condition to identify active loans and correctly joins the loan contract tables with clients through JOIN.

Contents

• Analysis of the current SQL query
• Logic correctness check
• Performance optimization
• Alternative approaches
• Additional recommendations

Analysis of the current SQL query

Your query is functionally correct and solves the stated task:

SELECT 
    t3.NAME,
    t3.BIRTH_DATE,
    t3.CITY,
    t1.CREDIT_CONTRACT_ID,
    t1.START_DATE,
    t1.END_DATE,
    t1.CREDIT_AMOUNT
FROM t1
JOIN t3 ON t1.CUSTOMER_ID = t3.CUSTOMER_ID
WHERE t1.END_DATE IS NULL;

Advantages of the current approach:

• Uses INNER JOIN to correctly link loan contracts with clients
• Applies the WHERE t1.END_DATE IS NULL condition to filter active loans
• Returns all necessary fields for analyzing active loans

Important: The END_DATE IS NULL condition is indeed the standard way to identify active loans in most databases, as a NULL value in the end date field indicates an incomplete contract.

Logic correctness check

To fully verify the correctness of the query, let’s analyze it step by step:

1. JOIN operation: JOIN t3 ON t1.CUSTOMER_ID = t3.CUSTOMER_ID correctly links each active loan with its corresponding client

2. Filtering active loans: WHERE t1.END_DATE IS NULL selects only those loans that have not yet been repaid

3. Data selection: The query returns all necessary information for analyzing active loans

Query results on the provided data:
The query will return clients with IDs 2, 3, 4, 5, 6, 7, 8, 9 - all those who have loans with END_DATE = NULL.

Potential issues:

• The query does not consider the t2 table (deposit contracts), but in this context, this is not an issue since we are only interested in loans
• There is no check for possible duplicates if a client has multiple active loans

Performance optimization

Although the current query is functionally correct, it can be optimized for better performance:

1. JOIN type selection

-- INNER JOIN (current option)
-- Returns only clients with active loans, which makes sense for this task

-- LEFT JOIN (if all clients are needed, even those without loans)
-- But this is not required in this case

2. Indexes for improved performance

-- It is recommended to create indexes on fields used in JOIN and WHERE
CREATE INDEX idx_t1_customer_id ON t1(CUSTOMER_ID);
CREATE INDEX idx_t1_end_date ON t1(END_DATE);

3. Field selection optimization

-- If only main fields are needed, you can limit the selection
SELECT 
    t3.NAME,
    t1.CREDIT_CONTRACT_ID,
    t1.CREDIT_AMOUNT,
    t1.START_DATE
FROM t1
JOIN t3 ON t1.CUSTOMER_ID = t3.CUSTOMER_ID
WHERE t1.END_DATE IS NULL;

Alternative approaches

1. Using COALESCE to explicitly indicate active loans

SELECT 
    t3.NAME,
    t1.CREDIT_CONTRACT_ID,
    t1.CREDIT_AMOUNT
FROM t1
JOIN t3 ON t1.CUSTOMER_ID = t3.CUSTOMER_ID
WHERE COALESCE(t1.END_DATE, CURRENT_DATE) > CURRENT_DATE;

This approach can be useful if the logic for determining active loans changes in the future.

2. Using NOT EXISTS for complex conditions

SELECT 
    t3.NAME,
    t1.CREDIT_CONTRACT_ID,
    t1.CREDIT_AMOUNT
FROM t3
JOIN t1 ON t3.CUSTOMER_ID = t1.CUSTOMER_ID
WHERE NOT EXISTS (
    SELECT 1 FROM t1 t2 
    WHERE t2.CUSTOMER_ID = t3.CUSTOMER_ID 
    AND t2.END_DATE IS NOT NULL
);

3. Aggregation for clients with multiple loans

SELECT 
    t3.NAME,
    COUNT(t1.CREDIT_CONTRACT_ID) AS active_credits_count,
    SUM(t1.CREDIT_AMOUNT) AS total_credit_amount
FROM t1
JOIN t3 ON t1.CUSTOMER_ID = t3.CUSTOMER_ID
WHERE t1.END_DATE IS NULL
GROUP BY t3.NAME, t3.CUSTOMER_ID;

Additional recommendations

1. Data integrity check

Before using the query, it is recommended to check data integrity:

-- Check for NULL values in CUSTOMER_ID
SELECT COUNT(*) FROM t1 WHERE CUSTOMER_ID IS NULL;

-- Check for uniqueness of CUSTOMER_ID in the client table
SELECT COUNT(*) FROM t3;
SELECT COUNT(DISTINCT CUSTOMER_ID) FROM t3;

2. Adding sorting for easier reading

SELECT 
    t3.NAME,
    t1.CREDIT_CONTRACT_ID,
    t1.CREDIT_AMOUNT,
    t1.START_DATE
FROM t1
JOIN t3 ON t1.CUSTOMER_ID = t3.CUSTOMER_ID
WHERE t1.END_DATE IS NULL
ORDER BY t3.NAME, t1.START_DATE DESC;

3. Using aliases for better readability

SELECT 
    c.NAME AS customer_name,
    cr.CREDIT_CONTRACT_ID,
    cr.CREDIT_AMOUNT,
    cr.START_DATE
FROM t1 cr
JOIN t3 c ON cr.CUSTOMER_ID = c.CUSTOMER_ID
WHERE cr.END_DATE IS NULL
ORDER BY c.NAME;

4. Security and edge case handling

-- Adding validation for correct dates
SELECT 
    c.NAME,
    cr.CREDIT_CONTRACT_ID,
    cr.CREDIT_AMOUNT
FROM t1 cr
JOIN t3 c ON cr.CUSTOMER_ID = c.CUSTOMER_ID
WHERE cr.END_DATE IS NULL
  AND cr.START_DATE <= CURRENT_DATE
  AND (cr.CREDIT_AMOUNT > 0 OR cr.CREDIT_AMOUNT IS NULL);

Recommended final query:

SELECT 
    c.NAME AS customer_name,
    c.CITY,
    cr.CREDIT_CONTRACT_ID,
    cr.START_DATE,
    cr.CREDIT_AMOUNT
FROM t1 cr
JOIN t3 c ON cr.CUSTOMER_ID = c.CUSTOMER_ID
WHERE cr.END_DATE IS NULL
  AND cr.START_DATE <= CURRENT_DATE
  AND cr.CREDIT_AMOUNT > 0
ORDER BY c.NAME, cr.START_DATE DESC;

Conclusion

1. The main query is completely correct for identifying clients with active loans by checking END_DATE IS NULL

2. It is recommended to add indexes on the CUSTOMER_ID and END_DATE fields to improve performance with large data volumes

3. For better code readability, meaningful table aliases should be used instead of t1, t2, t3

4. Data integrity checks and edge case handling should be added in production environments

5. For analytical tasks, aggregate functions can be used to get summary information for clients with multiple loans

Your initial approach is an excellent starting point, and the suggested optimizations will help make the query more reliable and performant.

Sources

1. Official MySQL Documentation - JOIN Syntax
2. W3Schools SQL NULL Values
3. Stack Overflow - Checking for NULL values in SQL
4. SQLShack - SQL Query Performance Optimization