CodeIgniter Forums
Subquery or Join - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24)
+--- Thread: Subquery or Join (/showthread.php?tid=69016)

Pages: 1 2


Subquery or Join - omid_student - 09-27-2017

Hello
I have 2 table that have relation both
I try to get result from table 2 with 2 way:
1: select table1.name,table2.score from table2 join table1 on table1.username = table2.username
2: select (select name from table1 where table1.username = table2.username) as name,score from table2

Now
Which way is good?

Note: i need name field only in table1


RE: Subquery or Join - rtenny - 09-27-2017

I am not sure on this but i believe option is will be the faster one (if you have your index set correct)
The second one would create 2 selects and might not be able to use the index as efficiant.

But somebody with more DB knowledge might correct me on this.


RE: Subquery or Join - omid_student - 09-27-2017

(09-27-2017, 08:42 AM)rtenny Wrote: I am not sure on this but i believe option is will be the faster one (if you have your index set correct)
The second one would create 2 selects and might not be able to use the index as efficiant.

But somebody with more DB knowledge might correct me on this.

Thanks


RE: Subquery or Join - dave friend - 09-27-2017

I agree that the "join" version will optimize better and run faster.


RE: Subquery or Join - omid_student - 09-28-2017

(09-27-2017, 01:48 PM)daveĀ friend Wrote: I agree that the "join" version will optimize better and run faster.

But when i use join,it return other fields,is it?


RE: Subquery or Join - dave friend - 09-28-2017

(09-28-2017, 11:31 AM)omid_student Wrote:
(09-27-2017, 01:48 PM)daveĀ friend Wrote: I agree that the "join" version will optimize better and run faster.

But when i use join,it return other fields,is it?

I should only return the fields you ask for.


RE: Subquery or Join - rtenny - 09-29-2017

select table1.name,table2.score from table2 join table1 on table1.username = table2.username

it will only return table1.name,table2.score nothing else unless you add it to the field list


RE: Subquery or Join - ciadvantage - 09-29-2017

select table1.name,table2.score from table2 join table1 on table1.username = table2.username

This is equivalent and give you better picture

select t1.name as 'name', t2.score as 'score' from table2 t2
join table1 t1 on t1.username= t2.username

Run the query only show 2 columns : 'name' and 'score'

Regards


RE: Subquery or Join - omid_student - 09-29-2017

(09-29-2017, 08:05 AM)ciadvantage Wrote: select table1.name,table2.score from table2 join table1 on table1.username = table2.username

This is equivalent and give you better picture

select t1.name as 'name', t2.score as 'score' from table2 t2
join table1 t1 on t1.username= t2.username

Run the query only show 2 columns : 'name' and 'score'

Regards

Ok
So Join is better than subquery


RE: Subquery or Join - php_rocs - 09-29-2017

@omid_student,

Don't forget that you can use the EXPLAIN statement to determine which one is more optimized or can be improved.