0

After reading the question title you may find it silly but I'm seriously asking this question with curiosity in my mind.

I'm using MySQL database system.

Consider below the two tables :

Customers(CustomerID(Primary Key), CustomerName, ContactName, Address, City, PostalCode, Country)

Orders(OrderID(Primary Key), CustomerID(Foreign Key), EmployeeID, OrderDate, ShipperID)

Now I want to get the details of all orders that is which order is placed by which customer?

So, I did it in two ways :

First way: 

SELECT o.OrderID, o.OrderDate, c.CustomerName 
FROM Customers AS c, Orders AS o 
WHERE c.CustomerID=o.CustomerID;

Second way:

SELECT Orders.OrderID, Orders.OrderDate, Customers.CustomerName 
FROM Orders 
INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;

In both the cases I'm getting exactly the same correct result. My question is why there is a necessary of additional and confusing concept of Inner Join in MySQL as we can achieve the same results even without using Inner Join?

Is the Inner Join more effective in any manner?

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
  • 3
    Explicit `JOIN` syntax has been the correct SQL syntax for over two decades. *Never* use commas in the `FROM` clause. – Gordon Linoff Dec 13 '17 at 04:12
  • @GordonLinoff : But, it didn't give me any syntax or deprecated error when I didn't use JOIN. Isn't it a valid and legal way anymore as of now and I must have to use JOIN for queries that involve more than one tables? –  Dec 13 '17 at 04:15
  • From my experience, yes, inner joins can be avoided just like you did (although the engine will optimize it and produce the same execution plan anyway), but I don't see any benefit. Actually, when you have long queries full of conditions, it's much better to have the join conditions in a separate place, right next to the joined table, than at the end with the rest of the filtering. I don't think you could avoid outer joins though, so why use two different approaches for joining? – Andrew Dec 13 '17 at 04:21
  • `Implicit` join i.e using`,` and `explicit` join i.e. `[INNER] JOIN` is conceptually the same thing, however explicit join is recommended. Result will be the same in both cases. BTW why do you want to avoid the word `JOIN` :-P – zarruq Dec 13 '17 at 04:22
  • @zarruq : Because I find is much more difficult to understand. Also, I didn't get any useful resource specifically regarding JOINS in MySQL database system. –  Dec 13 '17 at 04:30
  • 1
    There are lots and lots of materials on [**JOIN**](https://stackoverflow.com/questions/17946221/sql-join-and-different-types-of-joins) on SO as well as on other forums. – zarruq Dec 13 '17 at 04:36
  • 2
    @GordonLinoff 2017 is almost over. Now you can say "for over a quarter century" :) – Joel Coehoorn Dec 13 '17 at 04:43

2 Answers2

0

What you are looking at is ANSI-89 syntax (A,B WHERE) vs ANSI-92 syntax (A JOIN B ON).

For very simple queries, there is no difference. However, there are a number of things you can do with ANSI-92 that you cannot do or that become very difficult to implement and maintain in ANSI-89. Anything more than two tables involved, more than one condition in the same join, or separating LEFT JOIN conditions from WHERE conditions are all much harder to read and work with in the older syntax.

The old A,B WHERE syntax is generally considered obsolete and avoided, even for the simple queries where it still works.

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
0

The trade-offs of hardware optimization are second to none to users being able to maintain their queries.

Having explicit clean code is better than having esoteric implicit code. In actual production relational databases, most of the queries that take too long come from the ones where the tables are in a concatenated list. These queries show that:

  1. User did not put the effort on expressing the order these tables are joined.
  2. All the relationship joins are cluttered in one place instead organized on its own space for each join.
  3. If all queries are in such format for said user, user does not take advantage of Outer Joins. There are many cases where a relationship between tables can be: (1) TO (0-many) OR (many) TO (many) instead of (1) TO (1-many).

As in most use cases, these queries become to start to be a problem when the number of joins increase. Beginner users choose to query the tables by placing them as a list delimited with a comma because it takes less to type. At first, it does not seem to be a problem because they are joined against two to three tables. This in turn become a habit to the beginner user. As they start to write more complicated queries by increasing their number of joins, those type of queries are harder to maintain as described from the above bullet points.

Conclusion: As the number of joins within a query scales, improper indentation and categorization make the query harder to maintain.

You should use INNER JOIN and ident your query as below so it is easy for others to read:

SELECT 
      Orders.OrderID, 
      Orders.OrderDate, 
      Customers.CustomerName 

   FROM Orders

   INNER JOIN Customers 
   ON Customers.CustomerID = Orders.CustomerID;
softdevlife
  • 98
  • 9