In SQL, a Join is actually an Intersection? And it is also a linkage or a "Sideway Union"?
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.col6FROM 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 AINTERSECTSELECT col1, col2, col3 FROM B;
This happens to produce the same result as the following join:
SELECT a.col1, a.col2, a.col3FROM 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.
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.
An Outer Join - is not related to - Union or Union All.
For example, a 'null' would not occur as a result of Union or Union All operation, but it results from an Outer Join.