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?

The possible duplicate question is related but is far from being an exact duplicate. — Jonathan Leffler, Apr 01 '13 at 01:00

score 4 · Accepted Answer · answered Mar 31 '13 at 15:18

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

"The where clause then is a filter on this result set." That's what I was missing. — Adam R. Grey, Mar 31 '13 at 17:48

score 2 · Answer 2 · answered Mar 31 '13 at 15:17

Whenever you select from multiple tables, you are actually implicitly joining the tables.

SELECT UserId, RoleID 
FROM UserProfile, webpages_Roles;

The above query would return something like:

UserId  UserName  RoleID RoleName
1           Adam  1      user
1           Adam  2      admin

So when you add the where clause:

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

It only returns the rows where the UserProfile.Username is 'Adam' and webpages_roles.RoleName is 'admin'.

score 1 · Answer 3 · answered Mar 31 '13 at 15:36

the reason that this is correct and not an error of any kind is the following. When yo do

SELECT UserId, RoleID
FROM UserProfile, webpages_Roles

without a WHERE clause, what you get in standard SQL is the cartesian product of the relations, ie, UserProfile x webpages_Roles

which is in turn a realtion, in your case (as shown in DiscoInfiltrator's answer),

UserId  UserName  RoleID RoleName
1           Adam  1      user
1           Adam  2      admin

That means that this part of the query WHERE UserProfile.UserName = 'Adam' AND webpages_roles.RoleName = 'admin'; acts on a relation containing both the UserName and the RoleName attributes,

Hope this clarifies, cheers

what join is caused by "and"?

3 Answers3