When you need to select data from multiple tables and combine the tables horizontally (side by side), PROC SQL can be an efficient alternative to other SAS procedures or the DATA step. You can use a PROC SQL join to combine tables horizontally:

A PROC SQL join is a query that specifies multiple tables and/or views to be combined and, typically, specifies the conditions on which rows are matched and returned in the result set.

You should already be familiar with the basics of using PROC SQL to join tables. In this chapter, you will take a more in-depth look at joining tables.

In this chapter, you learn to

Before you begin this chapter, you should complete the following chapters:

An inner join combines and displays only the rows from the first table that match rows from the second table, based on the matching criteria (also known as join conditions) that are specified in the WHERE clause. A join condition is an expression that specifies the column(s) on which the tables are to be joined.

The following diagram illustrates an inner join of two tables. The shaded area of overlap represents the matching rows (the subset of rows) that the inner join returns as output.

Inner join syntax builds on the syntax of the simplest type of join that was shown earlier. In an inner join, a WHERE clause is added to restrict the rows of the Cartesian product that will be displayed in output.

When a WHERE clause is added to the PROC SQL query shown earlier, only a subset of rows is included in output. The modified query, tables, and output are shown below:

Because of the WHERE clause, this inner join does not display all rows from the Cartesian product (all possible combinations of rows from both tables) but only a subset of rows. The WHERE clause expression (join condition) specifies that the result set should include only rows whose values of column X in the table One are equal to values in column X of the table Two. Only one row from One and one row from Two have matching values of X. Those two rows are combined into one row of output.

where table1.id = table2.empid

Δ

Consider how PROC SQL processes this inner join.

Understanding how PROC SQL processes inner and outer joins will help you to understand which output is generated by each type of join. Conceptually, PROC SQL follows these steps to process a join:

By default, PROC SQL joins do not overlay columns with the same name. Instead, the output displays all columns that have the same name. To avoid having columns with the same name in the output from an inner or outer join, you can eliminate or rename the duplicate columns.

Consider the sample PROC SQL query that uses an inner join to combine the tables One and Two:

The two tables have a column with an identical name (X). Because the SELECT clause in the query shown above contains an asterisk, the output displays all columns from both tables.

To eliminate a duplicate column, you can specify just one of the duplicate columns in the SELECT statement. The SELECT statement in the preceding PROC SQL query can be modified as follows:

proc sql;
   select one.x, a, b 
      from one, two
      where one.x = two.x

Here, the SELECT clause specifies that only the column X from table One will be included in output. The output, which now displays only one column X, is shown below.

Another way to eliminate the duplicate X column in the preceding example is shown below:

proc sql;
    select one.*, b 
        from one, two
        where one.x = two.x;

By using the asterisk (*) to select all columns from table One, and only B from table Two, this query generates the same output as the preceding version.

If you are working with several tables that have a column with a common name but slightly different data, you might want both columns to appear in output. To avoid the confusion of displaying two different columns with the same name, you can rename one of the duplicate columns by specifying a column alias in the SELECT statement. For example, you could modify the SELECT statement of the sample query as follows:

proc sql;
    select one.x as ID, two.x, a, b 
        from one, two
        where one.x = two.x;

The output of the modified query is shown here.

Now that the column One.X has been renamed to ID, the output clearly indicates that ID and X are two different columns.

Consider what happens when you join two tables in which multiple rows have duplicate values of the column on which the tables are being joined. Each of the tables Three and Four has multiple rows that contain the value 2 for column X. The following PROC SQL inner join matches rows from the two tables based on the common column X:

The output shows how this inner join handles the duplicate values of X.

All possible combinations of the duplicate rows are displayed. There are no matches on any other values of X, so no other rows are displayed in output.

To enable PROC SQL to distinguish between same-named columns from different tables, you use qualified column names. To create a qualified column name, you prefix the column name with its table name. For example, the following PROC SQL inner join contains several qualified column names (shown highlighted):

proc sql;
title 'Employee Names and Job Codes';
    select staffmaster.empid, lastname, firstname, jobcode 
        from sasuser.staffmaster, sasuser.payrollmaster
        where staffmaster.empid=payrollmaster.empid; 

