How can I find matches in two SQL tables?

Question

I have two tables, UsersSample and UsersSearching. UsersMain is a table of the majority of available users. UsersSearching is a table of any users who have searched on the site.

I am trying to determine if a user from USersSample has searched (and appears in the UsersSearching table). But I'm having difficulty even beginning this SQL query. Can anyone advise and point me in the right direction?

Answer 1

You can use a LEFT OUTER JOIN to determine which users have records in the UserSearching table:

select distinct u.UserID, us.UserID as HasSearchedUserID
from User u
left outer join UserSearching us on u.UserID = us.UserID

If HasSearchedUserID is NULL, then that user has not searched,

Answer 2

SELECT
    us.*
FROM UsersSample us
INNER JOIN UsersSearching uss ON uss.UserId = us.UserId

this will result in a list of users that exist in BOTH tables.

Answer 3

First, you need to define what fields are used to match records in the two tables. For example, if there's a common UserID field in the two tables, your query could use one of these two forms:

SELECT * FROM UsersSample WHERE UserID IN (SELECT UserID FROM UsersSearching)

SELECT UsersSample.* FROM UsersSample INNER JOIN UsersSearching
   ON UsersSample.UserID = UsersSearching.UserID

SELECT * FROM UsersSample WHERE EXISTS 
   (SELECT * FROM UsersSearching WHERE UsersSearching.UserID = UsersSample.UserID)

If you need to use two or more columns to determine matches, you can use modified versions of the second and third options shown above.