what join is caused by "and"?

Question

I have a localDB database that includes the following tables:

UserProfile, which contains:

UserId  UserName
1           Adam

webpages_Roles, which contains:

RoleID RoleName
1      user
2      admin

webpages_UsersInRoles, which has two columns (UserId, RoleId), and is blank.

I want a query to add a user by name into a role by name. to figure out what to insert, if I run:

SELECT UserId, RoleID 
FROM UserProfile, webpages_Roles 
WHERE UserProfile.UserName = 'Adam' 
   OR webpages_roles.RoleName = 'admin';

I get this:

UserId  RoleId
1       2
1       1

which makes sense; it's a cross join. But I basically just want to run two separate selects at once and stick them together. I tried this:

SELECT UserId, RoleID 
FROM UserProfile, webpages_Roles 
WHERE UserProfile.UserName = 'Adam' 
  AND webpages_roles.RoleName = 'admin';

and it worked; I got UserId 1 and RoleId 2. but I don't understand that "and"; isn't it like comparing apples to oranges? shouldn't it be a syntax error? Is localDB actually running two selects and joining the results, perhaps by line number?

Answer 1

When you do this query:

SELECT UserId, RoleID 
FROM UserProfile, webpages_Roles
WHERE UserProfile.UserName = 'Adam' AND webpages_roles.RoleName = 'admin';

You are doing a cross join. You should get in the habit of being specific about the cross join, and use cross join rather than ,. In any case, this is equivalent to:

SELECT UserId, RoleID
FROM (select u.*
      from UserProfile u
      where u.UserName = 'Adam'
     ) cross join
     (select w.*
      from webpages_Roles w
      WHERE w.RoleName = 'admin'
     ) w

When doing a cross join you are creating a cartesian product -- each row from the first table is matched to all the rows in the other table. The where clause then is a filter on this result set.

If you want to align data, that is harder. Here is one method using SQL Server syntax:

SELECT UserId, RoleID
FROM (select u.*, row_number() over (order by (select NULL)) as seqnum
      from UserProfile u
      where u.UserName = 'Adam'
     ) u join
     (select w.*, row_number() over (order by (select NULL)) as seqnum
      from webpages_Roles w
      WHERE w.RoleName = 'admin'
     ) w
     on u.seqnum = w.seqnum