Replicate LEFT OUTER JOIN in WHERE clause

Question

How can I replicate a LEFT OUTER JOIN in a WHERE clause? Essentially, I want to use a cross rather than a join in the FROM clause and then filter the second table accordingly:

    SELECT A.KEY, B.KEY
    FROM A, B
    WHERE ?????;

Note the key will not be null in either case and will be unique.

Answer 1

Why not use a UNION?

(SELECT KEY FROM A WHERE ????) 
UNION
(SELECT KEY FROM B WHERE ????) ;

Answer 2

Assuming the column you want to join on is KEY from both tables,

SELECT A.KEY, B.KEY
FROM A, B
WHERE A.KEY *= B.KEY

Answer 3

May not apply, but the mentioned A.KEY *= B.KEY is history in later versions of MS SQL Server, search for "Use of *= and =*"

It wouldn't get past our code reviews here...

Answer 4

David,

My cryptic answer is "You can't." Despite answers given by others, the cross join doesn't contain the information you want in the first place. If you restrict the result set of the cross join to a subset of the cross join (which is what a WHERE clause is for), you won't get what you want. Unless, of course, you use the vile and evil *= operator, which turns the mathematical/relational meaning of a WHERE clause on its head.

Yaraher's suggestion of UNION was reasonable thinking (if bad execution), and in fact, in the SQL standard, OUTER joins are defined in terms of inner joins with unions. So if you object to the word OUTER, you can do this:

SELECT A.KEY, B.KEY
FROM A, B
WHERE A.KEY *= B.KEY
UNION ALL
SELECT A.KEY, NULL
FROM A
WHERE NOT EXISTS (
  SELECT * FROM B AS B2
  WHERE B2.KEY = A.KEY
)

But absent perversions of the language like *=, you can't get more (the outer rows) by asking for less (adding a WHERE clause).

I agree wholeheartedly with gbn, who says *= "will bite you later." Or it will bite somebody and be your fault. It's evil (did I say that yet?), and in fact some more complicated queries containing that operator are simply not well-defined. If the *= operator gets buried in a view somewhere, you're in trouble.

Using *= is irresponsible. It's like using 18-gauge wire to wire a switch for a 20-amp circuit you happen to be using only for a half-amp motor. If there were a SQL code like there is an electrical code, *= wouldn't meet code. (And fortunately, in some shops, like Adam's, it doesn't.)

(You can't even type in into Stackoverflow too close to Italic text without escaping it, it's so bad.)