INNER JOIN vs CROSS JOIN vs CROSS APPLY

Question

One of the joys of SQL is that there are often multiple ways to do the same thing, so which is "best"?

In this case, I am inserting records from an 'Import' table and giving them all a default security level of 'Viewer' (could have different Key IDs in different databases). I can do this in at least three different ways (and probably even a few more): CROSS JOIN, CROSS APPLY, and INNER JOIN. Any suggestions as to which would be best for either performance or design purposes? I'm leaning towards the Cross Apply.

This question might have already been answered, but I can't find it, and lately I keep running into this need in my development, so I might as well learn the best way.

Here are the 3 example statements. Which is the best way to JOIN the SecRole table?

INSERT INTO LocStaff (LocationID, StaffID, SecRoleID)
    SELECT i.LocationID, s.StaffID, sr.SecRoleID
    FROM IntStaff i
        JOIN Staff s ON i.EmployeeID = s.StaffNumber
        CROSS JOIN SecRole sr
    WHERE sr.Name = 'Viewer' 

INSERT INTO LocStaff (LocationID, StaffID, SecRoleID)
    SELECT i.LocationID, s.StaffID, sr.SecRoleID
    FROM IntStaff i
        JOIN Staff s ON i.EmployeeID = s.StaffNumber
        JOIN SecRole sr ON sr.Name = 'Viewer'

INSERT INTO LocStaff (LocationID, StaffID, SecRoleID)
    SELECT i.LocationID, s.StaffID, sr.SecRoleID
    FROM IntStaff i
        JOIN Staff s ON i.EmployeeID = s.StaffNumber
        CROSS APPLY (SELECT TOP 1 SecRoleID FROM SecRole WHERE Name = 'Viewer') sr

Answer 1

The first two are equivalent. Whether you use an inner join or cross join is really a matter of preference in this case. I think I would typically use the cross join, because there is no real join condition between the tables.

Note: You should never use cross join when the intention is a "real" inner join that has matching conditions between the tables.

The cross apply is not doing the same thing. It is only choosing one row. If your intention is to get at most one matching row, then use cross apply. If the intention is to get exactly one matching row, then use outer apply.