2

I have a problem using multiple joins in one SQL Statement. For example, I have 10 Tables which are tbl1, tbl2, tbl3,..,tbl10. I would like to select some columns from each table.

If I use joins in between, I don't know how to use Left, Right, etc. in the SQL statement from priority aspect. I mean is it organized with parentheses or something else?

Relations for example are like following:

enter image description here

My query is as following:

SELECT T1.col1, T2.col2, T3.col3, T4.col1 AS Expr1
FROM T1  
INNER JOIN T1_T2 ON T1.id = T1_T2.T1_FK 
INNER JOIN T2 ON T1_T2.T2_FK = T2.id 
LEFT JOIN T2_T3 ON T2.id = T2_T3.T2_FK 
INNER JOIN T3 ON T2_T3.T3_FK = T3.id 
LEFT JOIN   T3_T4 ON T3.id = T3_T4.T3_FK 
INNER JOIN T4 ON T3_T4.T4_FK = T4.id

How does SQL Server interpret the joins? Can one represent it like execution priorities with parentheses? I mean: ((T1 INNER Join T1_T2) INNER JOIN T2) LEFT JOIN T2_T3) ?

  • Kindly you share the Primary Key as well as Foreign Key of each table i.e, TB1 to TB10. – Md Haidar Ali Khan Jan 29 '15 at 11:27
  • If the row exists in both tables, then use inner join. If the row does not exists in the table you're adding to the clause, then use left outer join. Right join is the opposite, when it doesn't exist in the previous table you're referring to -- and at least I never use right outer join because you can change it to left outer join by changing the order in the SQL – James Z Jan 29 '15 at 11:56
  • 1
    Why is this tagged mysql? – Aaron Bertrand Jan 29 '15 at 12:54
  • i have edited my post for more explanation. please release the post for answering – user3213767 Jan 30 '15 at 14:32
  • I have edited the post. please release the question. thanks – user3213767 Feb 2 '15 at 8:06
-1
select tbl1.col1,tbl2.col2,tbl3.col3,
tbl4.col4,tbl5.col5,tbl6.col6,tbl7.col7,tbl8.col8,
tbl9.col9,tbl10.col10

from tbl1.col1 as tbc1 

left outer join tbl2.col2 as tbc2 on tbl1.PK=tbl2.FK
left outer join tbl3 as tbc3 on tbl2.PK=tbl3.FK
left outer join tbl4 as tbc4 on tbl3.PK=tbl4.FK
left outer join tbl5 as tbc5 on tbl4.PK=tbl5.FK
left outer join tbl6 as tbc6 on tbl5.PK=tbl6.FK
left outer join tbl7 as tbc7 on tbl6.PK=tbl7.FK
left outer join tbl8 as tbc8 on tbl7.PK=tbl8.FK
left outer join tbl9 as tbc9 on tbl8.PK=tbl9.FK
left outer join tbl10 as tbc10 on tbl9.PK=tbl10.FK

where tbl1.col1='some particular column value'
Group By tbl1.col1
Order tbl1.col1;

I am writing the sql query but it will depend on your table structure . How many there is PK and FK in your each table.

  • Thanks for reply, using your SQL Statement: tbl1 has left join on tbl2, but what about tbl3. SO do you mean like this: (tbl1 LEFT tbl2) LEFT tbl3 ??? and so on ? – user3213767 Jan 29 '15 at 13:34
  • I have already said that, what you want from your query and what are fields in your query it depends on that. It's Very from your Referencial integrity relation of your all tables. – Md Haidar Ali Khan Jan 29 '15 at 13:40
  • i have edited the post for more explanation. please review the question and it will explain more what i need – user3213767 Jan 30 '15 at 10:44
-1

The join order appears unimportant to the query final result given your mapped table equalities.

From MSDN:

The order of table sources after the FROM keyword does not affect the result set that is returned.

That said, you could, if order is very important to your process, or results, consider nested Common Table Expressions

  • It depends on user table PK,FK and their query criteria. – Md Haidar Ali Khan Jan 29 '15 at 12:52
  • 4
    For inner joins, I agree. For outer joins, order certainly does matter. – Aaron Bertrand Jan 29 '15 at 12:53
  • What @Aaron said above ^^^. What MSDN refers to as "table_source" is for tables in FROM, separated by commas only. If there are (outer) joins, order matters. – ypercubeᵀᴹ Jan 29 '15 at 13:28
  • Exactly. OUTER Join order matters .. DOES it mean: (tbl1 LEFT2) LEFT tbl3 ? see the paranthesis ! – user3213767 Jan 29 '15 at 13:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.