SQL Joins vs WHERE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SQL Joins vs WHERE

Author

Topic

microsoftee
Starting Member

6 Posts

Posted - 2008-02-13 : 04:03:27

Out of two, what is the best and efficient method in terms of performance to join the tables in SQL?

1) Using INNER, LEFT OUTER, RIGHT OUTER JOIN between tables
2) Writing comma separated table names and joining them in WHERE clause.

If the JOIN method is preferred, how can I write join query to join more than one table to a single table.

e.g.
If "Employees" table has three foreign keys "FK_DesignationID", "FK_DepartmentID" and "FK_GradeID" from "Designations", "Departments" and "Grades" table respectively.

How can I write query to join them all so, it shows the list of employees with their department name, designation and grade.

Although it can be accomplished by calling a function by passing ID to get all designation, department and grade or any of them but calling function is slow and takes more time and secondly this is only an example there may be more fields required from joining tables instead of only names and it takes more time by calling function for each field.

Please give me some experts opinion that is verified and tested.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-13 : 04:06:44

SELECT t1.Col1,
t2.Col1,
t3.Col1
FROM Table1 AS t1
INNER JOIN Table2 AS t2 ON t2.ColX = t1.ColY
LEFT JOIN Table3 AS t3 ON t3.ColBlue = t2.ColApple

E 12°55'05.25"
N 56°04'39.16"

microsoftee
Starting Member

6 Posts

Posted - 2008-02-13 : 04:31:11

Peso,
In your example query you've joined t3 with t2 but in my situation t3 also required to join with t1.

quote:
Originally posted by Peso

SELECT t1.Col1,
t2.Col1,
t3.Col1
FROM Table1 AS t1
INNER JOIN Table2 AS t2 ON t2.ColX = t1.ColY
LEFT JOIN Table3 AS t3 ON t3.ColBlue = t2.ColApple

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-13 : 04:40:59

[code]SELECT t1.Col1,
t2.Col1,
t3.Col1
FROM Table1 AS t1
INNER JOIN Table2 AS t2 ON t2.ColX = t1.ColY
LEFT JOIN Table3 AS t3 ON t3.ColBlue = t2.ColApple
AND t2.ColGreen = t1.ColZ
WHERE t1.ColW > 3[/code]

E 12°55'05.25"
N 56°04'39.16"

microsoftee
Starting Member

6 Posts

Posted - 2008-02-13 : 04:51:06

Wrong query again.
LEFT JOIN Table3 AS t3 ON t3.ColBlue = t2.ColApple
AND t2.ColGreen = t1.ColZ

t3 and t2 don't have any foreign key of each other (no link), how are you using joining fields "t3.ColBlue = t2.ColApple" and t2, t1 has only one link i.e. "ON t2.ColX = t1.ColY" how are you using "[red]AND t2.ColGreen = t1.ColZ[/red]".

Can you please first read my example requirement carefully.

quote:

Originally posted by Peso

SELECT		t1.Col1,
		t2.Col1,
		t3.Col1
FROM		Table1 AS t1
INNER JOIN	Table2 AS t2 ON t2.ColX = t1.ColY
LEFT JOIN	Table3 AS t3 ON t3.ColBlue = t2.ColApple
			AND t2.ColGreen = t1.ColZ
WHERE		t1.ColW > 3

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-13 : 05:01:22

[code]SELECT e.Columns,d.Columns.g.Columns
FROM Employees e
INNER JOIN Departments d
ON d.DesignationID=e.FK_DesignationID
LEFT JOIN Grades g
ON g.GradeID=e.FK_GradeID[/code]

substitute columns with details you want. If realtion employee to grades is 1->1 you can use INNER JOIN for grade also. it would be a good idea to post your column names also in future to get specific solution.

microsoftee
Starting Member

6 Posts

Posted - 2008-02-13 : 05:15:58

Still did not understand the structure. I'm describing the structure of DB again.

Total 4 tables listed with names and following it, relations between tables with relationship granularity. Hope this time you can understand the structure.

Tables

Employees AS E, Departments AS Dpt, Designation AS De, Grades AS G

Joins b/w Tables

Employees E - Departments Dpt (E.FK_DepartmentID=Dpt.PK_ID) (1-n)
Employees E - Designations De (E.FK_DesignationID=De.PK_ID) (1-n)
Employees E - Grades G (E.FK_GradeID=G.PK_ID) (1-1)

quote:
Originally posted by visakh16

SELECT e.Columns,d.Columns.g.Columns
FROM Employees e
INNER JOIN Departments d
ON d.DesignationID=e.FK_DesignationID
LEFT JOIN Grades g
ON g.GradeID=e.FK_GradeID

substitute columns with details you want. If realtion employee to grades is 1->1 you can use INNER JOIN for grade also. it would be a good idea to post your column names also in future to get specific solution.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-13 : 05:24:50

[code]SELECT emp.*,
des.*,
dep.*,
gra.*
FROM Employees AS emp
INNER JOIN Departments AS dep ON dep.Pk_ID = emp.FK_DepartmentID
INNER JOIN Designations AS des ON des.Pk_ID = emp.FK_DesignationID
INNER JOIN Grades AS gra ON gra.Pk_ID = emp.FK_GradeID[/code]
What about this exercise did you not understand?

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-13 : 05:27:28

[code]SELECT e.Columns,dt.Columns,d.Columns.g.Columns
FROM Employees e
INNER JOIN Departments dt
ON dt.PK_ID=e.FK_DepartmentID
INNER JOIN Designations d
ON d.PK_ID=e.FK_DesignationID
INNER JOIN Grades g
ON g.PK_ID=e.FK_GradeID[/code]

obviuosly you will have multiple records per employee as relations are 1->n

microsoftee
Starting Member

6 Posts

Posted - 2008-02-13 : 07:53:46

Thanks alot. I didn't actually know that a table can be joined with any table instead of only last table in joins sequence.

quote:
Originally posted by visakh16

SELECT e.Columns,dt.Columns,d.Columns.g.Columns
FROM Employees e
INNER JOIN Departments dt
ON dt.PK_ID=e.FK_DepartmentID
INNER JOIN Designations d
ON d.PK_ID=e.FK_DesignationID
INNER JOIN Grades g
ON g.PK_ID=e.FK_GradeID

obviuosly you will have multiple records per employee as relations are 1->n

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-13 : 08:43:30

JOIN order using INNER JOIN make no logically difference. Only to the query engine.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources