Differences between ANSI/ISO Join Syntaxes - All SQL Server Versions

Member of the LearnItFirst.com Video Training Network	\| LearnSqlServer.com \| SQL SSIS Training \| SQL Programming Tutorials \|

Welcome Guest

Search | New Posts | Members | Log In | Register

LearnSqlServer.com's SQL Server Forums » SQL Server Scripts, Code Samples and SSMS Custom Reports » All SQL Server Versions » Differences between ANSI/ISO Join Syntaxes

Differences between ANSI/ISO Join Syntaxes

Options

Previous Topic · Next Topic

Scott Whigham

Posted: Tuesday, November 07, 2006 11:26:35 AM

Rank: Super Mod

Joined: 3/20/2006
Posts: 345
Points: 748
Location: Dallas, TX

Last week I was teaching a SQL query-writing class and had tons of questions regarding the ANSI JOIN syntaxes. I thought I would post a few code samples that highlight the good and bad of using the old-style syntax (ANSI-89/SQL1).

Just in case you haven't seen this syntax before, take a look at the two code samples below. Both return the same results and use the same execution plan. The query returns employees who have placed orders and it only includes those orders who have a matching employee (this is an inner join):

Code:

-- Ansi-89
SELECT LastName, OrderId
FROM Employees e, Orders o
WHERE e.EmployeeId = o.EmployeeId

-- Ansi-92
SELECT LastName, OrderId
FROM Employees e JOIN Orders o
ON e.EmployeeId = o.EmployeeId

For writing two table inner join queries like this with not much else to it, it doesn't matter which syntax you use; SQL Server will choose the same query plan for both.

Now let's change it up: you now want to see all employees and their orders but, if an employee has placed no orders, you still want them listed. This will be an outer join:

Code:

-- Ansi-89
SELECT LastName, OrderId
FROM Employees e, Orders o
[b]WHERE e.EmployeeId *= o.EmployeeId[/b]

-- Ansi-92
SELECT LastName, OrderId
FROM Employees e LEFT JOIN Orders o
ON e.EmployeeId = o.EmployeeId

Not the bold-ed section. The asterisk on the left side of the = says to return all employees even if they do not have matching orders.

Let's switch it again: now we want all orders along with the employee who made the order but we also want orders who have no matching employees. We do NOT want to see employees without matching orders:

Code:

-- Ansi-89
SELECT LastName, OrderId
FROM Employees e, Orders o
[b]WHERE e.EmployeeId =* o.EmployeeId[/b]

-- Ansi-92
SELECT LastName, OrderId
FROM Employees e RIGHT JOIN Orders o
ON e.EmployeeId = o.EmployeeId

Starting to figure this out now?

Last one: we now want all employees and their orders but, if an employee has placed no orders, you still want them listed. We also want any orders that have no matching employees listed as well:

Code:

-- Ansi-89
SELECT LastName, OrderId
FROM Employees e, Orders o
WHERE e.EmployeeId *=* o.EmployeeId

-- Ansi-92
SELECT LastName, OrderId
FROM Employees e FULL JOIN Orders o
ON e.EmployeeId = o.EmployeeId

In this example, the code for the ANSI-89 JOIN will fail - this is not allowed syntax. The ANSI-92 syntax works fine.

There are lots of other reasons to use the new syntax but that's for another post!

Users browsing this topic

Guest

Forum Jump

You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.