Using ON clause to JOIN tables with same column name

Question

I wanted to ask about the condition of an ON clause while joining tables:

SELECT c_User.ID
FROM c_User
WHERE EXISTS (
    SELECT *
    FROM c_Group 
    JOIN c_Member ON (c_Group.Group_Name LIKE 'mcp%')
    WHERE 
        c_Group.Name = c_Member.Parent_Name
        AND c_Member.Child_Name = c_User.Lower_User_Name
)

I know that tables c_Member and c_Group have one column with the same name, Directory_ID. What I expected was c_Member and c_Group to join on that column using something like:

c_Group JOIN c_Member ON (c_Group.Directory_ID = c_Member.Directory_ID)
WHERE c_Group.Group_Name like 'mcp%'

How is this condition able to match the rows?

c_Member ON (c_Group.Group_Name LIKE 'mcp%')

Is this is a shorter way of referring to two tables joining on a column with the same name, while applying the LIKE condition?
If so, then can such a style work for a table with multiple column names that are the same?

score 1 · Accepted Answer · answered Apr 04 '19 at 23:10

This is your correlated subquery:

SELECT *
FROM c_Group 
JOIN c_Member ON (c_Group.Group_Name LIKE 'mcp%')
WHERE 
    c_Group.Name = c_Member.Parent_Name
    AND c_Member.Child_Name = c_User.Lower_User_Name

This subquery works, but the way it is spelled makes it quite unclear:

The join condition (c_Group.Group_Name LIKE 'mcp%') is not actually not related to the table being joined (c_Member) ; what it actually does is apply filter on table c_Group that makes it filter on (there is no magic such as shorter way of referring to two tables joining on a column with the same name, while applying the LIKE condition). It would make more sense to move it to the WHERE clause (this would still be functionaly equivalent).
On the other hand, the WHERE clause contains conditions that relate to the tables being joined (for example: c_Group.Name = c_Member.Parent_Name). A more sensible option would be to put them in the ON clause of the JOIN.

Other remarks:

when using NOT EXISTS, you usually would prefer SELECT 1 instead of SELECT *, (most RDBMS will optimize this under the hood for you, but this makes the intent clearer).
table aliases can be used to make the query more readable

I would suggest the following syntax for the query (which is basically syntaxically equivalent to the original, but a lot clearer):

SELECT u.ID
FROM c_User u
WHERE EXISTS (
    SELECT 1
    FROM c_Group g 
    JOIN c_Member m ON g.Name = m.Parent_Name AND m.Child_Name = u.Lower_User_Name
    WHERE g.Group_Name LIKE 'mcp%'
)

Using ON clause to JOIN tables with same column name

1 Answers1