It can be difficult to read PROC SQL code that contains lengthy qualified column names. In addition, typing (and retyping) long table names can be time-consuming. Fortunately, you can use a temporary, alternate name for any or all tables in any PROC SQL query. This temporary name, which is called a table alias, is specified after the table name in the FROM clause. The keyword AS is often used, although its use is optional.

The following modified PROC SQL query specifies table aliases in the FROM clause, and then uses the table aliases to qualify column names in the SELECT and WHERE clauses:

proc sql;
title 'Employee Names and Job Codes';
    select s.empid, lastname, firstname, jobcode 
        from sasuser.staffmaster as s, 
            sasuser.payrollmaster as p 
        where s.empid=p.empid; 

In this query, the optional keyword AS is used to define the table aliases in the FROM clause. The FROM clause would be equally valid with AS omitted, as shown below:

from sasuser.staffmaster s, 
     sasuser.payrollmaster p 

Table aliases are usually optional. However, there are two situations that require their use, as shown below.

from airline.staffmaster as s1, 
    airline.staffmaster as s2 

from airline.flightdelays as af, 
    work.flightdelays as wf 
where af.delay > wf.delay 

So far, you have seen relatively simple examples of inner joins. However, as in any other PROC SQL query, inner joins can include more advanced components, such as

Here are a few examples of more complex inner joins.

Suppose you want to display the names (first initial and last name), job codes, and ages of all company employees who live in New York. You also want the results to be sorted by job code and age.

The data that you need is stored in the two tables listed below.

EmpID, LastName, FirstName, State

EmpID, JobCode, DateOfBirth

Of the three columns that you want to display, JobCode is the only column that already exists in the tables. The other two columns will need to be created from existing columns.

The PROC SQL query shown here uses an inner join to generate the output that you want:

proc sql outobs=15;
title 'New York Employees';
    select substr(firstname,1,1) || '. ' || lastname
        as Name,
        jobcode,
        int((today() - dateofbirth)/365.25)
        as Age
    from sasuser.payrollmaster as p,
        sasuser.staffmaster as s
    where p.empid =
        s.empid
        and state='NY'
order by 2, 3;

The SELECT clause, shown below, specifies the new column Name, the existing column JobCode, and the new column Age:

select substr(firstname,1,1) || '. ' || lastname
    as Name,
    jobcode,
    int((today() - dateofbirth)/365.25)
    as Age

To create the two new columns, the SELECT clause uses functions and expressions as follows:

The WHERE clause contains two expressions linked by the logical operator AND:

where p.empid =
    s.empid
    and state='NY' 

This query only outputs rows that have matching values of EmpID and rows in which the value of State is NY. You do not need to prefix the column name State with a table name, because State occurs in only one of the tables.

You can also summarize and group data in a PROC SQL join. To illustrate, modify the previous PROC SQL inner join so that the output displays the following summarized columns for New York employees in each job code: number of employees and average age. The modified query is shown below:

proc sql outobs=15;
title 'Avg Age of New York Employees';
    select jobcode,
        count(p.empid) as Employees,
        avg(int((today() - dateofbirth)/365.25))
        format=4.1 as AvgAge
    from sasuser.payrollmaster as p,
        sasuser.staffmaster as s
    where p.empid =
        s.empid
        and state='NY'
    group by jobcode
    order by jobcode;

To create two new columns, the SELECT clause uses summary functions as follows:

The output of this modified query is shown below.

An outer join combines and displays all rows that match across tables, based on the specified matching criteria (also known as join conditions), plus some or all of the rows that do not match. You can think of an outer join as an augmentation of an inner join: an outer join returns all rows generated by an inner join, plus additional (nonmatching) rows.

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

The syntax of an outer join is shown below.

Consider how each type of outer join works.

A left outer join retrieves all rows that match across tables, based on the specified matching criteria (join conditions), plus nonmatching rows from the left table (the first table specified in the FROM clause).

Suppose you are using the following PROC SQL left join to combine the two tables One and Two. The join condition is stated in the expression following the ON keyword. The two tables and the three rows of output are shown below:

In each row of output, the first two columns correspond to table One (the left table) and the last two columns correspond to table Two (the right table).

