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/