INNER JOIN vs CROSS JOIN vs CROSS APPLY

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

---

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 .

---

We use Cross join (or just the keyword Join) only when we don't have any column in common among the tables (not the best structure design!) or we need all the possible cases. I don't know your table structure but I'm assuming table SecRole has no foreign or common key to Staff and InsStaff. In this case I would use a Right join (outer joins) here to get all the result from the first inner join between Staff and InsStaff and then put them next to SecRole desired records.

Here is the concept of a Right join http://www.dofactory.com/sql/right-outer-join

链接地址: http://www.djcxy.com/p/86228.html

上一篇: 将图像添加到Google可视化图表

下一篇: 内部连接与十字连接对十字应用