A SQL Join statement is used to connect rows from two or more tables, based on a related column between them. There are four types of JOINs, an Inner Join, Left Outer Join, Right Outer Join and Full Outer Join.
An Inner Join selects all rows from both the tables as long as the data matches. The Inner Join will combine all rows from both the tables where the columns match.
A Left Outer Join returns all the rows of the table on the left side of the join and matching rows for the table on the right side of the join. The rows for which there is no matching row on the right side.
A Right Outer Join is the opposite of a Left Outer Join. This join returns all the rows of the table on the right side of the join and matching rows for the table on the left side of the join.
Finally, a Full Outer Join returns all the rows from both tables. This Join creates the result-set by combining the result of both Left Outer Join and Right Outer Join.
When working on SSRS reports involving stored procedures, I found myself having to reteach myself the different types of JOINs in SQL. They are pretty simple once understood but can help with SQL queries quite a bit! I hope you enjoyed this post, thanks for stopping by!
Top comments (2)
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
There is also CROSS JOIN, where every combination of rows from both tables are joined with each other. But I feel like I know, why you left that one of your list. It is a rare occurence (at least for me), that I want all rows of two tables cross joined.
The only case I used it so far, was to join a table with another, that contained only one row.