Sql-server – Looking for a snippet illustrating ambiguous result of a join in a where clause

join;sql server

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?

Best Answer

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.

Related Question