How to implement SQL joins without using JOIN?

Question

How does one implement SQL joins without using the JOIN keyword?

This is not really necessary, but I thought that by doing this I could better understand what joins actually do.

Answer 1

The basic INNER JOIN is easy to implement. The following:

SELECT L.XCol, R.YCol
FROM LeftTable AS L
INNER JOIN RightTable AS R
ON L.IDCol=R.IDCol;

is equivalent to:

SELECT L.XCol, R.YCol
FROM LeftTable AS L, RightTable AS R
WHERE L.IDCol=R.IDCol;

In order to extend this to a LEFT/RIGHT/FULL OUTER JOIN, you only need to UNION the rows with no match, along with NULL in the correct columns, to the previous INNER JOIN.

For a LEFT OUTER JOIN, add:

UNION ALL
SELECT L.XCol, NULL /* cast the NULL as needed */
FROM LeftTable AS L
WHERE NOT EXISTS (
    SELECT * FROM RightTable AS R
    WHERE L.IDCol=R.IDCol)

For a RIGHT OUTER JOIN, add:

UNION ALL
SELECT NULL, R.YCol /* cast the NULL as needed */
FROM RightTable AS R
WHERE NOT EXISTS (
    SELECT * FROM LeftTable AS L
    WHERE L.IDCol=R.IDCol)

For a FULL OUTER JOIN, add both of the above.

Answer 2

There is an older deprecated SQL syntax that allows you to join without using the JOIN keyword.. but I personally find it more confusing than any permutation of the JOIN operator I've ever seen. Here's an example:

SELECT A.CustomerName, B.Address1, B.City, B.State, B.Zip
FROM dbo.Customers A, dbo.Addresses B
WHERE A.CustomerId = B.CustomerId

In the older way of doing it, you join by separating the tables with a comma and specifying the JOIN conditions in the WHERE clause. Personally, I would prefer the JOIN syntax:

SELECT A.CustomerName, B.Address1, B.City, B.State, B.Zip
FROM dbo.Customers A
JOIN dbo.Addresses B
ON A.CustomerId = B.CustomerId

The reason you should shy away from this old style of join is clarity and readability. When you are simply joining one table to another, it's pretty easy to figure out what's going on. When you're combining multiple types of joins across a half dozen (or more) tables, this older syntax becomes very challenging to manage.

The best way to get a handle on the JOIN operator is working with it. Here's a decent visual example of what the different JOINs do:

http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/

Some more info:

https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins

http://www.sqlservercentral.com/blogs/brian_kelley/2009/09/30/the-old-inner-join-syntax-vs-the-new-inner-join-syntax/

Answer 3

When SQL was an infant we didn't have "inner join" "left outer join" etc. All we did was list the tables like this:

FROM table1, table2, table3, .... tablen

Then we had a where clause that was like a novel in length, some of the conditions were for filtering the data, many of the conditions were to join tables, like this

FROM table1, table2, table2, .... tablen
WHERE table1.code = 'x' and table1.id = table3.fk and table2.name like 'a%' and table2.id = table1.fk and tablen.fk = table3.id and table2.dt >= '2014-01-01'

from this we hoped like heck we had all the tables nicely related and we crossed our fingers. The worst case scenario - which happened a lot - was that we forgot to include a table at all in the where clause. This was not nice because what we get when we do that is a "Cartesian product" (basically a multiplication of all rows by the number of rows in the table we missed).

Then came ANSI standard join syntax, and life was better. We now place the join conditions on the join - not in the where clause - and as a bonus the where clause is easier to understand.

I don't think you will find it easier to understand this ancient syntax, for example an outer join was join = bizarre(+) or maybe it was (+)bizarre = join (I try not to remember).

Try http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins