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.

Answer 3

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.

Answer 4

INNER JOIN treats two NULLs as two different values. So, if you join based on a nullable column, and if both tables have NULL values in that column, then INNER JOIN will ignore those rows.

Therefore, to correctly retrieve all common rows between two tables, INTERSECT should be used. INTERSECT treats two NULLs as the same value.

Example(SQLite):

Create two tables with nullable columns:

CREATE TABLE Table1 (id INT, firstName TEXT);
CREATE TABLE Table2 (id INT, firstName TEXT);

Insert NULL values:

INSERT INTO Table1 VALUES (1, NULL);
INSERT INTO Table2 VALUES (1, NULL);

Retrieve common rows using INNER JOIN (This shows no output):

SELECT * FROM Table1 INNER JOIN Table2 ON 
    Table1.id=Table2.id AND Table1.firstName=Table2.firstName;

Retrieve common rows using INTERSECT (This correctly shows the common row):

SELECT * FROM Table1 INTERSECT SELECT * FROM Table2;

Conclusion:

Even though, many times both INTERSECT and INNER JOIN can be used to get the same results, they are not the same and should be picked depending on the situation.