« Estimating the cost of Business Intelligence | Tableau Public- great visualization now where do we get the data? »
Inner and outer joins SQL examples and the Join block
In this post I’ll show you how to do all the main types of Joins with clear SQL examples. The examples are written for Microsoft SQL Server, but very similar syntax is used in Oracle, MySQL and other databases.
If you are tired of writing SQL and want to try a visual tool, you should give Datamartist a try. The diagrams with the SQL examples are actually right from the tool- you just have to pick what parts of the Venn diagram you want, and the data is joined for you- no code.
Joins can be said to be INNER or OUTER joins, and the two tables involved are referred to as LEFT and RIGHT. By combining these two concepts you get all the various types of joins in join land: Inner, left outer, right outer, and the full outer join.
Tables used for SQL Examples
In the screen shots I’ve configured Datamartist to only show the name columns to save space. The SQL code shown is “Select *” so it will return all the columns. You can see that in the Datamartist tool the type of join is selected by just checking the parts of the venn diagram that contain the rows you want.
1) Inner Join SQL Example
select * from dbo.Students S INNER JOIN dbo.Advisors A ON S.Advisor_ID=A.Advisor_ID
2) Left Outer Join SQL Example
select * from dbo.Students S LEFT OUTER JOIN dbo.Advisors A ON S.Advisor_ID=A.Advisor_ID
4) Full Outer Join SQL Example
select * from dbo.Students S FULL OUTER JOIN dbo.Advisors A ON S.Advisor_ID=A.Advisor_ID
5) SQL example for just getting the rows that don’t join
select * from dbo.Students S FULL OUTER JOIN dbo.Advisors A ON S.Advisor_ID=A.Advisor_ID where A.Advisor_ID is null or S.Student_ID is null
6) SQL example for just rows from one table that don’t join
select * from dbo.Students S FULL OUTER JOIN dbo.Advisors A ON S.Advisor_ID=A.Advisor_ID where A.Advisor_ID is null
But what about the duplicate row thing?
Now, since in this case we had a simple one to one relationship, the number of rows that were returned made the venn diagrams make sense, and add up pretty normally with table one and two.
What happens if the data in the tables are not a simple one to one relationship? What happens if we add one duplicate advisor with the same ID, but a different name?
A join will create a row for every combination of rows that join together. So if there are two advisors with the same key, for every student record that has that key, you will have two rows in the inner part of the join. The advisor duplicate makes duplicate student records for every student with that advisor.
You can see how this could add up to a lot of extra rows. The number of rows is the product of the two sets of joining rows. If the tables get big, just a few duplicates will cause the results of a join to be much larger than the total number of rows in the input tables- this is something you have to watch very carefully when joining- check your row counts.
So there you have it. If you want to try joining tables with the Datamartist tool- give it a try. It’s a super fast install, and you’ll be joining like a pro in no time.
« Estimating the cost of Business Intelligence | Tableau Public- great visualization now where do we get the data? »
thanks a lot
very nice examples with clarity data
this is the very cleared description of joins…
thanks
thanks a lot
very nice examples with clarity data
Crystal clear explanations with diagrams & ex’s, really helped a lot. Thank You.
Thanks… understandable article .
Very nice and clear tutorial.
Great article. Thanks a lot for all these clear explanations that made it easy to understand how to join multiple table with SQL.
looks very nice and clear tutorial !!! many thanks for making me easier understanding on “Join” !!!! Keep up great works…………..
Awesome! Thanks a lot!
hi,
What a great example. My doubts are cleared after long time..!!!!!!!
Thanks
Good Example.
Great article! Very clear and concise!
Thank you very much!
The concept has been very neatly explained with diagrams and examples which makes it all the more easier to understand. Nice Job!
Thanks a lot!
This is a very good way to clarity the Join concept.
Anybody can easily understand it.
Thanks a lot.
This is the best website I had seen that clearly pin points the difference between the Joins.
Thanks a Lot for your effort to create this
* Sql Introduction
* Using Stored Procedures
* sql queries
* groupby functions etc
* Joins
Complete explanation
Link here for Sql Tutorial
http://geeksprogrammings.blogspot.in/search/label/StructuredQueryLanguage%28SQL%29
Link here for joins explanation
geeksprogrammings.blogspot.in/2013/06/joins-in-sql.html
thanks to guide me………………………….
Good Example…
Thanks
Thanks for Your Higher Education
thanks a lot
good examples when compare to other websites Keep it up!!!!!
Excellent explantion with examples which helped me a lot..
Very nice example of join
Missing right outer join example. point 3)
Thanks , Nice examples
Thanks you very much, very nice tutorial
This also the best tutorial on Join that I have seen. The best explanation is the concept on types of join, and using the two tables, left and right.
Thanks
This piece of writing is truly a nice one it helps new internet people,
who are wishing in favor of blogging.
This article is one of the best examples i’ve seen on this topic.
Thank you !
good explanation,
Nice explanation
What will be the output in case we try Right Outer join? (right table being Advisors)
6) SQL example for just rows from one table that don’t join
select * from dbo.Students S FULL OUTER JOIN dbo.Advisors A ON S.Advisor_ID=A.Advisor_ID where A.Advisor_ID is null
I think this wouid be
select * from dbo.Students S FULL OUTER JOIN dbo.Advisors A ON S.Advisor_ID=A.Advisor_ID where A.Advisor_Name is null
thanks for the wonderful implimentation of the join…
Nice explanation of joins. Wish had done a left outer join with a where clause.
Thank you
This also the best tutorial on Join that I have seen. THANKS A LOT
really best example
It’s easy to understand because good example.
Thank you……………
Great tips and clearly expressed. I put into practice immediately.
well….got completly……
very good Excellent explanation……thank you…
Very easy to understand with good example.This is tutorial i have seen
Thanks a lot……….
I WON’T TO CODE FOR JOINS IN TABLEAU
Simple and nice examples to make things clear…thanks a lot for contribution
Hey there! If you love Tableau, but want to get your data ready before you load it, I’ll tell you a secret- we are just about to go out in Beta with a version of Datamartist that can write directly to Tabeleau TDE files. This means you can use the Datamartist Join functionality, which is graphical- just pick the columns and select the parts of the Venn diagram you want, and the data will join- then pump the data directly into Tableau. Stay tuned.
Thanks, simple & concise, article. Helped a lot!