SQL JOINS — INNER,OUTER( FULL,LEFT,Right) ,CROSS, SELF — Parv The IT Geek

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.


Originally published at https://parvtheitgeek.com on January 28, 2014.

Published by Parvinder Nijjar

I blog at ParvTheITGeek.com

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

<span>%d</span> bloggers like this: