7

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
Scott Duncan
  • 1,041
  • 1
  • 12
  • 22
  • 3
    Possible duplicate of [When should I use Cross Apply over Inner Join?](http://stackoverflow.com/questions/1139160/when-should-i-use-cross-apply-over-inner-join) – SqlZim Mar 03 '17 at 19:40
  • 1
    I don't think that `cross join` would be considered comparable to the other two in most situations. So then your question becomes `inner join vs cross apply` which is here: http://stackoverflow.com/questions/1139160/when-should-i-use-cross-apply-over-inner-join – SqlZim Mar 03 '17 at 19:41
  • Not quite a duplicate since the other was focused on when to use Cross Apply in general, but if your assertion is correct that Cross Join should be rejected outright, than I think the other post suggests that CROSS APPLY should be faster than INNER JOIN. Would that be the case when pulling exactly one record, or is there really no difference and it's more of a personal preference? – Scott Duncan Mar 07 '17 at 22:27
  • Your example queries do not make sense in the context of trying to compare these different operations. You are essentially pulling a constant value with three different ways of adding it to your query. You could also just use a local variable or use a subquery in your select. `SecRoleID` has no **relation** to the rest of your query. – SqlZim Mar 07 '17 at 22:43
  • You are correct. I am trying to add the appropriate SecRoleID to every record inserted. It could be with a local variable (using a SELECT to set it WHERE Name = 'Viewer'), or a subquery. Do you feel either of those are a better solution than using the Cross Apply? – Scott Duncan Mar 07 '17 at 22:53

2 Answers2

3

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I don't know OUTER APPLY; sounds like a 4th way to do this. You wouldn't know this (cause I didn't state it), but since SecRoleID is NOT NULL in the target table, I am indeed looking for exactly one matching row. Are you saying that the CROSS APPLY (TOP 1) could come back NULL, and so it is not an appropriate solution? – Scott Duncan Mar 03 '17 at 20:23
  • @ScottDuncan . . . No. `CROSS APPLY (TOP 1)` does what you want. The others would not seem to. `OUTER APPLY` would return `NULL` values if there were no match between the tables. – Gordon Linoff Mar 03 '17 at 23:05
  • You mentioned that the first two are equivalent. Is that true, or does the Cross Join join all rows before filtering vs the Inner Join joining on just the one desired row? Either way, I'm leaning towards the Cross Apply since it seems to most clearly indicate the intent. I was concerned about performance, but the link SqlZim provided above seemed to indicate that the Cross Apply should have better performance anyway. – Scott Duncan Mar 07 '17 at 22:33
  • 1
    @ScottDuncan . . . SQL statements *describe* the result set. The optimize chooses the best execution plan. Every optimizer that I'm familiar with would treat the two as identical. – Gordon Linoff Mar 08 '17 at 04:15
  • 1
    FWIW I just wanted to drop a note here since it was brought up that it seems the optimizer doesn't always pick the best execution plan between INNER JOIN and CROSS JOIN even when the datasets are identical. In my case, I had one column in the two tables that was the same constant value for all rows. CROSS JOIN and INNER JOIN on that column are thus the same. However, if that column gets used in a later join, it seems the optimizer did not retain the knowledge that it was fixed and thus did a lot of unnecessary comparisons, whereas with INNER JOIN it 'remembered' that they matched. – Shirik Jun 29 '20 at 20:10
0

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

Mehrad Eslami
  • 308
  • 3
  • 13
  • The question is, best way to attach exactly one value from SecRole when there is no common key between SecRole & the other tables, so what do you mean by 'put them next to' the (exactly one) desired record from SecRole? My example shows three possible ways, and I'm wondering which is best, or is there a fourth better way? Are you suggesting a Right Outer Join onto SecRole? Since there is no common key, what would be the ON clause, & how is that better than the CROSS JOIN with the WHERE filter, or the LEFT JOIN with the filter in the ON clause? – Scott Duncan Mar 07 '17 at 22:12
  • Sorry, I meant INNER JOIN, not LEFT JOIN (apparently you only have 5 minutes to catch a typo & fix it in-place). – Scott Duncan Mar 07 '17 at 22:19