Because this is a left join, all rows (both matching and nonmatching) from table One (the left table) are included in the output (the first two columns). Rows from table Two (the right table) are displayed in the output (the last two columns) only if they match a row from table One.

In this example, the second row of output is the only row in which the row from table One matched a row from table Two, based on the matching criteria (join conditions) specified in the ON clause. In the first and third rows of output, the row from table One had no matching row in table Two.

To eliminate one of the duplicate columns (in this case, X) in any outer join, as shown earlier with an inner join, you can modify the SELECT clause to list the specific columns that will be displayed. Here, the SELECT clause from the preceding query has been modified to remove the duplicate X column:

A right outer join retrieves all rows that match across tables, based on the specified matching criteria (join conditions), plus nonmatching rows from the right table (the second table specified in the FROM clause).

Consider what happens when you use a right join to combine the two tables used in the previous example. The following PROC SQL query uses a right join to combine rows from One and Two, based on the join conditions specified in the ON clause:

In each row of output, the first two columns correspond to table One (the left table) and the last two columns correspond to table Two (the right table).

Because this is a right join, all rows (both matching and nonmatching) from table Two (the right table) are included in the output (the last two columns). Rows from table One (the left table) are displayed in the output (the first two columns) only if they match a row from table Two.

In this example, there is only one row in table One that matches a value of X in table Two, and these two matching rows combine to form the first row of output. In the remaining rows of output, there is no match and the columns corresponding to table One are set to missing values.

A full outer join retrieves both matching rows and nonmatching rows from both tables.

Combine the same two tables again, this time using a full join. The PROC SQL query, the tables, and the output are shown below:

Because this is a full join, all rows (both matching and nonmatching) from both tables are included in the output. There is only one match between table One and table Two, so only one row of output displays values in all columns. All remaining rows of output contain only values from table One or table Two, with the remaining columns set to missing values.

Now that you have seen how the three types of outer joins work, consider a realistic situation requiring the use of an outer join.

Suppose you want to list all of an airline's flights that were scheduled for March, along with corresponding delay information (if it exists). Each flight is identified by both a flight date and a flight number. Your output should display the following data: flight date, flight number, destination, and length of delay in minutes.

The data that you need is stored in the two tables shown below. The applicable columns from each table are identified.

Date, FlightNumber, Destination

Date, FlightNumber, Destination, Delay

Your output should include the columns that are listed above and all of the following rows:

To generate the output that you want, the following PROC SQL query uses a left outer join, with Sasuser.Marchflights specified as the left (first) table.

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;

Notice the following:

The query output is shown below.

The first 12 rows of output display rows from Sasuser.Marchflights (the left table) that have no matching rows in Sasuser.Flightdelays. Therefore, in these 12 rows, the last 2 columns are set to missing values.

When all of the values of the BY variable match, you can use a PROC SQL inner join to produce the same results as a DATA step match-merge.

Suppose you want to combine the tables One and Two, as shown below.

These two tables have the column X in common, and all values of X in each row match across the two tables. Both tables are already sorted by X.

The following DATA step match-merge (followed by a PROC PRINT step) and the PROC SQL inner join produce identical reports.

Step Match-Merge	DATA PROC SQL Inner Join

When only some of the values of the BY variable match, you can use a PROC SQL full outer join to produce the same result as a DATA step match-merge. Unlike the DATA step match-merge, however, a PROC SQL outer join does not overlay the two common columns by default. To overlay common columns, you must use the COALESCE function in the PROC SQL full outer join.

Consider what happens when you use a PROC SQL full outer join without the COALESCE function. Suppose you want to combine the tables Three and Four. These two tables have the column X in common, but most of the values of X do not match across tables. Both tables are already sorted by X. The following DATA step match-merge (followed by a PROC PRINT step) and the PROC SQL outer join combine these tables, but do not generate the same output.e COALESCE function can also be used with inner join operators.

DATA Step Match-Merge	PROC SQL Full Outer Join

The DATA step match-merge automatically overlays the common column, X. The PROC SQL outer join selects the value of X from just one of the tables, table Three,so that no X values from table Four are included in the PROC SQL output. However, the PROC SQL outer join cannot overlay the columns by default. The values that vary across the two merged tables are in bold above.

