PROC SQL - WHY 'ON' INSTEAD OF 'WHERE'?? 1

[Asender]

Hi.

As a relative newbie to SAS programming I'm just getting to grips with some basic sql.

We all know that when we use a LEFT or RIGHT join in sql we must use 'ON' rather than 'WHERE'.

Does anyone know the reason why?

Regards...

 

[jebry]

Hi!

Because Where is already a key word used to define how to choose which records to eliminate or display. On is the key word used to define which record from one dataset is to matched with record(s) from another dataset. Since there are two different functions two different key words were needed.

hth


Jeff Bridgham
bridgham@purdue.edu

 

[teralearner]

This is not the convention for PROC SQL, it is the convetion for SQL. One needs to use ON for all joins: LEFT JOIN, RIGHT JOIN, FULL JOIN, and INNER JOIN. It is a good practice to use INNER JOIN and ON for inner joins, and WHERE for conditions.

 

[Asender]

Ah!

That explains why I have had a bit of trouble understanding the difference.

I was taught to code a basic sql query in this fashion:

Proc sql;
create table BLAH as
select *
from ONE a,
TWO b
where a.polno = b.polno;
quit;

I have now been informed that the ',' represents an implicit INNER JOIN.

 

[Quehay]

Sorry for the late post folks...


SQL 89

select t1.*, t2.*

from

t1,t2
where t1.joinfield=t2.joinfield

SQL 92
select t1.*, t2.*

from

t1 inner join t2
on t1.joinfield=t2.joinfield


It's just different syntax for the join. The problem with outer joins is application support & syntax.

SQL 92
select t1.*, t2.*

from

t1 left join t2
on t1.joinfield=t2.joinfield


ORACLE
select t1.*, t2.*

from

t1,t2
where (+)t1.joinfield=t2.joinfield



Jeff Roberts
Insight Data Consulting
Access and SQL Server Development

http://www.InsightDataConsulting.com

 

[iren]

In Oracle9i,Oracle intruduces strengthened support for ANSI/ISO join syntax. To join the content of two tables together in a single result according to the syntax we must include a join tablename on join_condition in our SQL statement