Understanding the Problem and the SQL Query

As a developer, we often encounter situations where we need to fetch data from multiple tables based on certain conditions. In this case, we have two tables: e_state and usr. The e_state table has three columns: State_id, country_id, and state_name. The usr table is used to store user inputs, including a state id that needs to be compared with the e_state table. When we fetch records from the usr table, we need to include data from the e_state table if there’s a match.

The Current SQL Query

The current SQL query being used is:

SELECT 
    usr.USER_ID,
    usr.USER_FNAME,
    usr.USER_LNAME,
    usr.STATE_ID,
    usr.CITY_ID,
    state.STATE_NAME,
    cty.CITY_NAME,
    rct.API_REQUEST_DATE_TIME,
    rct.API_RESPONSE_DATE_TIME,
    DATE(rct.API_REQUEST_DATE_TIME) AS API_REQUEST_DATE,
    TIME(rct.API_REQUEST_DATE_TIME) AS API_REQUEST_TIME,
    DATE(rct.API_RESPONSE_DATE_TIME) AS API_RESPONSE_DATE,
    TIME(rct.API_RESPONSE_DATE_TIME) AS API_RESPONSE_TIME,
    rct.API_OPERATOR,
    rct.API_OPERATOR_ID,
    rct.TRANSACTION_ID,
    rct.DEVICE_NUMBER,
    rct.RECHARGE_AMOUNT,
    rct.RC_STATUS_CODE,
    rct.RECHARGE_TYPE,
    rct.API_TRANSACTION_ID,
    rct.REFUND_STATUS,
    uth.PAYMENT_MODE 
FROM
    e_user_trans_hist uth,
    e_rc_transaction rct,
    e_user usr,
    e_state state,
    e_city cty
WHERE
    rct.USER_ID = usr.USER_ID
        AND usr.STATE_ID = state.STATE_ID 
        AND usr.CITY_ID = cty.CITY_ID
        AND uth.TRANSACTION_ID = rct TRANSACTION_ID
        AND rct.RECHARGE_TYPE = 'MOBILE' 
ORDER BY  rct.API_RESPONSE_DATE_TIME DESC;

This query is using the INNER JOIN syntax, which means that only records with matching values in both tables are included in the results. However, this approach has a limitation: it won’t include data from the usr table where there’s no match in the e_state table.

Improving the Query

To solve this issue, we need to change the query to use the LEFT OUTER JOIN syntax instead of INNER JOIN. This will allow us to include all records from the usr table, even if there’s no match in the e_state table.

Here’s how the modified query looks like:

FROM
  e_user_trans_hist uth
INNER JOIN
  e_rc_transaction rct
    ON uth.TRANSACTION_ID = rct.TRANSACTION_ID
INNER JOIN
  e_user usr
    ON rct.USER_ID = usr.USER_ID
LEFT OUTER JOIN
  e_state state
    ON usr.STATE_ID = state.STATE_ID 
INNER JOIN -- Possibly here too?
  e_city cty
    ON usr.CITY_ID = cty.CITY_ID
WHERE
  rct.RECHARGE_TYPE = 'MOBILE' 
ORDER BY
  rct.API_RESPONSE_DATE_TIME DESC;

By using the LEFT OUTER JOIN syntax, we can include all records from the usr table, and then filter out any records that don’t have a match in the e_state table.

How it Works

When we use the LEFT OUTER JOIN syntax, SQL creates a new virtual table that contains all records from both tables. If there’s no match between the two tables, the result set will contain NULL values for the columns that are joined on.

In this case, when we join the usr table with the e_state table using a LEFT OUTER JOIN, SQL creates a new virtual table like this:

As you can see, the records with no match in the e_state table (1 and 4) have NULL values for the state.STATE_NAME column.

Filtering Out Non-Matching Records

To filter out these non-matching records, we need to add a condition to the WHERE clause that only includes rows where there’s a match in the e_state table. We can do this by using the following query:

FROM
  e_user_trans_hist uth
INNER JOIN
  e_rc_transaction rct
    ON uth.TRANSACTION_ID = rct.TRANSACTION_ID
INNER JOIN
  e_user usr
    ON rct.USER_ID = usr.USER_ID
LEFT OUTER JOIN
  e_state state
    ON usr.STATE_ID = state.STATE_ID 
INNER JOIN -- Possibly here too?
  e_city cty
    ON usr.CITY_ID = cty.CITY_ID
WHERE
  rct.RECHARGE_TYPE = 'MOBILE'
  AND (usr.STATE_ID IS NOT NULL OR state.STATE_NAME IS NULL)
ORDER BY
  rct.API_RESPONSE_DATE_TIME DESC;

By adding the AND clause that checks for either a non-NULL value in the STATE_ID column or a NULL value in the STATE_NAME column, we can filter out any records where there’s no match in the e_state table.

Conclusion

In this article, we’ve discussed how to improve an SQL query to include data from multiple tables based on certain conditions. By using the LEFT OUTER JOIN syntax and adding a condition to the WHERE clause, we can filter out non-matching records and ensure that our queries are more robust and efficient.

Last modified on 2024-06-29