Can You Have an Inner Join Without the on Keyword

Can you have an INNER JOIN without the ON keyword?

Small universe... I ran across a tool generating this syntax yesterday and was rather flummoxed.

Apparently,

FROM a 
     INNER JOIN b
     INNER JOIN c ON (b.id = c.id)
     ON (a.id = c.id)

is equivalent to a nested subquery

FROM a
     INNER JOIN (SELECT <<list of columns>>
                   FROM b
                        INNER JOIN c ON (b.id=c.id)) c
             ON (a.id = c.id)

How to implement SQL joins without using JOIN?

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.

How to use mysql JOIN without ON condition?

MySQL documentation covers this topic.

Here is a synopsis. When using join or inner join, the on condition is optional. This is different from the ANSI standard and different from almost any other database. The effect is a cross join. Similarly, you can use an on clause with cross join, which also differs from standard SQL.

A cross join creates a Cartesian product -- that is, every possible combination of 1 row from the first table and 1 row from the second. The cross join for a table with three rows ('a', 'b', and 'c') and a table with four rows (say 1, 2, 3, 4) would have 12 rows.

In practice, if you want to do a cross join, then use cross join:

from A cross join B

is much better than:

from A, B

and:

from A join B -- with no on clause

The on clause is required for a right or left outer join, so the discussion is not relevant for them.

INNER JOIN keywords | with and without using them

The second way is the classical way of doing it, from before the join keyword existed.

Normally the query processor generates the same database operations from the two queries, so there would be no difference in performance.

Using join better describes what you are doing in the query. If you have many joins, it's also better because the joined table and it's condition are beside each other, instead of putting all tables in one place and all conditions in another.

Another aspect is that it's easier to do an unbounded join by mistake using the second way, resulting in a cross join containing all combinations from the two tables.

Is there something wrong with joins that don't use the JOIN keyword in SQL or MySQL?

Filtering joins solely using WHERE can be extremely inefficient in some common scenarios. For example:

SELECT * FROM people p, companies c 
    WHERE p.companyID = c.id AND p.firstName = 'Daniel'

Most databases will execute this query quite literally, first taking the Cartesian product of the people and companies tables and then filtering by those which have matching companyID and id fields. While the fully-unconstrained product does not exist anywhere but in memory and then only for a moment, its calculation does take some time.

A better approach is to group the constraints with the JOINs where relevant. This is not only subjectively easier to read but also far more efficient. Thusly:

SELECT * FROM people p JOIN companies c ON p.companyID = c.id
    WHERE p.firstName = 'Daniel'

It's a little longer, but the database is able to look at the ON clause and use it to compute the fully-constrained JOIN directly, rather than starting with everything and then limiting down. This is faster to compute (especially with large data sets and/or many-table joins) and requires less memory.

I change every query I see which uses the "comma JOIN" syntax. In my opinion, the only purpose for its existence is conciseness. Considering the performance impact, I don't think this is a compelling reason.

What is join in MySQL without anything in front?

Just read the documentation from MySQL. It says.

In MySQL, JOIN, CROSS JOIN, and INNER JOIN are syntactic equivalents (they can replace each other). In standard SQL, they are not equivalent. INNER JOIN is used with an ON clause, CROSS JOIN is used otherwise.

To answer your question: join is the same like a inner join

---

Related Topics