Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Joining multiple tables in SQL

Can sombody Explains me about joins?

Inner join selects common data based on where condition.

Left outer join selects all data from left irrespective of common but takes common data from right table and vice versa for Right outer.

I know the basics but question stays when it comes to join for than 5, 8, 10 tables.

Suppose I have 10 tables to join. If I have inner join with the first 5 tables and now try to apply a left join with the 6th table, now how the query will work?

I mean to say now the result set of first 5 tables will be taken as left table and the 6th one will be considerded as Right table? Or only Fifth table will be considered as left and 6th as right? Please help me regarding this.

like image 931
Prince Avatar asked Apr 13 '13 11:04

Prince


People also ask

Can you join multiple tables in SQL?

An SQL query can JOIN multiple tables. For each new table an extra JOIN condition is added. Multi-Table JOINs work with SELECT, UPDATE, and DELETE queries.

Can you join 4 tables in SQL?

Using JOIN in SQL doesn't mean you can only join two tables. You can join 3, 4, or even more! The possibilities are limitless.


1 Answers

When joining multiple tables the output of each join logically forms a virtual table that goes into the next join.

So in the example in your question the composite result of joining the first 5 tables would be treated as the left hand table.

See Itzik Ben-Gan's Logical Query Processing Poster for more about this.

The virtual tables involved in the joins can be controlled by positioning the ON clause. For example

SELECT *
FROM   T1
       INNER JOIN T2
         ON T2.C = T1.C
       INNER JOIN T3
                  LEFT JOIN T4
                    ON T4.C = T3.C
         ON T3.C = T2.C 

is equivalent to (T1 Inner Join T2) Inner Join (T3 Left Join T4)

like image 108
Martin Smith Avatar answered Oct 06 '22 19:10

Martin Smith