DEV Community

Discussion on: Different Types of SQL JOINs

Collapse
aarone4 profile image
Aaron Reese

In 15 years of writing SQL code I have not come across a legitimate use of Right Join in production code. It is a code-smell that indicates you don't understand the data.
In most dialects of SQL you don't need the inner and outer key words.
As already stated there is CROSS JOIN. This is useful where you need to generate a record Even if one may not exist in the source table (e.g. daily sales)
Select s.store, d.dayofweek,t.total from store as s cross join days as d left join turnover as t on t.store = s.store and t.dayofweek = d.dayofweek
The above will generate a record for every store for every day with a turnover value IF the store was open.
Cross join us especially useful for reports where you need histogram or time-series data where source values may be missing.
Also bear in mind that any WHERE criteria on a left joined table will effectively turn it into an inner join, as will using an inner join later in the code