0

A while ago (around 2004) I worked as a back-end developer on a team where everyone used the where clause joins. As a proponent of using inner/outer joins instead, I showed them an example of a where clause join that illustrated how it could produce ambiguous results depending on data, and how the outer joins were free from that problem.

I have to do the same presentation but cannot find that snippet anymore. It is somewhere in my file archive which I unfortunately do not have access to. Does anyone have an example handy?

5

This can only be demonstrated on databases set to 80 compatibility level (or SQL Server <= 2000).

CREATE TABLE dbo.splunge(splunge_id INT);

CREATE TABLE dbo.mort(splunge_id INT, name VARCHAR(32));

INSERT dbo.splunge(splunge_id) SELECT 1 UNION SELECT 2 UNION SELECT 3;

INSERT dbo.mort(splunge_id, name) SELECT 1,'hi' UNION SELECT 2,NULL;

-- returns all three rows - should it?
SELECT * 
  FROM dbo.splunge AS s, dbo.mort AS m
  WHERE s.splunge_id *= m.splunge_id
  AND m.name IS NULL;

-- returns rows 2 and 3
SELECT * 
  FROM dbo.splunge AS s
  LEFT OUTER JOIN dbo.mort AS m
  ON s.splunge_id = m.splunge_id
  WHERE m.name IS NULL;

DROP TABLE dbo.splunge, dbo.mort;

Don't want to use a nullable name column to demonstrate? Ok, how about a nullable foreign key column?

CREATE TABLE dbo.splunge(splunge_id INT);

CREATE TABLE dbo.mort(splunge_id INT);

INSERT dbo.splunge(splunge_id) SELECT 1 UNION SELECT 2 UNION SELECT 3;

INSERT dbo.mort(splunge_id) SELECT 1 UNION SELECT 2 UNION SELECT NULL;

-- returns all three rows - should it?
SELECT * 
  FROM dbo.splunge AS s, dbo.mort AS m
  WHERE s.splunge_id *= m.splunge_id
  AND m.splunge_id IS NULL;

-- only returns row 3 (because it was the only row truly missing)
SELECT * 
  FROM dbo.splunge AS s
  LEFT OUTER JOIN dbo.mort AS m
  ON s.splunge_id = m.splunge_id
  WHERE m.splunge_id IS NULL;

DROP TABLE dbo.splunge, dbo.mort;

Not sure why you need to demonstrate this today - you just simply shouldn't be using *= / =* joins at all, regardless of whether you can reproduce some ambiguous behavior - they are deprecated and won't work in any database that doesn't support 80 compatibility level. And if you are using compatibility level 80, you need to be prepared for a ton of other functional differences and potential breaking changes when you do finally upgrade.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.