SQL left join vs multiple tables on FROM line?

Question

Most SQL dialects accept both the following queries:

SELECT a.foo, b.foo
FROM a, b
WHERE a.x = b.x

SELECT a.foo, b.foo
FROM a
LEFT JOIN b ON a.x = b.x

Now obviously when you need an outer join, the second syntax is required. But when doing an inner join why should I prefer the second syntax to the first (or vice versa)?

Sagacious

@ahnbizcad The two given queries don't do the same thing. The first returns the same as an INNER JOIN ON. Implementation is DBMS version-specific, and even then has few guarantees. But DBMS transformations equivalencing cases of comma vs INNER JOIN ON/WHERE vs CROSS JOIN WHERE is trivial. Learn about relational database query optimization/implementation. – Einkorn

@AlessandroDS Again: Comma means cross join but with lower precedence than keyword JOINs. Whether one is self-joining is irrelevant. – Einkorn

Answer 1

The old syntax, with just listing the tables, and using the WHERE clause to specify the join criteria, is being deprecated in most modern databases.

It's not just for show, the old syntax has the possibility of being ambiguous when you use both INNER and OUTER joins in the same query.

Let me give you an example.

Let's suppose you have 3 tables in your system:

Company
Department
Employee

Each table contain numerous rows, linked together. You got multiple companies, and each company can have multiple departments, and each department can have multiple employees.

Ok, so now you want to do the following:

List all the companies, and include all their departments, and all their employees. Note that some companies don't have any departments yet, but make sure you include them as well. Make sure you only retrieve departments that have employees, but always list all companies.

So you do this:

SELECT * -- for simplicity
FROM Company, Department, Employee
WHERE Company.ID *= Department.CompanyID
  AND Department.ID = Employee.DepartmentID

Note that the last one there is an inner join, in order to fulfill the criteria that you only want departments with people.

Ok, so what happens now. Well, the problem is, it depends on the database engine, the query optimizer, indexes, and table statistics. Let me explain.

If the query optimizer determines that the way to do this is to first take a company, then find the departments, and then do an inner join with employees, you're not going to get any companies that don't have departments.

The reason for this is that the WHERE clause determines which rows end up in the final result, not individual parts of the rows.

And in this case, due to the left join, the Department.ID column will be NULL, and thus when it comes to the INNER JOIN to Employee, there's no way to fulfill that constraint for the Employee row, and so it won't appear.

On the other hand, if the query optimizer decides to tackle the department-employee join first, and then do a left join with the companies, you will see them.

So the old syntax is ambiguous. There's no way to specify what you want, without dealing with query hints, and some databases have no way at all.

Enter the new syntax, with this you can choose.

For instance, if you want all companies, as the problem description stated, this is what you would write:

SELECT *
FROM Company
     LEFT JOIN (
         Department INNER JOIN Employee ON Department.ID = Employee.DepartmentID
     ) ON Company.ID = Department.CompanyID

Here you specify that you want the department-employee join to be done as one join, and then left join the results of that with the companies.

Additionally, let's say you only want departments that contains the letter X in their name. Again, with old style joins, you risk losing the company as well, if it doesn't have any departments with an X in its name, but with the new syntax, you can do this:

SELECT *
FROM Company
     LEFT JOIN (
         Department INNER JOIN Employee ON Department.ID = Employee.DepartmentID
     ) ON Company.ID = Department.CompanyID AND Department.Name LIKE '%X%'

This extra clause is used for the joining, but is not a filter for the entire row. So the row might appear with company information, but might have NULLs in all the department and employee columns for that row, because there is no department with an X in its name for that company. This is hard with the old syntax.

This is why, amongst other vendors, Microsoft has deprecated the old outer join syntax, but not the old inner join syntax, since SQL Server 2005 and upwards. The only way to talk to a database running on Microsoft SQL Server 2005 or 2008, using the old style outer join syntax, is to set that database in 8.0 compatibility mode (aka SQL Server 2000).

