You're not crazy, you can indeed get an equivalent join using a WHERE clause.

But I don't recommend it.

It mixes up your different types of clauses (JOIN vs WHERE), which makes your SQL harder to understand, and worse, you can get unintuitive results.

For example, what would you expect the following to return?

create table people (person_id integer not null primary key, surname v
+archar(40) not null)

insert into people values (1,'Smith')
insert into people values (2,'Jones')
insert into people values (3,'Harry')
insert into people values (4,'Peanut')

create table town (person_id integer not null references people(person
+_id), town varchar(40) null)

insert into town values (1,'Bristol')
insert into town values (2,'Washington')
insert into town values (3,NULL)
insert into town values (4,'Cairns')

    SELECT surname, town 
    FROM   people, town
    WHERE  people.person_id *= town.person_id
    AND    (town <> 'Washington' AND town IS NOT NULL)
[download]

Would you be surprised to get the following results?

surname                        town                                   
+  
------------------------------ ---------------------------------------
+- 
Smith                          Bristol
Jones                          NULL
Harry                          NULL
Peanut                         Cairns
[download]

(Hint: You should be surprised, the SELECT was trying to exclude NULLS)

The source of this ambiguity is that the IS NOT NULL part of the WHERE clause got evaluated before the join part, and the join part then re-introduced the NULLs.

In contrast, the LEFT OUTER JOIN makes the intention clearer (albeit somewhat self-defeating in this example)

    SELECT surname, town 
    FROM   people
    LEFT OUTER JOIN town 
        ON people.person_id = town.person_id
    WHERE (town <> 'Washington' AND town IS NOT NULL)
[download]

Results:

surname                                  town                         
+            
---------------------------------------- -----------------------------
+----------- 
Smith                                    Bristol
Peanut                                   Cairns
[download]

Update: It seems that Oracle gives different results to MS SQL Server 2000, where this code was tested.

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.