6 replaced http://dba.stackexchange.com/ with https://dba.stackexchange.com/
source | link

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.

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.

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.

5 deleted 18 characters in body
source | link

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(1232));

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, name VARCHAR(12));

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.

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(12));

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, name VARCHAR(12));

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.

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.

4 added 85 characters in body
source | link

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(12));

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

INSERT dbo.mort(splunge_id, name) VALUES(SELECT 1,'hi'),( UNION SELECT 2,NULL);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, name VARCHAR(12));

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

INSERT dbo.mort(splunge_id) VALUES(SELECT 1),( UNION SELECT 2),(NULL); 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.namesplunge_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.namesplunge_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.

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(12));

INSERT dbo.splunge(splunge_id) VALUES(1),(2),(3);

INSERT dbo.mort(splunge_id, name) VALUES(1,'hi'),(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, name VARCHAR(12));

INSERT dbo.splunge(splunge_id) VALUES(1),(2),(3);

INSERT dbo.mort(splunge_id) VALUES(1),(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;

-- 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.name 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.

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(12));

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, name VARCHAR(12));

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.

3 added 130 characters in body
source | link
2 added 130 characters in body
source | link
1
source | link