Additionally, the old way, by throwing a bunch of tables at the query optimizer, with a bunch of WHERE clauses, was akin to saying "here you are, do the best you can". With the new syntax, the query optimizer has less work to do in order to figure out what parts goes together.

So there you have it.

LEFT and INNER JOIN is the wave of the future.

Hochman

"is being deprecated in most modern databases." --- just curious, which ones? – Halima

forgive me, am not familiar with the *= operator, what does it do? thanks! – Implore

Star= and =Star are (well were) right and left outer joins, or is that left and right? Been deprecated for ages, I haven't used them since SQL Server 6. – Durant

Comma is not deprecated. Never-standard OUTER JOIN syntax *=/=*/*=* is deprecated. – Einkorn

This answer doesn't even answer the question, which is not about outer joins. The one claim it does make about comma vs INNER JOIN ON, re optimization, is wrong. – Einkorn

Sure it does, because the older syntax is deprecated you shouldn't use it. The reason why it is deprecated has to do with outer joins but they deprecated the whole thing in the process. I thought that was important to know. You're welcome to provide your own answer or edit mine if there's something specifically wrong with it. The part about the query optimizer having less to do is taken from an old article on msdn which I don't have the link for. – Hochman

, is not deprecated anywhere. (+), (*) & *=/*=*/=* outer join syntax in various DBMSs were always inadequate and were never ANSI. Optimization does not affect defined behaviour. Subqueries as arguments to joins were added when OUTER JOIN ON was added to ,; given that subqueries were added, OUTER JOIN is just a shorthand for a complex subquery & INNER JOIN ON is shorthand for a simple subquery. The post body does not ask about left join. I don't expect to convince this answer's author. – Einkorn

Confirming what I said--comma is not deprecated & outer joins in comma are but were never standard SQL. But the question isn't about outer join. And it's trivial in optimizing to treat comma, cross & inner joins the same & on & where the same. – Einkorn

Answer 2

The JOIN syntax keeps conditions near the table they apply to. This is especially useful when you join a large amount of tables.

By the way, you can do an outer join with the first syntax too:

WHERE a.x = b.x(+)

Or

WHERE a.x *= b.x

Or

WHERE a.x = b.x or a.x not in (select x from b)

Kotta

The *= syntax is deprecated in MS SQLServer and for good reason: Not only does it make it harder to read, but it doesn't do what people think it does and it is NOT the same as a similarly looking LEFT JOIN. The (+) syntax is unfamiliar to me; what SQL implementation does that? – Niehaus

The other syntax is used by Oracle, at least. – Hochman

Never use the SQL Server syntax *=, it will NOT give consistent results as it will sometimes interpret as a cross join not a left join. This is true even as far back as SQL Server 2000. If you have any code using this, you need to fix. – Bewley

Answer 3

Basically, when your FROM clause lists tables like so:

SELECT * FROM
  tableA, tableB, tableC

the result is a cross product of all the rows in tables A, B, C. Then you apply the restriction WHERE tableA.id = tableB.a_id which will throw away a huge number of rows, then further ... AND tableB.id = tableC.b_id and you should then get only those rows you are really interested in.

DBMSs know how to optimise this SQL so that the performance difference to writing this using JOINs is negligible (if any). Using the JOIN notation makes the SQL statement more readable (IMHO, not using joins turns the statement into a mess). Using the cross product, you need to provide join criteria in the WHERE clause, and that's the problem with the notation. You are crowding your WHERE clause with stuff like

    tableA.id = tableB.a_id 
AND tableB.id = tableC.b_id 

which is only used to restrict the cross product. WHERE clause should only contain RESTRICTIONS to the resultset. If you mix table join criteria with resultset restrictions, you (and others) will find your query harder to read. You should definitely use JOINs and keep the FROM clause a FROM clause, and the WHERE clause a WHERE clause.

Suddenly

Answer 4

