Type of Outer Join
|
Output
|
---|---|
Left
|
All matching rows plus
nonmatching rows from the first table specified in the FROM clause
(the left table)
|
Right
|
All matching rows plus
nonmatching rows from the second table specified in the FROM clause
(the right table)
|
Full
|
All matching rows plus
nonmatching rows in both tables
|
General form, SELECT
statement for outer join:
SELECT column-1<,...column-n>
FROM table-1
| view-1
LEFT
JOIN | RIGHT JOIN | FULL JOIN
table-2
| view-2
ON join-condition(s)
<other
clauses>;
LEFT JOIN, RIGHT JOIN, FULL JOIN
are keywords that specify
the type of outer join.
ON
specifies join-condition(s),
which are expression(s) that specify the column or columns on which
the tables are to be joined.
<other clauses>
refers to optional
PROC SQL clauses.
|
Table
|
Relevant Columns
|
---|---|
Sasuser.Marchflights
|
Date, FlightNumber,
Destination |
Sasuser.Flightdelays
|
Date, FlightNumber,
Destination, Delay |
proc sql outobs=20; title 'All March Flights'; select m.date, m.flightnumber label='Flight Number', m.destination label='Left', f.destination label='Right', delay label='Delay in Minutes' from sasuser.marchflights as m left join sasuser.flightdelays as f on m.date=f.date and m.flightnumber= f.flightnumber order by delay;