Type of Outer Join
|
Output
|
---|---|
Left
|
Returns all rows from
the left table (first table) and matching rows from the right table
(second table) that are specified in the FROM clause.
|
Right
|
Returns all rows from
the right table (second table) and matching rows from the left table
(first table).
|
Full
|
Returns all matching
and nonmatching rows from all the tables.
|
Syntax, 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 table1.column = table2.column
<other
clauses>;
LEFT JOIN, RIGHT JOIN, FULL JOIN
are keywords that specify
the type of outer join.
table
specifies the name
of the source table.
ON
specifies join conditions,
which are expressions that specify the column or columns on which
the tables are to be joined.
table.column
refers to the source
table and the column name on which the join occurs.
<other clauses>
refers to optional
PROC SQL clauses.
|
proc sql; select * from certadv.one left join certadv.two on one.x=two.x ; quit;
proc sql;
select one.x, a, b
from certadv.one left join
certadv.two
on one.x=two.x
;
quit;
proc sql; select * from certadv.one right join certadv.two on one.x=two.x ; quit;
proc sql; select * from certadv.one full join certadv.two on one.x=two.x ; quit;
Table
|
Relevant Columns
|
---|---|
Certadv.Marchflights
|
Date, FlightNumber,
Destination
|
Certadv.Flightdelays
|
Date, FlightNumber,
Destination, Delay
|
proc sql outobs=20; title 'All March Flights'; select m.date, /*1*/ m.flightnumber label='Flight Number', m.destination label='Left', f.destination label='Right', delay label='Delay in Minutes' from certadv.marchflights as m left join /*2*/ certadv.flightdelays as f on m.date=f.date /*3*/ and m.flightnumber=f.flightnumber order by delay; /*4*/ quit;
1 | The SELECT clause eliminates the duplicate Date and FlightNumber columns by specifying their source as Certadv.Marchflights. However, the SELECT clause list specifies the Destination columns from both tables and assigns a table alias to each to distinguish between them. |
2 | The FROM clause lists the tables to select from. The FROM clause uses the AS keyword to distinguish a table alias from other table names. The FROM clause also specifies the LEFT JOIN keyword to specify the join type. |
3 | The ON clause contains two join conditions, which match the tables on the two columns Date and FlightNumber. |
4 | The ORDER BY clause specifies the order in which rows are displayed in the result table. |