Is there such a thing as a LEFT CROSS JOIN?

Question

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?

Answer 1

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.)

Answer 2

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.