Consider how the COALESCE function is used in the PROC SQL outer join to overlay the common columns.

When you add the COALESCE function to the SELECT clause of the PROC SQL outer join, the PROC SQL outer join can produce the same result as a DATA step match-merge.

The COALESCE function overlays the specified columns by

Below is the SELECT clause from the PROC SQL full outer join shown in the previous section, with the COALESCE function added:

select coalesce(three.x, four.x) 
           as X, a, b

This SELECT clause overlays the X columns from tables Three and Four. The column alias X is assigned to the coalesced column.

When the COALESCE function is added to the preceding PROC SQL full outer join, the DATA step match-merge (with PROC PRINT step) and the PROC SQL full outer join will combine rows in the same way. The two programs, the tables, and the output are shown below.

DATA Step Match-Merge	PROC SQL Full Outer Join

DATA step match-merges and PROC SQL joins both have advantages and disadvantages. Here are some of the main advantages of PROC SQL joins.

Advantage	Example
PROC SQL joins do not require sorted or indexed tables.
PROC SQL joins do not require that the columns in join expressions have the same name.
PROC SQL joins can use comparison operators other than the equal sign (=).

You can learn more about the comparative advantages and disadvantages of DATA step match-merges and PROC SQL joins in Chapter 15, "Combining Data Horizontally," on page 511.

Sometimes, you might want to specify an in-line view rather than a table as the source of data for a PROC SQL query. An in-line view is a nested query that is specified in the outer query's FROM clause. (You should already be familiar with a subquery, which is a nested query that is specified in a WHERE clause.) An in-line view selects data from one or more tables in order to produce a temporary (or virtual) table that the outer query then uses to select data for output.

For example, the following FROM clause specifies an in-line view:

from (select flightnumber, date, 
        boarded/passengercapacity*100 
        as pctfull 
        format=4.1 label='Percent Full' 
    from sasuser.marchflights) 

This in-line view selects two existing columns ( FlightNumber and Date) and defines the new column PctFull based on the table Sasuser.Marchflights.

Unlike a table, an in-line view exists only during query execution. Because it is temporary, an in-line view can be referenced only in the query in which it is defined. In addition, an in-line view can be assigned an alias but it cannot be assigned a permanent name.

There are two potential advantages to using an in-line view instead of a table in a PROC SQL query:

The preceding FROM clause is from a simple PROC SQL query that references just one data source: the in-line view. However, a PROC SQL query can join multiple tables and in-line views. For example, the FROM clause shown below specifies both a table (Sasuser.Flightschedule) and an in-line view.

from sasuser.flightschedule, 
    (select flightnumber, date, 
        boarded/passengercapacity*100 
        as pctfull 
        format=4.1 label='Percent Full' 
    from sasuser.marchflights) 

You can specify more than one table in the FROM clause of an in-line view, as shown in the following example:

from (select marchflights.flightnumber,
        marchflights.date,
        boarded/passengercapacity*100
        as pctfull
        format=4.1 label='Percent Full',
        delay
    from sasuser.marchflights, 
        sasuser.flightdelays 
where marchflights.flightnumber=
    flightdelays.flightnumber
    and marchflights.date=
    flightdelays.date)

In other words, you can base an in-line view on a join.

You can assign an alias to an in-line view just as you can to a table. In the following example, the alias f has been added in the first FROM clause to reference the table Sasuser.Flightschedule and the alias m has been added in the second FROM clause to reference the in-line view. After the main FROM clause, a WHERE clause that uses both of the aliases has been added.

from sasuser.flightschedule as f,
    (select flightnumber, date
            boarded/passengercapacity*100
            as pctfull
            format=4.1 label='Percent Full'
        from sasuser.marchflights) as m 
where m.flightnumber=f.flightnumber
    and m.date=f.date

Suppose you want to identify the air travel destinations that experienced the worst delays in March. You would like your output to show all of the following data:

Your PROC SQL query uses an in-line view (shown highlighted) to calculate all of the new columns except for the last one:

