CODEWITHSUNDEEP
sqlsql-serverjoin
Prince
Prince

Reputation: 328

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.

Upvotes: 21

Views: 119293

Answers (6)

Hanan Imran
Hanan Imran

Reputation: 1

Join used to combine rows from two or more tables, based on a related column between them. This example from Adventure works:

SELECT a.[EmailAddress],b.[FirstName],b.[LastName],c.[PhoneNumber],d.[Name]
FROM [Person].[EmailAddress] a
INNER JOIN [Person].[Person] b
ON a.BusinessEntityID = b.BusinessEntityID 
INNER JOIN [Person].[PersonPhone] c
ON b.BusinessEntityID = c.BusinessEntityID
INNER JOIN [Person].[PhoneNumberType] d
ON c.phoneNumberTypeID = d.phoneNumberTypeID

Upvotes: 0

Shuvashri
Shuvashri

Reputation: 1

Just tried the following from the Example DataBase given in W3School. Worked Fine for me.

   SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate, Products.ProductName, Products.ProductID
    FROM Orders
    INNER JOIN Products
    INNER JOIN Customers
    ON Orders.CustomerID=Customers.CustomerID;

Upvotes: 0

Bha15
Bha15

Reputation: 271

You can apply join like this..

select a.*,b.*,c.*,d.*,e.*  
from [DatabaseName].[Table_a] a 
INNER JOIN [DatabaseName].[Table_b] b ON a.id = b.id 
INNER JOIN [DatabaseName].[Table_c] c ON b.id=c.id 
INNER JOIN [DatabaseName].[Table_d] d on c.id=d.id 
INNER JOIN [DatabaseName].[Table_e] e on d.id=e.id where a.con=5  and 
b.con=6

Here, at place of a.* and in where condition, you can show column(filed) which you like and according condition in where condition. You can insert more table and database as per your choice. But mind that you need to mention database name and alias if you work in different database.

Upvotes: 1

Manu R S
Manu R S

Reputation: 970

> Simple INNER JOIN VIEW code...

CREATE VIEW room_view
AS SELECT a.*,b.*
FROM j4_booking a INNER JOIN j4_scheduling b
on a.room_id = b.room_id;

Upvotes: 1

Colselaw
Colselaw

Reputation: 1079

It's helpful to think of JOIN's in sequence, so the former is correct.

SELECT *
  FROM a
  INNER JOIN b ON b.a = a.id
  INNER JOIN c ON c.b = b.id
  LEFT JOIN d ON d.c = c.id
  LEFT JOIN e ON e.d = d.id

Would be all the fields from a and b and c where all the ON criteria match, plus the values from d where its criteria match plus all the contents of e where all its criteria match.

I know RIGHT JOIN is perfectly acceptable, but I've found in my experience that it's unnecessary - I almost always just join things from left to right.

Upvotes: 13

Martin Smith
Martin Smith

Reputation: 452977

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)

Upvotes: 28

Related Questions