CODEWITHSUNDEEP
oracle-database
Diogo Basilio
Diogo Basilio

Reputation: 11

Combining 2 tables with the number of users on a different table ORACLE SQl

Is this considered good practice? Been trying to merge data from 2 different tables (dsb_nb_users_option & dsb_nb_default_options) for the number of users existing on dsb_nb_users table. Would a JOIN statement be the best option? Or a sub-query would work better for me to access the data laying on dsb_nb_users table?

This might be an operation i will have to perform a few times so i want to understand the mechanics of it.

INSERT INTO dsb_nb_users_option(dsb_nb_users_option.code, dsb_nb_users_option.code_value, dsb_nb_users_option.status)
SELECT dsb_nb_default_option.code, dsb_nb_default_option.code_value, dsb_nb_default_option.status
FROM dsb_nb_default_options
WHERE dsb_nb_users.user_id IS NOT NULL;

Thank you for your time!!

Upvotes: 0

Views: 31

Answers (1)

EdStevens
EdStevens

Reputation: 3872

On the face of it, I see nothing wrong with your query to achieve your goal. That said, I see several things worth pointing out.

First, please learn to format your code - for your own sanity as well as that of others who have to read it. At the very least, put each column name on a line of its own, indented. A good IDE tool like SQL Developer will do this for you. Like this:

INSERT INTO dsb_nb_users_option (
    dsb_nb_users_option.code,
    dsb_nb_users_option.code_value,
    dsb_nb_users_option.status
)
    SELECT
        dsb_nb_default_option.code,
        dsb_nb_default_option.code_value,
        dsb_nb_default_option.status
    FROM
        dsb_nb_default_options
    WHERE
        dsb_nb_users.user_id IS NOT NULL;

Now that I've made your code more easily readable, a couple of other things jump out at me. First, it is not necessary to prefix every column name with the table name. So your code gets even easier to read.

INSERT INTO dsb_nb_users_option (
    code,
    code_value,
    status
)
    SELECT
        code,
        code_value,
        status
    FROM
        dsb_nb_default_options
    WHERE
        dsb_nb_users.user_id IS NOT NULL;

Note that there are times you need to qualify a column name, either because oracle requires it to avoid ambiguity to the parser, or because the developer needs it to avoid ambiguity to himself and those that follow. In this case we usually use table name aliasing to shorten the code.

select a.userid,
       a.username,
       b.user_mobile_phone
from users a,
     join user_telephones b on a.userid=b.userid;

Finally, and more critical your your overall design, It appears that you are unnecessarily duplicating data across multiple tables. This goes against all the rules of data design. Have you read up on 'data normalization'? It's the foundation of relational database theory.

Upvotes: 1

Related Questions