In SQL, a Join is actually an Intersection? And it is also a linkage or a "Sideway Union"?

Question

I always thought of a Join in SQL as some kind of linkage between two tables.

For example,

select e.name, d.name from employees e, departments d 
  where employees.deptID = departments.deptID

In this case, it is linking two tables, to show each employee with a department name instead of a department ID. And kind of like a "linkage" or "Union" sideway".

But, after learning about inner join vs outer join, it shows that a Join (Inner join) is actually an intersection.

For example, when one table has the ID 1, 2, 7, 8, while another table has the ID 7 and 8 only, the way we get the intersection is:

select * from t1, t2 where t1.ID = t2.ID

to get the two records of "7 and 8". So it is actually an intersection.

So we have the "Intersection" of 2 tables. Compare this with the "Union" operation on 2 tables. Can a Join be thought of as an "Intersection"? But what about the "linking" or "sideway union" aspect of it?

Answer 1

You're on the right track; the rows returned by an INNER JOIN are those that satisfy the join conditions. But this is like an intersection only because you're using equality in your join condition, applied to columns from each table.

Also be aware that INTERSECTION is already an SQL operation and it has another meaning -- and it's not the same as JOIN.

An SQL JOIN can produce a new type of row, which has all the columns from both joined tables. For example: col4, col5, and col6 don't exist in table A, but they do exist in the result of a join with table B:

SELECT a.col1, a.col2, a.col3, b.col4, b.col5, b.col6
FROM A INNER JOIN B ON a.col2=b.col5;

An SQL INTERSECTION returns rows that are common to two separate tables, which must already have the same columns.

SELECT col1, col2, col3 FROM A
INTERSECT
SELECT col1, col2, col3 FROM B;

This happens to produce the same result as the following join:

SELECT a.col1, a.col2, a.col3
FROM A INNER JOIN B ON a.col1=b.col1 AND a.col2=b.col2 AND a.col3=b.col3;

Not every brand of database supports the INTERSECTION operator.

Answer 2

A join 'links' or erm... joins the rows from two tables. I think that's what you mean by 'sideways union' although I personally think that is a terrible way to phrase it. But there are different types of joins that do slightly different things:

• An inner join is indeed an intersection.
• A full outer join is a union.

This page on Jeff Atwood's blog describes other possibilities.