proc sql;
title "Flight Destinations and Delays";
   select destination,
          average format=3.0 label='Average Delay',
          max format=3.0 label='Maximum Delay',
          late/(late+early) as prob format=5.2
          label='Probability of Delay'
     from  (select destination, 
                   avg(delay) as average, 
                   max(delay) as max, 
                   sum(delay > 0) as late, 
                   sum(delay <= 0) as early 
                   from sasuser.flightdelays 
                   group by destination) 
      order by average;

Consider each clause of the outer query, starting with the FROM clause, because PROC SQL evaluates the FROM clause before the SELECT clause.

The FROM clause specifies an in-line view rather than a table. The in-line view (nested query) specifies the following columns that are in the table Sasuser.Flightdelays or are based on a column in that table:

The columns Average, Max, Late, and Early are all calculated by using summary functions.

In the calculation for the columns Late and Early, a Boolean expression is used as the argument for the summary function. A Boolean function resolves either to 1 (true) or 0 (false). For example, Late is calculated by taking the sum of the Boolean expression delay > 0. For every value of Delay that is greater than 0, the Boolean expression resolves to 1; values that are equal to or less than 0 resolve to 0. The SUM function adds all values of Delay to indicate the number of delays that occurred for each destination.

The in-line view concludes with the clause group by destination, specifying that the in-line view data should be grouped and summarized by the values of Destination.

If you submitted this in-line view (nested query) as a separate query, it would generate the following output.

Consider the outer query's SELECT and ORDER BY clauses (shown highlighted):

proc sql;
title "Flight Destinations and Delays";
    select destination, 
        average format=3.0 label='Average Delay', 
        max format=3.0 label='Maximum Delay', 
        late/(late+early) as prob format=5.2 
        label='Probability of Delay' 
    from (select destination,
        avg(delay) as average,
        max(delay) as max,
        sum(delay > 0) as late,
        sum(delay <= 0) as early
        from sasuser.flightdelays
        group by destination) 
    order by average; 

The outer query's SELECT clause specifies columns as follows:

The outer query's last clause is an ORDER BY clause. The output will be sorted by the values of Average.

This PROC SQL query generates the following output.

Later in this chapter, you will see a PROC SQL query that combines multiple tables and uses an in-line view.

Suppose you want to list the names of supervisors for the crew on the flight to Copenhagen on March 4, 2000. To solve this problem, you will need to use the following four tables.

EmpID, Date, Destination

EmpID, FirstName, LastName, State

EmpID, JobCode

EmpID, State, JobCategory

This problem can be handled in a number of different ways. Examine and compare three different techniques:

Sasuser.Flightschedule ( EmpID, Date, Destination)
Sasuser.Staffmaster ( EmpID, FirstName, LastName, State)
Sasuser.Payrollmaster ( EmpID, JobCode)
Sasuser.Supervisors ( EmpID, State, JobCategory)

Completing the stated task requires a complex query that includes several subqueries, joins, and an in-line view. To make the task more manageable, build the complex query piece-by-piece in four steps:

Note that at each of the four steps, a new piece of the final query will be added. The final query will include the four separate pieces.

Query 1: Identify the crew for the Copenhagen (CPH) flight

This query lists the employee ID numbers of all six crew members on the Copenhagen flight:

Query 2: Find the states and job categories of the crew members

Query 1 becomes a subquery and returns the employee ID numbers of the six Copenhagen crew members to the outer query, Query 2. (Query 2 is shaded.) Query 2 uses an inner join to combine two tables. Query 2 selects the job category (by using the SUBSTR function to extract the job category from JobCode) and state for each of the six crew members.

Query 3: Find the employee numbers of the crew supervisors

Query 2 becomes an in-line view within Query 3, and the alias c has been assigned to the in-line view. (Query 3 is shown in bold.) Query 2 returns to Query 3 the job category and state for each crew member. Query 3 selects the employee ID numbers for supervisors whose job category and state match the job category and state of a crew member.

Note

Sasuser.Supervisors specifies the label Supervisor ID for the EmpID column, and this label appears in the output.

Δ

Note that two rows contain the same value of EmpID: 1983. This duplication indicates that two different crew members have the same manager. In all, there are five supervisors for the six crew members of the Copenhagen flight.

