« | »

Inner and outer joins SQL examples and the Join block

join-block-venn-diagram-datamartist
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

Join-Example-Students-And-Advisors-Tables

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

Join-Example-Students-And-Advisors-Inner-Join

2) Left Outer Join SQL Example

select * from dbo.Students S LEFT OUTER JOIN dbo.Advisors A ON S.Advisor_ID=A.Advisor_ID

Join-Example-Students-And-Advisors-Left-Outer-Join

4) Full Outer Join SQL Example

select * from dbo.Students S FULL OUTER JOIN dbo.Advisors A ON S.Advisor_ID=A.Advisor_ID
Join-Example-Students-And-Advisors-Full-Outer-Join

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
Join-Example-Students-And-Advisors-non-joining-Join

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
Join-Example-Students-And-Advisors-left-exlusive-Join

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?
Join-Example-Students-And-Advisors-duplicate-advisors

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.

Tagged as: , ,

Twitter

« | »

63 Comments

  1. HOW CAN WE JOINS TWO TABLES IN DBMS WITHOUT USING RDBMS.?SUB QUERIES AND JOINS AND SET OPERATORS ALL COMES UNDER RDBMS.................................

  2. excellent!!

    nice explanation mention here

    Thanks..

  3. Thanks a lot. This will clear all types of join.

    Thanks you again for such a nice examples....

  4. Nice example of join query

  5. Informative article. Thanks

  6. very nice example

  7. Thanksss....a lot

  8. Nicely explained with Examples.

  9. This is a much learning lesson with good examples....Thankxxx

  10. Good Example

  11. Awesome topic explination

  12. Very nice. Nicely explained with examples.

Trackbacks

  1. Solution: Difference between INNER and OUTER joins #dev #it #computers | Good Answer

Leave a Response