5
SELECT title,name FROM Lessons,Users WHERE Lessons.author = Users.email;

and

SELECT title,name FROM Lessons JOIN Users ON Lessons.author = Users.email;

and

SELECT title,name FROM Lessons INNER JOIN Users ON Lessons.author = Users.email;

Lessons has a column named author indexed as a foreign key to a Users.email. title is a column in Lessons and name is a column in Users

Mark
  • 39,169
  • 11
  • 42
  • 48

2 Answers2

12

There is no difference between the three statements, they all are, either implicit or explicit, INNER JOINs

  1. The first statement is using the implicit old join syntax. While this is still supported, using explicit joins is both more readable and maintainable. Don't use old style joins.

  2. The second statement is using an explicit join without specifying the type of join. By default, this is an INNER JOIN

  3. The third statement is also an explicit join and avoids any ambiguities. This is by far the most recommended way of writing joins.

Dan J
  • 16,319
  • 7
  • 50
  • 82
Lieven Keersmaekers
  • 57,207
  • 13
  • 112
  • 146
1

Not only there are perfect idempotent, but there could be another way. If, for instance, you could rename Lessons.author in Lesson.email, in order to make sure that related columns have the same name in the two tables, you could use NATURAL JOIN which implicitly ties together rows from different tables that share same name:

-- After renaming Lessons.author in Lessons.email, you simply use NATURAL JOIN
SELECT title,name FROM Lessons NATURAL JOIN Users;
Dimitri
  • 121
  • 4