Query 4: Find the names of the supervisors

Query 3 becomes a subquery within Query 4. (Query 4 is shown highlighted.) Query 3 returns to Query 4 the employee numbers (supervisor IDs) for the supervisors of the Copenhagen crew. Query 4 selects the names of the supervisors.

Note that the output has five rows, one for each supervisor. The duplicate name of a supervisor has been eliminated.

Technique 1 produces a PROC SQL query that includes

• four SELECT statements

• four tables, each read separately.

This program is not optimized and, in addition, includes complex code that is likely to take a long time to write.

Sasuser.Flightschedule ( EmpID, Date, Destination)
Sasuser.Staffmaster ( EmpID, FirstName, LastName, State)
Sasuser.Payrollmaster ( EmpID, JobCode)
Sasuser.Supervisors ( EmpID, State, JobCategory)

You can also solve this problem by using a multi-way join with a reflexive join (joining a table to itself). The code is shown below:

Technique 2, which uses a multi-way join, provides a more efficient solution to the problem than Technique 1. In a multi-way join, PROC SQL joins two tables at a time and performs the joins in the most efficient order (the order that will minimize the size of the Cartesian product). This multi-way join code is more difficult to build step-by-step than the code in Technique 1.

Note that Sasuser.Staffmaster is read two separate times in this query: this is the reflexive join. As you can see in the FROM clause, Sasuser.Staffmaster is assigned a different table alias each time it is read: first b, then e. The table is read the first time (alias b) to look up the states of the Copenhagen crew members, the second time (alias e) to look up the names of the supervisors.

Sasuser.Flightschedule ( EmpID, Date, Destination)
Sasuser.Staffmaster ( EmpID, FirstName, LastName, State)
Sasuser.Payrollmaster ( EmpID, JobCode)
Sasuser.Supervisors ( EmpID, State, JobCategory)

For comparison, look at the traditional SAS programming that can be used to solve this problem. The code is shown below, followed by the output.

/* Find the crew for the flight. */

proc sort data=sasuser.flightschedule (drop=flightnumber)
            out=crew (keep=empid);
        where destination='CPH' and date='04MAR2000'd;
        by empid;
run;

/* Find the State and job code for the crew. */

proc sort data=sasuser.payrollmaster
            (keep=empid jobcode)
        out=payroll;
    by empid;
run;

proc sort data=sasuser.staffmaster
            (keep=empid state firstname lastname)
        out=staff;
    by empid;
run;

data st_cat (keep=state jobcategory);
    merge crew (in=c)
        staff
        payroll;
    by empid;
    if c;
    jobcategory=substr(jobcode,1,2);
run;

/* Find the supervisor IDs. */

proc sort
    data=st_cat;
        by jobcategory state;

run;

proc sort data=sasuser.supervisors
            out=superv;
    by jobcategory state;
run;

data super (keep=empid);
    merge st_cat(in=s)
        superv;
    by jobcategory state;
    if s;
run;

/* Find the names of the supervisors. */

proc sort data=super;
    by empid;
run;

data names(drop=empid);
    merge super (in=super)
        staff (keep=empid firstname lastname);
    by empid;
    if super;
run;

proc print data=names noobs uniform;
run;

This output is not identical to the output of the PROC SQL approaches (Techniques 1 and 2). The SQL queries eliminated the duplicate names that are seen here. When you use Technique 3, you can eliminate duplicates by adding the NODUPKEY option to the last PROC SORT statement, as shown below:

proc sort data=super nodupkey; 

Based on a mainframe benchmark in batch mode, the SQL queries use less CPU time, but more I/O operations, than this non-SQL program.

PROC SQL;

  SELECT column-1<,...column-n>

     FROM table-1 | view-1, table-2 | view-2<,...table-n | view-n>

     WHERE join-condition(s)

      <AND other subsetting condition(s)>

          <other clauses>;

  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>;

  SELECT column-1<,...column-n>

     FROM table-1 | view-1

         INNER JOIN

         table-2 | view-2

         ON join-condition(s)

     other clauses>;

  SELECT COALESCE (column-1<,...column-n>)

       <AS> column-alias><,...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>;

QUIT;