Did that set off a trigger for you? It did for my SAS SQL 1: Essentials class, packed with SQL and SAS programmers alike.  To clarify matters I pulled up some examples to help get the differences quickly.

Set operators and Joins are similar in that they both combine multiple tables or sets to form a result set. That’s where the similarity ends. So, what's the difference?

If I was to try to be very brief, I’d say something like this:

Joins stack columns and align rows.

Joins combine multiple tables into a single row.  In the example below employee demographics are stored in the employee table (EMPSAU) and their home and work phone numbers are stored in the phone table (PHONEHW). If I want to see the entire information for one employee across, then I would code a join like this:

proc sql;
select * from empsau as e, phonehw as p
where e.empid=p.empid;

In the output you’ll notice that an entire column comes from only one of the tables in your Select statement but each row can contain columns from each table.

On to Set operators...Once again in brevity, I’d say:

Set operators stack rows and align columns.

In a set operator’s final result set, the entire row comes from one of the Select statements in your query. You might not be able to tell which one. A single column can contain rows from individual select statements. In my example below, Sales staff data is stored in the SALES table. NonSales staff data is stored in the NONSALES table. If I wanted to see all the data in one table with rows stacked together my code would probably look like this:

proc sql;
select * from sql1.sales
union
select * from sql1.nonsales;

Take a look at the output, specifically at the Job_Title column. You’ll see how the first two rows came from the NONSALES Table. The third and forth rows were contributed from the SALES table.

Jeff Atwood wrote a great blog on this topic.  Learn it First has some good videos by Scott Whigham but you do have to sign up to get some free downloads.

I hope you found the distinction between SQL Joins and SET operators useful. I know I found writing this blog post valuable for my own clarity and I got how one can’t replace the other. Stay tuned for my next blog post where I’ll share my awesome sketches (modestly she says!) from class to hopefully help visualize the difference between the DATA Step and PROC SQL!