SQL Joins

In this Post, we are going to discuss on the different types of Joins of SQL.
Lets no buy time on the introduction, here are the important things which needs to be remember -

Note - What is the maximum number of Joins, you can do in Oracle. This was a question in Interviews. -  Well, there is no documented limit, as of now. But yes , there should be practical limit, to get benefits from Joining.

Join is a way of combining two or more tables from the Database, to get the desired result set, as simple as that. Although some Interviewers might not be happy with the answer. You can go on explain different Joins to get attention.
For convenience, lets use the following two tables - Workers & Section

Table - Workers

Table - Sections

So, these are pretty simple Tables, now lets Join these two tables. You can see, both the Table contains Section ID, so we will be using the Section for Joining -
Lets Start -

• Inner Join - Inner Join will fetch the common data from the above two tables. We will join the two tables using the common Section ID. Here are the results below -

As you can see, the Sections with ID 90, 80 & 60 do not appear in the result set. Its that simple.
Note - Just to clear the concept, you can see or understand via Venn Diagrams, "common portion between the two circles".

Differences between "ON" clause and "USING" clause in Join
We can also join using "USING" clause, when we have explicit common column to join. Here, "Section_ID" present in both the tables, hence we can use "USING" clause. But, there will be a minor change in the result set. Just see below -


See, the column "Section_ID" do not appear in the result Set. This was another Interview questions and people get confused.

Lets do it via old syntax, and this will give you the same result as using "ON" clause, as follows -


What will happen if you use "JOIN" instead of "INNER JOIN" in the above queries -  Well, "JOIN" means, simple Inner Join and there's no hype or differences.

• Left Outer Join - Left outer join, will display all the results or records from your left table, along with the matched records from the right table. If some records do not match, result set will display blank for those records. Just see the example, you will understand.

Interview questions  - How will you display all the records from your first table , along with all the matched records from your second table. If matched, display the values, if not matched, then display blank. What will be your preferred way, like, "case", "decode", etc. -- Well, just use fundamentals of "left Outer Join" or "right outer Join".




You can also use the old syntax using the following query - only one simple thing to notice here -
select * from workers w, sections s where w.section_id = s.section_id(+);
'plus' sign is on the right table to indicate "left outer join". Don't get confuse here.

• Right Outer Join - Its just the opposite of left outer join. Result set will display all the records from the right table and matched records from the left table. Unmatched records of the left table will be blank. Just see the example -

For the old syntax, use the following query and notice the 'plus" sign is on left side.
select * from workers w, sections s where w.section_id (+) = s.section_id;

• Full Outer Join - This is interesting. Result set will display matched results from both left and right tables; unmatched results will be blank, from both left and right tables. See the example -

Now, do not try to use 'Plus' sign in both the sides for other syntax. Well you can't outer join both the tables -this is the error you will get.
You can also omit the "outer" keyword from the above query. The following query will give you the same result -
select * from workers w full join sections s on w.section_id = s.section_id;

• Cartesian Product or Cross Join -  As the name suggests, result set will display the cartesian product of the two tables, i.e for every record in left table, there will all records in right table, thus, if left table contains 8 records and right table have 8 record, cartesian product will fetch 8X8 records i.e. 64 records. Here is the example below -

Note - You do not have to use 'ON' or 'USING' clause , otherwise it will lead to error.
You might have noticed some other JOIN terms in the internet. Lets discuss on them to clear the confusion.

• Equi Join  - Equi Join is nothing but an Inner Join, using an 'equality' operator. This is same as using 'ON' clause on a Inner Join or Join. The following query is nothing but a Equi Join.

We have discussed this above, saying its a oracle syntax.

• Non-Equi Join - If we use the Inner Join oracle syntax, using operator , other than "=", it will be a Non-Equi Join. e.g. >=, <=, etc. Its that pretty simple.
• Natural Join - Natural Join is again a Equi join, only difference is , it will display the common column only once. This behaves the same way, as using an Inner Join using "USING" clause.

• Self Join - Self Join means Joining with itself. One table, two aliases, behaves as two different tables and Join. The most common example of Self Join, is when an "employee" table contains Employee_ID as well as their "Manager_ID", who themselves are nothing but the Employees.

Alternatively, we can use "hierarchical Queries", which I will discuss in separate Post.
Good Luck !! Any comments or questions is appreciable.