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

Solution 1:

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.

Solution 2:

The more verbose INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN are from the ANSI SQL/92 syntax for joining. For me, this verbosity makes the join more clear to the developer/DBA of what the intent is with the join.

Solution 3:

In SQL Server there are always query plans to check, a text output can be made as follows:

SET SHOWPLAN_ALL ON
GO

DECLARE @TABLE_A TABLE
(
    ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    Data VARCHAR(10) NOT NULL
)
INSERT INTO @TABLE_A
SELECT 'ABC' UNION 
SELECT 'DEF' UNION
SELECT 'GHI' UNION
SELECT 'JKL' 

DECLARE @TABLE_B TABLE
(
    ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    Data VARCHAR(10) NOT NULL
)
INSERT INTO @TABLE_B
SELECT 'ABC' UNION 
SELECT 'DEF' UNION
SELECT 'GHI' UNION
SELECT 'JKL' 

SELECT A.Data, B.Data
FROM
    @TABLE_A AS A, @TABLE_B AS B
WHERE
    A.ID = B.ID

SELECT A.Data, B.Data
FROM
    @TABLE_A AS A
    INNER JOIN @TABLE_B AS B ON A.ID = B.ID

Now I'll omit the plan for the table variable creates, the plan for both queries is identical though:

 SELECT A.Data, B.Data  FROM   @TABLE_A AS A, @TABLE_B AS B  WHERE   A.ID = B.ID
  |--Nested Loops(Inner Join, OUTER REFERENCES:([A].[ID]))
       |--Clustered Index Scan(OBJECT:(@TABLE_A AS [A]))
       |--Clustered Index Seek(OBJECT:(@TABLE_B AS [B]), SEEK:([B].[ID]=@TABLE_A.[ID] as [A].[ID]) ORDERED FORWARD)
 SELECT A.Data, B.Data  FROM   @TABLE_A AS A   INNER JOIN @TABLE_B AS B ON A.ID = B.ID
  |--Nested Loops(Inner Join, OUTER REFERENCES:([A].[ID]))
       |--Clustered Index Scan(OBJECT:(@TABLE_A AS [A]))
       |--Clustered Index Seek(OBJECT:(@TABLE_B AS [B]), SEEK:([B].[ID]=@TABLE_A.[ID] as [A].[ID]) ORDERED FORWARD)

So, short answer - No need to rewrite, unless you spend a long time trying to read them each time you maintain them?