Which approach is better to retrieve data from a database

Question

I got confused in choosing two approaches.

The script
has two tables Table 1and Table 2respectively. Table 1contains user data such as first name, last name, etc.

Table 2contains cars that every user has with a description. ie Color, Registration Noetc.

Now, if I want to get all the information from all users, which approach is best done in a minimal amount of time?

Approach 1.

Request for all lines in Table 1and save them all in the list for ex.

Then, go through the list and request it and get the data from Table 2according to the user saved in the first step.

Approach 2

Query for all rows and save this row to get all values from Table 2and save them too.

If I think about system processes, then I think that it can be the same thing, because in both approaches there are no identical records.

If there is any other idea, please let me know.

+3

Afnan bashir May 21 '11 at 9:17

1 answer

usr · Accepted Answer · 2011-05-21T09:21:55+0000

Your two approaches will have the same performance (slow due to N + 1 queries). You can quickly make such a request as follows:

select *
from T1
left join T2 on ...
order by T1.PrimaryKey

Your client application can interpret the results and have all the data in one request. An alternative could be:

select *, 1 as Tag
from T1
union all
select *, 2 as Tag
from T2
order by T1.PrimaryKey, Tag

This is just pseudo code, but you can make it work.

union-all , sql- " ", . -, .