0

Is there a way to do what I want to call a "LEFT CROSS JOIN"?

I.e., is there a way to specify a CROSS JOIN ON, but return at least one row in the result for every row in the left, using nulls for the right-side columns when the right-side table does not contain a match?

2

2 Answers 2

3

This explanation took a bit of time to digest, but helped clarify for me that what I was looking for is in fact just a LEFT OUTER JOIN.

LEFT OUTER JOIN is logically, as they explain, a CROSS JOIN ON, followed by restoring any rows from the left side that were removed because they had no matches in the "ON".

(Posting this for others, because it reflects how I was searching before I understood.)

4
  • LEFT JOIN ... ON 1=1 is what you are looking for Dec 18, 2021 at 21:15
  • @Charlieface, I don't think I can use an inner join on 1=1 when I also do need to match on some index columns. I think that requires OUTER, like LEFT OUTER JOIN ON a.employee_id = b.employee_id AND a.company_id = b.company_id (This question gives some information about the 1=1 trick.) For example, the first case illustrated in the answer I linked, A INNER JOIN B ON A.Colour = B.Colour does not result in a column with ('Red', NULL)) Dec 18, 2021 at 22:52
  • 1
    Sorry your question said LEFT CROSS JOIN which implied no join condition, I was explaining how to do that. If you have a join condition then I fail to see what the issue is, it's just a normal left-join Dec 19, 2021 at 0:37
  • Got it, that's a useful distinction to leave here. I started with CROSS JOIN thinking that CROSS was always needed when you want to get rows for every matching combination of left and right, and it worked like I'd expected. Later when I wanted to add in rows with NULLs to show when there were no matches on the right, I tried to find a way to "LEFT CROSS" by analogy to LEFT INNER and LEFT OUTER. Thus leaving a question like this for others following the same trail. Dec 19, 2021 at 0:59
1

There are five types of joins in SQL Server

  • INNER JOIN - Requires data to match in both tables.
  • LEFT OUTER JOIN - You get all rows from the left table, plus rows from the right table, where they match the left.
  • RIGHT OUTER JOIN - Opposite of Left Join (Rarely used).
  • FULL OUTER JOIN - returns all joined rows, play unjoined rows from both table. (Also rarely used)
  • CROSS JOIN - Joins all rows in both table. If both tables have 10 rows, you’ll get 100 rows back.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.