Why use joins?

[Luzian]

Beside personal preference, why use [tt]JOIN[/tt] syntax when the same can be acheived by joining columns in the [tt]WHERE[/tt] clause?

CREATE TABLE [Groups]
(
	[id] int NOT NULL IDENTITY PRIMARY KEY,
	[name] varchar(50) NOT NULL UNIQUE
)
CREATE TABLE [Users]
(
	[id] int NOT NULL IDENTITY PRIMARY KEY,
	[name] varchar(50) NOT NULL UNIQUE
)
CREATE TABLE [Users_Groups]
(
	[group_id] int FOREIGN KEY REFERENCES [Groups]([id]),
	[user_id] int FOREIGN KEY REFERENCES [Users]([id])
)
GO

using join syntax

CREATE PROCEDURE [dbo].[pr_GetUsersByGroupName]
(
	@group_name varchar(50)
)
AS
BEGIN
	SELECT u.*
	FROM [Users_Groups] u_g
	JOIN [Groups] g
		ON g.[id] = u_g.[group_id]
	JOIN [Users] u
		ON u.[id] = u_g.[user_id]
	
	WHERE g.[name] = @group_name
END
GO

without

CREATE PROCEDURE [dbo].[pr_GetUsersByGroupName]
(
	@group_name varchar(50)
)
AS
BEGIN
	SELECT u.*
	FROM [Groups] g,
		[Users] u,
		[Users_Groups] u_g
	WHERE g.[name] = @group_name
	AND u_g.[group_id] = g.[id]
	AND u_g.[user_id] = u.[id]
END
GO

 

[LKBrwnDBA]

Where your code says without, you are incorrect. The second is just another way (syntax) of 'join'ing tables.

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[PHV]

For me, using explicit JOINs help the optimizer to find the best query plan (just a guess ...).

 

[r937]

also, you can't write an outer join without OUTER JOIN syntax

well, not in ANSI SQL anyway ;-)

Luzian, you do realize this is the ANSI SQL forum, right? are you using Microsoft SQL Server? because there's a separate forum for that particular variant of the SQL language

r937.com | rudy.ca

 

[Luzian]

Where your code says without, you are incorrect. The second is just another way (syntax) of 'join'ing tables.

"Without" is an extension to the sentence "using join syntax".
So in the second stored procedure I am joining without using [tt]JOIN[/tt] syntax. My argument is why use the [tt]JOIN[/tt] keyword when the 2nd stored procedure without the [tt]JOIN[/tt] keyword will do the same as the first. Am I still incorrect?

 

[Luzian]

Luzian, you do realize this is the ANSI SQL forum, right? are you using Microsoft SQL Server? because there's a separate forum for that particular variant of the SQL language

It's true, I am using Microsoft SQL Server. But for this particular question, JOIN VS. WHERE clause isn't microsoft specific. If there is a microsoft specific difference in the two syntaxes, I don't expect that type of an answer.

 

[Golom]

The joins you are illustrating are INNER joins and, yes, the same effect can be had with a suitable Where clause but consider the following
[tt]
tblA | tblB
ID | ID Value
1 | 1 -1
2 | 3 5
3 |
4 |
[/tt]
Then

Select A.ID, B.Value

From tblA A LEFT OUTER JOIN tblB.B
     ON A.ID = B.ID AND B.Value > 4

Produces
[tt]
ID Value
1 NULL
2 NULL
3 5
4 NULL
[/tt]
but

Select A.ID, B.Value

From tblA A, tblB.B

WHERE A.ID = B.ID AND B.Value > 4

Produces
[tt]
ID Value
3 5
[/tt]

As PHV implied, doing a cross-join followed by a where clause filtering of the resultant "N * M" records is usually much more expensive than using indexes to select records to be included.

Just as an aside, there was early in the history of SQL, a syntax like

Where A.ID [COLOR=red][b]*[/b][/color]= B.ID

that specified a LEFT JOIN in the Where clause. That has been dropped, first to avoid confusion and second because it gave incorrect results in some cases.