Have you ever been baffled when people talk about SQL joins? Mystified when you hear inner, outer, cross or self? Ran away in fear at the thought of joining two tables?
Well I am about to help you out and face down those fears and explain to you what joins are, the different types and what they are used for.
What is a join?
A join in SQL is a clause which combines two or more tables together within a database. The join is done on matching predicates between two or more tables and the result is a new data set.
Simply this means that it will take two tables and join them on a column within each table which has data that contains similar data.
What is an inner join?
An inner join will return only values where there is match between both corresponding columns in both tables.
Simply it means where the two columns match the rows will be returned in the new table.
What is an outer join? Left? Right? Full?
An outer join is different to an inner join as it does not require the data to match. A Left join would return all record from the left table and only the matching record from the right.
A Right join would return all record from the right table and only the matching record from the left.
A Full outer join returns all unmatched and matched data. Simply it is returning the results of a Left & Right join
What is a self-join?
A self-join is a table which is joined to itself.
What is a cross join
A cross join is often referred to as a Cartesian product and returns all possible combinations of rows.