I have already explained about SQL joins in other articles. Here in this article my focus is on SQL join examples for equi join and non equi join. 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 importan
CLICK HERE TO GET MORE INFORMATION ABOUT INNER JOIN AND OUTER JOIN
1.Joins using Operators -> Equi Join, Non Equi Join
2.Joins using Concept-> Inner Join, Outer Join, Cross Join, Self Join
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
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
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 From Employee a,Department b
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.
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
Introduction Cryptocurrencies took the world by storm, setting up a new financial system and breaking…
In my previous article I have given Top 20 technical support interview questions with its…
In my previous articles I have given 15 most asked desktop support interview questions with…
A business analyst is someone who is versed in processes of data analysis used for…
In my previous article I have already given top questions and answers for Desktop support…
In my previous article I have given unix production support interview questions and answers. In…