SQL Joins Basic | SQL Joins with Real Life Scenarios | Types of SQL Joins

  • SQL Joins:-

The most used concept in real life scenarios are nothing but SQL Joins.Although in reporting,stand alone applications development,Web application development the concept of join is really importantSQL Joinst.Joins are nothing but Combining the records from two or more tables.You will get Basic information about all the joins in this article.I will explain the Real life scenarios for all the joins in the article.

CLICK HERE TO GET MORE INFORMATION ABOUT INNER JOIN AND OUTER JOIN

  • Types of SQL Joins:

1.Joins using Operators -> Equi Join,Non Equi Join

2.Joins using Concept-> Inner Join,Outer Join,Cross Join,Self Join

Joins Using Operator:

When two or more tables are joined using Operators is nothing but the Joins Using operator.

1.Equi Join: When two or more tables has been joined using equal to operator then this category is called as equi join.Equi join should be inner joins or outer join.Just we

  /*Equi Join*/

need to concentrate on condition is equal to(=) between the columns in the table.Make sure that we are using where clause to apply the condition between two tables.if the condition of join misses or there is not relation between the tables in the join then Equi join fails and the result will be the Cartesian product or cross join.

CLICK HERE TO GET MORE INFORMATION ON CARTESIAN JOIN

Syntax :

Select alias1.column_name1,alias1.column_name2,alias2.column_name1..

from table1 alias1,table2 alias2

where table1.column=table2.column;     ---Equi join condition

 

  • Real Life Scenario:  

Question: Query to fetch the Employees with its deparment_name.

Consider there are 2 tables.Employee table which has following columns:

Employee_num Employee_name Department ID Salary
1 Amit 100 680000
2 Rohan 100 550000
3 Rohit 101 430000

There is another table called as Depatment which has following structure:

 

Department ID Department Name
100 OBIEE
101 Oracle PLSQL
102 COGNOS

SQL Query:

Select  a.Employee_name,b.Department_name

where a.Department_ID=b.Department_ID;

Output:

Employee Name Department Name
Amit OBIEE
Rohan OBIEE
Rohit Oracle PLSQL

2.Non Equi Join:

When two or more tables are joining without Equal to condition then that join is known as Non Equi join.The use of non equi join is very rare in real life scenarios.You can join tables using any other attributes except equal to operator.You can use any operator for non equi join that is <>,!=,<,>,Between.

Real Life Scenario:

Consider 2 table which as given above.one is Employee table and other is department table.

Question:Tell me the department which is not allocated to any of the Employee.

Consider there are 2 tables.Employee table which has following columns:

Employee_num Employee_name Department ID Salary
1 Amit 100 680000
2 Rohan 100 550000
3 Rohit 101 430000

There is another table called as Depatment which has following structure:

Department ID Department Name
100 OBIEE
101 Oracle PLSQL
102 COGNOS

SQL Query:

Select b.Department_ID,b.Department_name from

Employee a,Department b where a.Department_id <> b.Department_ID;

The Above Query will return following output:

102 COGNOS

These kind of real life scenarios we need to face in day to day life.I will explain other joins in next article.Hope you like this article.Kindly comment if you like the article or if any suggestions.

CLICK HERE TO GET 20 MOST IMPORTANT COMPLEX SQL QUERIES FOR INTERVIEW

HOME

Leave a Reply