MySQL Join or Nest Loop

Question

I have a table called workshops, and another table called workshop_parts.

Each row in workshop_parts holds a parent ID which references its parent workshop, along with various other data for that part.

I am trying to figure out the most efficient way to retrieve and display all my data on a website.

I want to list the workshop information and then all of its parts below.

With what I know right now I would just loop through all the workshops and then have a nested loop that loops through all the parts.

Is there some way I might use a join to get all the data in one fell swoop, or is the nested loop the best solution?

You are correct in thinking you should use a JOIN, this will give you a list that has the workshop information listed along with the workshop parts. In some cases it might be better off that you get the data in pieces and put it back together on the client side, but for the most part a join is a better option. (unless the extra data/payload size overwhelms the speed difference of doing an SQL query versus offloading work to the client). — shaunhusain, May 13 '13 at 03:38

score 2 · Accepted Answer · edited May 23 '17 at 11:49

2

As per this SO post I would do it as an actual JOIN:

SQL left join vs multiple tables on FROM line?

select ws.name as ws_name, part.name as part_name, part.qty as part_quantity from workshop ws LEFT JOIN workshop_parts parts ON (ws.id = parts.workshop_id);

Left JOIN will get you anywhere a workshop id exists but leave out parts that have no matching workshop.

INNER JOIN will get you only workshops and parts that have a match (workshops without parts will not show, nor will parts without workshops)

RIGHT JOIN will get you only workshop with parts and any other parts even if they have no workshop.

EDIT Here's the best way I've found to think about it: http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html

edited May 23 '17 at 11:49

Community

1
1

answered May 13 '13 at 03:40

shaunhusain

19,630
4
38
51

@user1372122 the distinction between the two queries is that @shaunhusain 's will give you all of the workshops if they have parts or not... mine will give only workshops with parts. His is a `left join` whereas mine was a `inner join`. On the left join version you will be able to identify a workshop without parts by inspecting the part's primary key, null means no parts. – Orangepill May 13 '13 at 03:58
This makes some sense, but I am just starting to grasp how to use joins. If the workshop_parts were just a single value like the name of that part then that would be straight forward, but I need to get all the data for each part. How would that look in the joined table? Would I need to alias each part so I would have something like part1_name, part1_date, part1_description....part2_name, part2_date, part2_description....and so on? Resulting in all the parts for a workshop residing in that workshops row? – Greg G May 13 '13 at 04:07
Basically whatever you list in the "SELECT LIST" the stuff that comes after the word SELECT, becomes the columns for the returned table. The "as", word is basically renaming them for display (temporary name, not changing the table, just for the select results). You can alternatively say "SELECT * FROM tableName" to get all columns, but usually it's better to specify so you can say which column comes from which table and rename if it helps. Added a link to a nice Venn Diagram that I think will help. I generally visualize it this way. – shaunhusain May 13 '13 at 04:09
Also if you don't specify INNER vs OUTER, then OUTER is assumed, shown here: http://stackoverflow.com/questions/406294/left-join-and-left-outer-join-in-sql-server – shaunhusain May 13 '13 at 04:15
1

great, I think I got it. I see now that its a trade off like you described. What I ended up with is a single table with a bunch of redundant information. This made my query faster but results in more data being sent. So if I am working on a smaller project where I don't or probably wont have hundreds of entries will it still be faster to use a join and create all this redundant information for each row? I suppose a nested loop would create a lot of back and forth traffic that might slow it down just as much as a larger one time transfer. – Greg G May 13 '13 at 05:57
1

Actually I was being dumb, its late. I was able to clean up the data on the server side before I sent it over the network. (I'm doing this asynchronously). So in the end it saved me a nested loop. Thanks guys! – Greg G May 13 '13 at 07:24
No problemo, glad you ended up understanding the issues for yourself and got everything sorted. – shaunhusain May 14 '13 at 00:20

score 1 · Answer 2 · answered May 13 '13 at 03:38

1

Use a join... it will look something like this

select ws.name as ws_name, part.name as part_name, part.qty as part_quantity from workshop ws, workshop_parts parts where (ws.id = parts.workshop_id);

answered May 13 '13 at 03:38

Orangepill

24,500
3
42
63

I used your answer so will +1 but sort of disagree on the details. **Edit was phrased in valley girl talk, just a mistype, neither from the valley nor a girl** – shaunhusain May 13 '13 at 03:43

score 0 · Answer 3 · answered May 13 '13 at 03:40

SQL will be a good choice since programmatically you will be making calls to the database for each workshop id. Better fetch all the data at once and then display it on the page thereby reducing database calls.

Here is the sql.

select workshop.id, workshop_parts.*
from workshop
left join workshop_parts
on workshop.id=workshop_parts.workshop_id

Hope this helps.

MySQL Join or Nest Loop

3 Answers3