The first way is the older standard. The second method was introduced in SQL-92, http://en.wikipedia.org/wiki/SQL. The complete standard can be viewed at http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt .

It took many years before database companies adopted the SQL-92 standard.

So the reason why the second method is preferred, it is the SQL standard according the ANSI and ISO standards committee.

Pasha

, is still standard. on needed to be introduced only for outer join once subselects were also introduced. – Einkorn

Answer 5

The second is preferred because it is far less likely to result in an accidental cross join by forgetting to put inthe where clause. A join with no on clause will fail the syntax check, an old style join with no where clause will not fail, it will do a cross join.

Additionally when you later have to a left join, it is helpful for maintenance that they all be in the same structure. And the old syntax has been out of date since 1992, it is well past time to stop using it.

Plus I have found that many people who exclusively use the first syntax don't really understand joins and understanding joins is critical to getting correct results when querying.

Bewley

Answer 6

I think there are some good reasons on this page to adopt the second method -using explicit JOINs. The clincher though is that when the JOIN criteria are removed from the WHERE clause it becomes much easier to see the remaining selection criteria in the WHERE clause.

In really complex SELECT statements it becomes much easier for a reader to understand what is going on.

Carduaceous

Answer 7

The SELECT * FROM table1, table2, ... syntax is ok for a couple of tables, but it becomes exponentially (not necessarily a mathematically accurate statement) harder and harder to read as the number of tables increases.

The JOIN syntax is harder to write (at the beginning), but it makes it explicit what criteria affects which tables. This makes it much harder to make a mistake.

Also, if all the joins are INNER, then both versions are equivalent. However, the moment you have an OUTER join anywhere in the statement, things get much more complicated and it's virtually guarantee that what you write won't be querying what you think you wrote.

Niehaus

Answer 8

When you need an outer join the second syntax is not always required:

Oracle:

SELECT a.foo, b.foo
  FROM a, b
 WHERE a.x = b.x(+)

MSSQLServer (although it's been deprecated in 2000 version)/Sybase:

SELECT a.foo, b.foo
  FROM a, b
 WHERE a.x *= b.x

But returning to your question. I don't know the answer, but it is probably related to the fact that a join is more natural (syntactically, at least) than adding an expression to a where clause when you are doing exactly that: joining.

Sulfa

Answer 9

I hear a lot of people complain the first one is too difficult to understand and that it is unclear. I don't see a problem with it, but after having that discussion, I use the second one even on INNER JOINS for clarity.

Inn

I was brought up with the habit of not using the JOIN syntax and doing it the first way. I must admit I am still stuck in the habit often times just because I think my brain has been conditioned to follow that logic, wheras the join syntax at times to me seems hard to think in. – Anglian

I was taught that way too. I changed my coding style, because people would look at it and not easily recognize what was going on. Since there is no logical difference and I can find no reason for choosing the former over the latter, I felt that I should adapt to making the code clearer to help others understand what I write. – Inn

Answer 10

To the database, they end up being the same. For you, though, you'll have to use that second syntax in some situations. For the sake of editing queries that end up having to use it (finding out you needed a left join where you had a straight join), and for consistency, I'd pattern only on the 2nd method. It'll make reading queries easier.

Outcaste

Answer 11

Well the first and second queries may yield different results because a LEFT JOIN includes all records from the first table, even if there are no corresponding records in the right table.

Raasch

Answer 12

If both are inner joins there is no difference in the semantics or the execution of the SQL or performance. Both are ANSI Standard SQL It is purely a matter of preference, of coding standards within your work group.

Over the last 25 years, I've developed the habit that if I have a fairly complicated SQL I will use the INNER JOIN syntax because it is easier for the reader to pick out the structure of the query at a glance. It also gives more clarity by singling out the join conditions from the residual conditions, which can save time (and mistakes) if you ever come back to your query months later.

However for outer joins, for the purpose of clarity I would not under any circumstances use non-ansi extensions.

Korwin