Is listing tables in FROM clause essentially the same as an INNER JOIN?

Question

Say I have these two tables:

User
===============
  UserID
  Username
  Job

UserInfo
===============
  UserID
  UserInfo1
  UserInfo2

are the following two sql statements the same (on Oracle 11g)?

SELECT * FROM User, UserInfo
WHERE UserInfo.UserID = User.UserID

and

SELECT * FROM User
INNER JOIN UserInfo ON UserInfo.UserID = User.UserID

Please explain/elaborate on the FROM [table list] syntax. I do not understand what's going on there... If there are multiple UserInfos, will it return all of them? or just the first one that matches for each User? what about if the User doesnt have any UserInfo, will they be omitted from the results?

Yes both are same, first one is implicit join and other one is explicit — rs., Apr 18 '13 at 23:41
Yes, it is a duplicate. I had trouble searching before I learned the correct terms. :) — solidau, Apr 18 '13 at 23:48

score 1 · Answer 1 · answered Apr 18 '13 at 23:45

Both of those queries are doing in INNER JOIN which will return all matching rows between both tables.

If your user table contains a single UserId but the UserInfo table contains, 4 rows with the same userId, then you will return 4 rows. Since you have multiple rows that match you return all matching rows, not the first one.

If you want to return the Users that do not have a matching row in the UserInfo table, then you will have to use a LEFT JOIN:

SELECT * 
FROM User
LEFT JOIN UserInfo 
  ON UserInfo.UserID = User.UserID

The LEFT JOIN will return the rows that match in the UserInfo table as well as the unmatched rows. The unmatched rows, will return a null for the UserInfo values.

Is listing tables in FROM clause essentially the same as an INNER JOIN?

1 Answers1